SQL 2016 has a new feature which converts data on SQL server to JSON. I am having difficulty in combining array of objects into array of values i.e.,
EXAMPLE -
CREATE TABLE #temp (item_id VARCHAR(256))
INSERT INTO #temp VALUES ('1234'),('5678'),('7890')
SELECT * FROM #temp
--convert to JSON
SELECT (SELECT item_id
FROM #temp
FOR JSON PATH,root('ids'))
RESULT -
{
"ids": [{
"item_id": "1234"
},
{
"item_id": "5678"
},
{
"item_id": "7890"
}]
}
But I want the result as -
"ids": [
"1234",
"5678",
"7890"
]
Can somebody please help me out?
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