How to Convert a SQL Server Array of Objects to an Array of Values in JSON?

SQL Server 2016 JSON Conversion: Transforming Object Arrays into Value Arrays

SQL Server 2016's JSON capabilities are powerful, but converting arrays of objects to arrays of values requires a specific approach. Let's illustrate the challenge and solution.

Consider this scenario:

<code class="language-sql">CREATE TABLE #temp (item_id VARCHAR(256))

INSERT INTO #temp VALUES ('1234'),('5678'),('7890')

SELECT * FROM #temp

-- Initial JSON conversion attempt
SELECT (SELECT item_id 
FROM #temp
FOR JSON PATH,root('ids')) </code>

This yields a JSON array of objects:

<code class="language-json">{
    "ids": [{
        "item_id": "1234"
    },
    {
        "item_id": "5678"
    },
    {
        "item_id": "7890"
    }]
}</code>

The goal, however, is a JSON array of values:

<code class="language-json">{
  "ids": [
    "1234",
    "5678",
    "7890"
  ]
}</code>

The key is to leverage XML as an intermediate step:

<code class="language-sql">SELECT  
JSON_QUERY('[' + STUFF(( SELECT ',' + '"' + item_id + '"' 
FROM #temp FOR XML PATH('')),1,1,'') + ']' ) ids  
FOR JSON PATH , WITHOUT_ARRAY_WRAPPER </code>

This query first constructs an XML string representing the values, then uses STUFF to remove the leading comma, and finally wraps the result in square brackets to form a JSON array. The FOR JSON PATH, WITHOUT_ARRAY_WRAPPER clause ensures the output is a single JSON array of values, not an array containing a single object. This efficiently achieves the desired JSON structure.

The above is the detailed content of How to Convert a SQL Server Array of Objects to an Array of Values in JSON?. For more information, please follow other related articles on the PHP Chinese website!