How to query JSON values from a column into one JSON array in MS-SQL 2016?

Question

I'm starting to fiddle out how to handle JSON in MSSQL 2016+

I simply created a table having a ID (int) and a JSON (nvarchar) column.

Here are my queries to show the issue:

First query just returns the relational table result, nice and as expected.

SELECT * FROM WS_Test

-- Results:

1   { "name": "thomas" }
2   { "name": "peter" }

Second query returns just the json column as "JSON" created my MSSQL.
Not nice, because it outputs the json column content as string and not as parsed JSON.

SELECT json FROM WS_Test FOR JSON PATH

-- Results: 

[{"json":"{ \"name\": \"thomas\" }"},{"json":"{ \"name\": \"peter\" }"}]

Third query gives me two result rows with json column content as parsed JSON, good.

SELECT JSON_QUERY(json, '$') as json FROM WS_Test

-- Results:

{ "name": "thomas" }
{ "name": "peter" }

Fourth query gives me the json column contents as ONE (!) JSON object, perfectly parsed.

SELECT JSON_QUERY(json, '$') as json FROM WS_Test FOR JSON PATH

-- Results:

[{"json":{ "name": "thomas" }},{"json":{ "name": "peter" }}]

BUT: I don't want to have the "json" property containing the json column content in each array object of example four. I just want ONE array containing the column contents, not less, not more. Like this:

[
    {
        "name": "peter"
    },
    {
        "name": "thomas"
    }
]

How can I archive this with just T-SQL? Is this even possible?

Answer 1

The FOR JSON clause will always include the column names - however, you can simply concatenate all the values in your json column into a single result, and then add the square brackets around that.

First, create and populate sample table (Please save us this step in your future questions):

CREATE TABLE WS_Test
(
    Id int, 
    Json nvarchar(1000)
);

INSERT INTO WS_Test(Id, Json) VALUES
(1, '{ "name": "thomas" }'),
(2, '{ "name": "peter" }');

For SQL Server 2017 or higher, use the built in string_agg function:

SELECT '[' + STRING_AGG(Json, ',') + ']' As Result
FROM WS_Test

For lower versions, you can use for xml path with stuff to get the same result as the string_agg:

SELECT STUFF(
(
    SELECT ',' + Json
    FROM WS_Test
    FOR XML PATH('')
), 1, 1, '[')+ ']' As Result

The result for both of these queries will be this:

Result
[{ "name": "thomas" },{ "name": "peter" }]

You can see a live demo on DB<>Fiddle