Can I use FOR JSON without a source column name becoming a key in SQL Server 2016?

Question

I have a table which contains json strings with a group id. I want to create a json array that contains all the json objects with the same id.

Note that in my case the column contents I'm trying to put into an array are json snippets themselves, therefore my case and question is more specific than turning rows into an array. The use of OPENJSON in the accepted answer also leverages this detail.

However, I could not find a way of using FOR JSON that allows me to select a single column without that column's name becoming the key for all the objects in the array.

I realise the key I'm trying to get rid of is necessary to project columns to keys of the key-value representation of json but in this case I'm selecting only one column and I'm curious if there is a trick I cannot think of.

I can achieve what I want by simply using COALASCE but I'd like to know if it is possible to handle this edge case using FOR JSON. Here is the simplified code that demonstrates what I'm trying to do:

BEGIN
    declare @Json_Snippets table (id int, json nvarchar(max));

    insert into @Json_Snippets (id, json) VALUES (1, '{"root":{"obj":"one"}}');
    insert into @Json_Snippets (id, json) VALUES (1, '{"root":{"obj":"two"}}');

   select
          JSON_QUERY(js.json) as 'dont_want_this'
   from
        @Json_Snippets js
    FOR JSON PATH;

END

I have to JSON_QUERY to avoid escaping characters in the json column, i.e. json string stays as json. Running the above gives:

[{"dont_want_this":{"root":{"obj":"one"}}},{"dont_want_this":{"root":{"obj":"two"}}}]

What I'd like to get is:

[{"root":{"obj":"one"}},{"root":{"obj":"two"}}]

You don't need `JSON_QUERY` at all in this query. I suspect your *actual* question is how to *aggregate* muiltiple JSON values in an array? Not supported unfortunately, you'll have to hack it. In SQL Server 2017 you can use `STRING_AGG`. In earlier versions you'll have to use one of the usual string aggregation techniques — Panagiotis Kanavos, Apr 18 '19 at 09:23
@PanagiotisKanavos I actually need it (did you see my edit?) because otherwise json strings would be escaped and would not be json I can directly use anymore. — GrumpyRodriguez, Apr 18 '19 at 09:27
The *other* option is to strip the outer property. That's how the XML aggregation tricks work too - convert the result to XML with dummy element names, strip the root element and replace any garbage — Panagiotis Kanavos, Apr 18 '19 at 09:27
Possible duplicate of [SQL to JSON - array of objects to array of values in SQL 2016](https://stackoverflow.com/questions/37708638/sql-to-json-array-of-objects-to-array-of-values-in-sql-2016) — Panagiotis Kanavos, Apr 18 '19 at 09:29
@PanagiotisKanavos that's a useful question, thanks! Should I delete my Q? Mention the link you provided and state that mine is a duplicate? — GrumpyRodriguez, Apr 18 '19 at 09:33
No, because actually getting the string right isn't that easy when the contents are JSON themselves. Try the techniques first to see which one actually works — Panagiotis Kanavos, Apr 18 '19 at 09:35

Zhorov · Accepted Answer · 2019-04-18T10:02:03.970

If I understand you correctly, next approach may help. Just select your json data with OPENJSON() and WITH clause, then format the results with FOR JSON:

-- Table
declare @Json_Snippets table (id int, json nvarchar(max));
insert into @Json_Snippets (id, json) VALUES (1, '{"root":{"obj":"one"}}');
insert into @Json_Snippets (id, json) VALUES (1, '{"root":{"obj":"two"}}');

-- Statement
SELECT j.[root]
FROM @Json_Snippets t
CROSS APPLY OPENJSON(t.json, '$') WITH ([root] nvarchar(max) AS JSON) j
FOR JSON AUTO

Output:

[{"root":{"obj":"one"}},{"root":{"obj":"two"}}]

score 2 · Answer 2 · answered Apr 18 '19 at 09:56

If you know the root key(s) you can use OPENJSON() WITH (... AS JSON) to extract their values as json and recreate json using FOR JSON. Here is an example:

DECLARE @json_snippets TABLE (id INT, json NVARCHAR(MAX));
INSERT INTO @json_snippets (id, json) VALUES
(1, '{"root":{"obj":"one"}}'),
(1, '{"root":{"obj":"two","foo":"bar"}}'),
(2, '{"root":{"obj":"three"}}');

SELECT id, (
    SELECT j.*
    FROM @json_snippets AS x
    CROSS APPLY OPENJSON(json) WITH (
        root NVARCHAR(MAX) AS JSON
    ) AS j
    WHERE id = t.id
    FOR JSON AUTO
)
FROM @json_snippets AS t
GROUP BY id

thanks a lot, @zhorov seems to have given the earlier response so I chose his. I know the root keys all the time indeed, so this solution will work. — GrumpyRodriguez, Apr 18 '19 at 10:42

score 0 · Answer 3 · edited Oct 30 '20 at 22:28

DECLARE @json_snippets TABLE (person INT, [json] NVARCHAR(MAX));
INSERT INTO @json_snippets (person, json) VALUES
(1, '{"obj":"one"}'),
(1, '{"obj":"two","foo":"bar"}'),
(2, '{"obj":"three"}');

SELECT x.person, JSON_QUERY('[' +x.sagg + ']') as objs
FROM (
    SELECT  w.person, STRING_AGG ([json], ',') as sagg
    FROM @json_snippets as w
    GROUP BY w.person
) as x
FOR JSON PATH

The result (with my best attempt at pretty-print):

[
    {"person":1,"objs":[
                 {"obj":"one"}
                 ,{"obj":"two","foo":"bar"}
            ]
    }
    ,{"person":2,"objs":[
                {"obj":"three"}
            ]
    }
]

Can I use FOR JSON without a source column name becoming a key in SQL Server 2016?

3 Answers3