Microsoft SQL Server
Consultas con datos JSON

Usando valores de JSON en la consulta

La función JSON_VALUE le permite tomar datos del texto JSON en la ruta especificada como segundo argumento y usar este valor en cualquier parte de la consulta de selección:

select ProductID, Name, Color, Size, Price, JSON_VALUE(Data, '$.Type') as Type
from Product
where JSON_VALUE(Data, '$.Type') = 'part'

Usando valores JSON en reportes

Una vez que los valores JSON se extraen del texto JSON, puede usarlos en cualquier parte de la consulta. Puede crear algún tipo de informe sobre datos JSON con agregaciones de agrupación, etc.:

select JSON_VALUE(Data, '$.Type') as type,
        AVG( cast(JSON_VALUE(Data, '$.ManufacturingCost') as float) ) as cost
from Product
group by JSON_VALUE(Data, '$.Type') 
having JSON_VALUE(Data, '$.Type') is not null

Filtra el texto JSON incorrecto de los resultados de la consulta

Si algún texto JSON puede no tener el formato correcto, puede eliminar esas entradas de la consulta mediante la función ISJSON.

select ProductID, Name, Color, Size, Price, JSON_VALUE(Data, '$.Type') as Type
from Product
where JSON_VALUE(Data, '$.Type') = 'part'
and ISJSON(Data) > 0

Actualizar el valor en la columna JSON

La función JSON_MODIFY se puede usar para actualizar el valor en alguna ruta. Puede usar esta función para modificar el valor original de la celda JSON en la declaración UPDATE:

update Product
set Data = JSON_MODIFY(Data, '$.Price', 24.99)
where ProductID = 17;

La función JSON_MODIFY actualizará o creará la clave de precio (si no existe). Si el nuevo valor es NULL, la clave se eliminará. La función JSON_MODIFY tratará el nuevo valor como una cadena (escapa de los caracteres especiales, la envuelve con comillas dobles para crear la cadena JSON adecuada). Si su nuevo valor es el fragmento JSON, debe envolverlo con la función JSON_QUERY:

update Product
set Data = JSON_MODIFY(Data, '$.tags', JSON_QUERY('["promo","new"]'))
where ProductID = 17;

La función JSON_QUERY sin el segundo parámetro se comporta como una "conversión a JSON". Dado que el resultado de JSON_QUERY es un fragmento JSON válido (objeto o matriz), JSON_MODIFY no escapará a este valor cuando modifique la entrada JSON.

Agregar nuevo valor a la matriz JSON

La función JSON_MODIFY se puede usar para agregar un nuevo valor a alguna matriz dentro de JSON:

update Product
set Data = JSON_MODIFY(Data, 'append $.tags', "sales")
where ProductID = 17;

Se agregará un nuevo valor al final de la matriz, o se creará una nueva matriz con valor ["ventas"]. La función JSON_MODIFY tratará el nuevo valor como una cadena (escapa de los caracteres especiales, la envuelve con comillas dobles para crear la cadena JSON adecuada). Si su nuevo valor es el fragmento JSON, debe envolverlo con la función JSON_QUERY:

update Product
set Data = JSON_MODIFY(Data, 'append $.tags', JSON_QUERY('{"type":"new"}'))
where ProductID = 17;

La función JSON_QUERY sin el segundo parámetro se comporta como una "conversión a JSON". Dado que el resultado de JSON_QUERY es un fragmento JSON válido (objeto o matriz), JSON_MODIFY no escapará a este valor cuando modifique la entrada JSON.

Mesa JOIN con colección JSON interior

Si tiene una "tabla secundaria" formateada como colección JSON y almacenada en fila como columna JSON, puede descomprimir esta colección, transformarla en tabla y unirla a la fila principal. En lugar del operador estándar JOIN, debe usar CROSS APPLY. En este ejemplo, las partes del producto se formatean como una colección de objetos JSON y se almacenan en la columna de datos:

select ProductID, Name, Size, Price, Quantity, PartName, Code
from Product
    CROSS APPLY OPENJSON(Data, '$.Parts') WITH (PartName varchar(20), Code varchar(5))

El resultado de la consulta es equivalente a la unión entre las tablas de Producto y Parte.

Encontrar filas que contienen valor en la matriz JSON

En este ejemplo, la matriz de etiquetas puede contener varias palabras clave como ["promo", "ventas"], por lo que podemos abrir esta matriz y filtrar los valores:

select ProductID, Name, Color, Size, Price, Quantity
from Product
    CROSS APPLY OPENJSON(Data, '$.Tags') 
where value = 'sales'

OPENJSON abrirá la colección interna de etiquetas y la devolverá como tabla. Luego podemos filtrar los resultados por algún valor en la tabla.