Share via

Select query with JSON value

Mohamed Farook 161 Reputation points
2021-02-23T14:58:38.957+00:00

Hi, I need JSON value from query string in table column it's possible ? CREATE TABLE #SUB1 (Ledger INT,Description VARCHAR(100)) CREATE TABLE #SUB2 (Ledger INT,Particulars VARCHAR(100)) INSERT INTO #SUB1 (Ledger,Description) VALUES (1001,'JOHN'),(1002,'ROBERT'),(1003,'JOE') INSERT INTO #SUB2 (Ledger,Particulars) VALUES (2001,'US'),(2002,'IND'),(2003,'DXB') CREATE TABLE #Main (ID INT,Name VARCHAR(50),QueryStr NVARCHAR(max)) INSERT INTO #Main (ID,Name,QueryStr) VALUES (1,'AAA','') ,(2,'BBB','') ,(3,'CCC','select Ledger,Description from #SUB1 for JSON PATH ') ,(4,'DDD','select Ledger,Particulars from #SUB2 for JSON PATH') select * from #Main drop table #Main,#SUB1,#SUB2 pls help.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 107.2K Reputation points
    2021-02-23T23:01:26.703+00:00

    You can use dynamic SQL, but when I see things like that I always wonder "what does the poster really want to achieve"? May be there is a better solution that does not require dynamic SQL.

    I have an article about http://www.sommarskog.se/dynamic_sql.html here. It is long, but dynamic SQL is an advanced topic.

    0 comments
  2. CathyJi-MSFT 21,131 Reputation points Microsoft Vendor
    2021-02-24T06:49:50.547+00:00

    Hi @Mohamed Farook ,

    > I need JSON value from query string in table column it's possible ?

    Yes. If you have JSON text that's stored in database tables, you can read or modify values in the JSON text by using some built-in functions. Such as OPENJSON, JSON_QUERY etc. Please refer to the MS document JSON data in SQL Server or the blog Basics of Working with JSON in SQL Server to get more information.

    If I misunderstood, please let me know.

    If the response is helpful, please click "Accept Answer" and upvote it, thank you.

    0 comments