Select query with JSON value

Mohamed Farook 141 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,119 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 104.2K Reputation points MVP
    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 No comments

  2. CathyJi-MSFT 21,111 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 No comments