SQL Server 2016: Transformando as matrizes de objeto JSON em matrizes de valores

Os recursos JSON do SQL Server 2016 são robustos, mas extrair estruturas de dados específicas às vezes requer abordagens criativas. Este exemplo demonstra a transformação de uma matriz JSON de objetos em uma variedade mais simples de valores.

o desafio:

Considere uma consulta que gera uma matriz JSON de objetos:

SELECT (SELECT item_id 
FROM #temp
FOR JSON PATH, root('ids')) 

isso produz json como este:

{
  "ids": [
    {"item_id": "1234"},
    {"item_id": "5678"},
    {"item_id": "7890"}
  ]
}

O objetivo é reestruturar isso em uma matriz json contendo apenas o item_id valores:

{
  "ids": [
    "1234",
    "5678",
    "7890"
  ]
}

A solução:

A chave é um processo de duas etapas que alavancam o XML como um intermediário:

SELECT  
JSON_QUERY('['   STUFF(( SELECT ','   '"'   item_id   '"' 
FROM #temp FOR XML PATH('')),1,1,'')   ']' ) ids  
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER 

Este código primeiro usa para xml path ('') para concatenar os valores item_id , em torno de cada uma com cotações duplas. Stuff remove a vírgula líder. O resultado é então embrulhado em colchetes para criar uma matriz JSON válida, que json_query passa. Finalmente, para JSON PATH, sem_array_wrapper formate a saída como uma simples array json. Isso efetivamente achata a matriz de objeto JSON original na matriz de valor desejada.