SQL to JSON - array of objects to array of values in SQL 2016
Martin!
I believe this is an even simpler way of doing it:
SELECT '"ids": ' +
REPLACE(
REPLACE( (SELECT item_id FROM #temp FOR JSON AUTO),'{"item_id":','' ),
'"}','"' )
declare @temp table (item_id VARCHAR(256))
INSERT INTO @temp VALUES ('1234'),('5678'),('7890')
SELECT * FROM @temp
--convert to JSON
select
json_query(QUOTENAME(STRING_AGG('"' + STRING_ESCAPE(item_id, 'json') + '"', char(44)))) as [json]
from @temp
for json path
When we want to concatenate strings as json array then:
escape string - STRING_ESCAPE
concatenate string with comma separator - STRING_AGG, comma ascii code is 44
add quotation it in brackets - QUOTENAME (without param)
return string (with array of elements) as json - JSON_QUERY
Since arrays of primitive values are valid JSON, it seems strange that a facility for selecting arrays of primitive values isn't built into SQL Server's JSON functionality. (If on the contrary such functionality exists, I at least haven't been able to discover it after quite a bit of searching).
The approach outlined above works as described. But when applied for a field in a larger query, the array of primitives is surrounded with quotes.
E.g., this
DECLARE @BomTable TABLE (ChildNumber dbo.udt_ConMetPartNumber);
INSERT INTO @BomTable (ChildNumber) VALUES (N'101026'), (N'101027');
SELECT N'"Children": ' + REPLACE(REPLACE((SELECT ChildNumber FROM @BomTable FOR JSON PATH), N'{"ChildNumber":', N''), '"}','');
works by producing:
"Children": ["101026,"101027]
But, following the approach above, this:
SELECT
p.PartNumber,
p.Description,
REPLACE(REPLACE((SELECT
ChildNumber
FROM
Part.BillOfMaterials
WHERE
ParentNumber = p.PartNumber
ORDER BY
ChildNumber
FOR
JSON AUTO
), N'{"ChildNumber":', N''), '"}', '"') AS [Children]
FROM
Part.Parts AS p
WHERE
p.PartNumber = N'104444'
FOR
JSON PATH
Produces:
[
{
"PartNumber": "104444",
"Description": "ASSY HUB R-SER DRIV HP10 ABS",
"Children": "[\"101026\",\"101027\",\"102291\",\"103430\",\"103705\",\"104103\"]"
}
]
Where the Children array is wrapped as a string.
Thanks! The soultion we found is converting into XML first -
SELECT
JSON_QUERY('[' + STUFF(( SELECT ',' + '"' + item_id + '"'
FROM #temp FOR XML PATH('')),1,1,'') + ']' ) ids
FOR JSON PATH , WITHOUT_ARRAY_WRAPPER