SQL to JSON - array of objects to array of values in SQL 2016
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
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 JSONselect json_query(QUOTENAME(STRING_AGG('"' + STRING_ESCAPE(item_id, 'json') + '"', char(44)))) as [json]from @tempfor json pathWhen 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