How to Extract an Array of Values from an Array of JSON Objects in SQL Server 2016?

SQL Server 2016: Transforming JSON Object Arrays into Value Arrays

SQL Server 2016's JSON capabilities are robust, but extracting specific data structures sometimes requires creative approaches. This example demonstrates transforming a JSON array of objects into a simpler array of values.

The Challenge:

Consider a query that generates a JSON array of objects:

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

This yields JSON like this:

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

The goal is to restructure this into a JSON array containing only the item_id values:

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

The Solution:

The key is a two-step process leveraging XML as an intermediary:

<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 code first uses FOR XML PATH('') to concatenate the item_id values, surrounding each with double quotes. STUFF removes the leading comma. The result is then wrapped in square brackets to create a valid JSON array, which JSON_QUERY parses. Finally, FOR JSON PATH, WITHOUT_ARRAY_WRAPPER formats the output as a simple JSON array. This effectively flattens the original JSON object array into the desired value array.

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