Microsoft SQL Server
OPENJSON

Obtener clave: pares de valores de texto JSON

La función OPENJSON analiza el texto JSON y devuelve todos los pares clave: valor en el primer nivel de JSON:

declare @json NVARCHAR(4000) = N'{"Name":"Joe","age":27,"skills":["C#","SQL"]}';
SELECT * FROM OPENJSON(@json);

El tipo de columna describe el tipo de valor, es decir, nulo (0), cadena (1), número (2), booleano (3), matriz (4) y objeto (5).

Transformar la matriz JSON en un conjunto de filas

La función OPENJSON analiza la colección de objetos JSON y devuelve valores del texto JSON como un conjunto de filas.

declare @json nvarchar(4000) = N'[
  {"Number":"SO43659","Date":"2011-05-31T00:00:00","Customer": "MSFT","Price":59.99,"Quantity":1},
  {"Number":"SO43661","Date":"2011-06-01T00:00:00","Customer":"Nokia","Price":24.99,"Quantity":3}
]'

SELECT    * 
FROM OPENJSON (@json)
    WITH (
          Number   varchar(200),
          Date     datetime,
          Customer varchar(200),
          Quantity int
  )

En la cláusula WITH se especifica el esquema de retorno de la función OPENJSON. Las claves en los objetos JSON se recuperan por nombres de columna. Si alguna clave en JSON no se especifica en la cláusula WITH (por ejemplo, Precio en este ejemplo) se ignorará. Los valores se convierten automáticamente en tipos especificados.

Transformar los campos JSON anidados en un conjunto de filas

La función OPENJSON analiza la colección de objetos JSON y devuelve valores del texto JSON como un conjunto de filas. Si los valores en el objeto de entrada están anidados, se puede especificar un parámetro de mapeo adicional en cada columna en la cláusula WITH:

declare @json nvarchar(4000) = N'[
  {"data":{"num":"SO43659","date":"2011-05-31T00:00:00"},"info":{"customer":"MSFT","Price":59.99,"qty":1}},
  {"data":{"number":"SO43661","date":"2011-06-01T00:00:00"},"info":{"customer":"Nokia","Price":24.99,"qty":3}}
]'

SELECT    * 
FROM OPENJSON (@json)
    WITH (
          Number   varchar(200) '$.data.num',
          Date     datetime '$.data.date',
          Customer varchar(200) '$.info.customer',
          Quantity int '$.info.qty',
  )

En la cláusula WITH se especifica el esquema de retorno de la función OPENJSON. Después de que el tipo se especifique, la ruta a los nodos JSON donde se debe encontrar el valor devuelto. Las claves en los objetos JSON son recuperadas por estas rutas. Los valores se convierten automáticamente en tipos especificados.

Extracción de subobjetos JSON internos

OPENJSON puede extraer fragmentos de objetos JSON dentro del texto JSON. En la definición de columna que hace referencia al subobjeto JSON, establezca el tipo nvarchar (max) y la opción AS JSON:

declare @json nvarchar(4000) = N'[
  {"Number":"SO43659","Date":"2011-05-31T00:00:00","info":{"customer":"MSFT","Price":59.99,"qty":1}},
  {"Number":"SO43661","Date":"2011-06-01T00:00:00","info":{"customer":"Nokia","Price":24.99,"qty":3}}
]'

SELECT    * 
FROM OPENJSON (@json)
    WITH (
          Number   varchar(200),
          Date     datetime,
          Info nvarchar(max) '$.info' AS JSON
  )

La columna de información se asignará al objeto "Info". Los resultados serán:

Trabajar con subarreglas JSON anidadas

JSON puede tener una estructura compleja con matrices internas. En este ejemplo, tenemos una matriz de órdenes con una matriz matriz anidada de artículos de pedido.

declare @json nvarchar(4000) = N'[
  {"Number":"SO43659","Date":"2011-05-31T00:00:00",
    "Items":[{"Price":11.99,"Quantity":1},{"Price":12.99,"Quantity":5}]},
  {"Number":"SO43661","Date":"2011-06-01T00:00:00",
    "Items":[{"Price":21.99,"Quantity":3},{"Price":22.99,"Quantity":2},{"Price":23.99,"Quantity":2}]}
]'

Podemos analizar las propiedades de nivel de raíz utilizando OPENJSON que devolverá el arreglo de elementos según el fragmento JSON. Luego podemos aplicar OPENJSON nuevamente en la matriz de elementos y abrir la tabla JSON interna. La tabla de primer nivel y la tabla interna se "unirán" como en UNIR entre tablas estándar:

SELECT    * 
FROM
    OPENJSON (@json)
    WITH (  Number varchar(200), Date datetime,
            Items nvarchar(max) AS JSON )
        CROSS APPLY 
            OPENJSON (Items)
            WITH ( Price float, Quantity int)

Resultados: