Generate JSON from SQL Server

Question

I need to generate JSON string from a sql table (using the FOR JSON AUTO qualifiers), while one (or more) of the columns is (are) stored as json string.

e.g.:

Table Persons:

First_Name    | Family_Name   |City      | Children
--------------+---------------+----------+---------------
David         |Bin            | Miami    |[{"First_Name" :"John","Family_Name":"Bin"}]
Mary          |Nis            | New York |[]

The required result would then be:

[
 {"First_Name":"David",
  "Family_Name":"Bin",
  "City":"Miami",
  "Children": [{"First_Name" :"John",
                "Family_Name":"Bin"}
              ]
 },
 {"First_Name":"Mary",
  "Family_Name":"Nis",
  "City":"New York",
  "Children": []
 }
]

My current issue is that in the result, all the occurrences of the " are escaped and hence the application receiving it fails (illegal JSON).

Could anyone suggest the correct phrasing for the SELECT command that would generate the result shown above?

Thanks in advance.

SELECT First_Name , Family_Name , City , Children , FROM Person FOR JSON AUTO — Tehila
– Tehila, Commented Aug 9, 2018 at 10:09

FDavidov · Accepted Answer · 2018-08-09 11:26:23Z

4

Try this:

  SELECT        First_Name      , 
                Family_Name     , 
                City            , 
     JSON_QUERY(Children) 
  FROM Person FOR JSON AUTO ;

answered Aug 9, 2018 at 11:26

FDavidov

3,7556 gold badges27 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Generate JSON from SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related