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.

Answer 1

Try this:

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