Get multiple rows using FOR JSON clause

Question

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 ?

SQL Server 2016 CTP3.2 added without_array_wrapper... Sample: select top 5 (select a.* for json path, without_array_wrapper) from sys.objects a — Jason Goemaat
– Jason Goemaat, Commented Sep 7, 2018 at 22:54

Jeroen Mostert · Accepted Answer · 2017-06-16 07:30:49Z

47

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)
)

edited Jun 16, 2017 at 7:30

answered Apr 24, 2017 at 12:44

Jeroen Mostert

29k2 gold badges60 silver badges92 bronze badges

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

3 Comments

Jeroen Mostert Over a year ago

@Mathew: yes, see updated question. I don't know of a way to do this that will have good performance; ask a separate question if that's a concern. (Or, you know, just write out the columns, SELECT * is recommended against for a reason.)

abhi Over a year ago

Still useful in 2022.

CodePT Over a year ago

Worked like a charm, the only thing I was missing was the INCLUDE_NULL_VALUES option for null values that I wanted anyway, gonna leave it here, might help someone.

Nenad Zivkovic · Accepted Answer · 2019-09-30 09:12:48Z

9

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)

edited Sep 30, 2019 at 9:12

Nenad Zivkovic

18.6k6 gold badges45 silver badges58 bronze badges

answered Dec 25, 2018 at 16:35

Barak Yellin

911 silver badge1 bronze badge

1 Comment

AndresChica Over a year ago

Thanks a lot. The other answers doesn`t work for me

Erick de Vathaire · Accepted Answer · 2021-11-10 23:08:51Z

0

Same as Barak Yellin but more lazy:

1-Create this proc

CREATE PROC PRC_SELECT_JSON(@TBL VARCHAR(100), @COLS VARCHAR(1000)='D.*') AS BEGIN
EXEC('
SELECT X.O FROM ' + @TBL + ' D
CROSS APPLY (
    SELECT ' + @COLS + '
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) X (O)
')
END

2-Can use either all columns or specific columns:

CREATE TABLE #TEST ( X INT, Y VARCHAR(10), Z DATE )
INSERT #TEST VALUES (123, 'TEST1', GETDATE())
INSERT #TEST VALUES (124, 'TEST2', GETDATE())

EXEC PRC_SELECT_JSON #TEST

EXEC PRC_SELECT_JSON #TEST, 'X, Y'

If you're using PHP add SET NOCOUNT ON; in the first row (why?).

answered Nov 10, 2021 at 23:08

Erick de Vathaire

1732 silver badges8 bronze badges

Collectives™ on Stack Overflow

Get multiple rows using FOR JSON clause

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related