Search code examples
sql-serversql-server-2016

Get multiple rows using FOR JSON clause

Using PostgreSQL I can have multiple rows of json objects.

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

This gives me this result:

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

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

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

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

How to get the same result format in SqlServer ?

Solution

  • By constructing separate JSON in each individual row:

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

    There is an alternative form that doesn't require you to know the table structure (but likely has worse performance because it may generate a large intermediate JSON):

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