Meghana Raj Jayanarasimha Meghana Raj Jayanarasimha - 3 months ago 29
JSON Question

SQL to JSON - array of objects to array of values in SQL 2016

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?

Answer

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