SQL OPENJSON to Single Column

Question

I have the data below;

DECLARE @json NVARCHAR(2048) = N'{
    "searchId": -1,
    "statuses": [
        33,
        85,
        90
        ]
}';

And perform a return from this with;

SELECT searchID
    ,x.statuses
FROM OpenJson(@json) WITH (
        searchID VARCHAR(200) '$.searchId'
        ,statuses NVARCHAR(MAX) '$.statuses' AS JSON
        )
CROSS APPLY OPENJSON(statuses, '$') WITH (statuses int '$') AS x

And returns;

searchID    statuses
-1          33
-1          85
-1          90

It works, however id like the return to be as a single row, so the "statuses" column to be presented as

searchID    statuses
-1          33,85,90

There are a few ways ive experimented with this mostly using Stuff() and ForXML however im thinking there is bound to be a cleaner way manipulating the json ?

Answer 1

You could just leave the statuses as JSON, and leave the presentation to the front-end.

DECLARE @json NVARCHAR(2048) = N'{
    "searchId": -1,
    "statuses": [33,85,90]
}';

SELECT searchID
    ,x.statuses
FROM OpenJson(@json) WITH (
        searchID VARCHAR(200) '$.searchId'
        ,statuses NVARCHAR(MAX) '$.statuses' AS JSON
        ) x

outputs

searchID              statuses
--------------------- -----------
-1                    [33,85,90]

Or just remove the '[' and ']' from the JSON array:

DECLARE @json NVARCHAR(2048) = N'{
    "searchId": -1,
    "statuses": [33,85,90]
}';

SELECT searchID
    ,substring(x.statuses,2,LEN(x.statuses)-2) statuses
FROM OpenJson(@json) WITH (
        searchID VARCHAR(200) '$.searchId'
        ,statuses NVARCHAR(MAX) '$.statuses' AS JSON
        ) x

outputs

searchID              statuses
--------------------- -----------
-1                    33,85,90

Answer 2

STRING_AGG it back to varchar.

DECLARE @json NVARCHAR(2048) = N'{
    "searchId": -1,
    "statuses": [
        33,
        85,
        90
        ]
}';
--
SELECT searchID, string_agg(x.statuses, ',') statuses
FROM OpenJson(@json) WITH (
        searchID VARCHAR(200) '$.searchId'
        ,statuses NVARCHAR(MAX) '$.statuses' AS JSON
        )
CROSS APPLY OPENJSON(statuses, '$') WITH (statuses int '$') AS x
GROUP BY searchID;