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

SQL Server 2016 JSON Conversion: Array of Objects to Array of Values

SQL Server 2016 offers powerful JSON capabilities, simplifying data transformation for reporting and integration. This example addresses a common need: converting a SQL array of objects into a simpler array of values within a JSON structure.

Scenario:

Consider a table, #temp, containing data like this:

#temp Table:

| item_id | |---|---| | 1234 | | 5678 | | 7890 |

The goal is to transform this data into JSON, presenting the item_id values as a single array, not an array of objects.

Target JSON Structure:

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

Solution:

This efficient query achieves the desired conversion:

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

Explanation:

The query cleverly uses FOR XML PATH('') to create a comma-separated string of quoted item_id values. STUFF removes the leading comma. The resulting string is then enclosed in square brackets ([]) to form a valid JSON array, which is parsed using JSON_QUERY. Finally, FOR JSON PATH, WITHOUT_ARRAY_WRAPPER formats the output as the desired JSON object.

Output:

Executing the query produces the target JSON structure:

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

This method provides a concise and effective way to manage JSON array transformations within SQL Server 2016.

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