CODEWITHSUNDEEP
sqljsonsql-server-2016
Tehila
Tehila

Reputation: 61

Generate JSON from SQL Server

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.

Upvotes: 3

Views: 5173

Answers (1)

FDavidov
FDavidov

Reputation: 3675

Try this:

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

Upvotes: 4

Related Questions