Clever Geek Handbook

Get multiple lines using FOR JSON clause

Using PostgreSQL I can have multiple strings of json objects.

select (select ROW_TO_JSON(_) from (select c.name, c.age) as _) as jsonresult from employee as c

It gives me this result:

{"age":65,"name":"NAME"}
{"age":21,"name":"SURNAME"}

But in SqlServer, when I use FOR JSON AUTO clause, it gives me an array of json objects instead of multiple lines.

select c.name, c.age from customer c FOR JSON AUTO

[{"age":65,"name":"NAME"},{"age":21,"name":"SURNAME"}]

How can I get the same result format in SqlServer?

+7


source to share


2 answers


By creating separate JSON on each separate line:

SELECT (SELECT [age], [name] FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
FROM customer


There is an alternative form that does not require you to know the structure of the table (but it probably has the worst performance since it can generate large intermediate JSON):

SELECT [value] FROM OPENJSON(
    (SELECT * FROM customer FOR JSON PATH)
)
+17


source


no structure better performance



SELECT c.id, jdata.*
FROM customer c
  cross apply 
    (SELECT * FROM customer jc where jc.id = c.id FOR JSON PATH , WITHOUT_ARRAY_WRAPPER) jdata (jdata)
+2


source






More articles:


All Articles