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"}}]