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

SQL to JSON conversion: Convert array of objects to array of values ​​in SQL 2016

SQL 2016 provides the function of converting SQL Server data to JSON, expanding many possibilities. However, when dealing with arrays of objects, sometimes the required JSON structure may differ from the default conversion result.

Specifically, when converting an array of objects to JSON, the default behavior is to embed the object as its own array within the parent array. For example:

<code class="language-sql">SELECT (SELECT item_id FROM #temp FOR JSON PATH,root('ids'))</code>

Result:

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

In some cases it may be preferable to display an array of objects as an array of values, without nested structures.

To achieve this, an intermediate XML transformation can be used:

<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 step converts the data into a flattened XML structure, which can then be converted to JSON using the JSON_QUERY() and WITHOUT_ARRAY_WRAPPER parameters. The result is the desired array containing the values:

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

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