Validación, consulta y cambio de datos JSON con funciones integradas (SQL Server)

Se aplica a: SQL Server 2016 (13.x) y versiones posteriores Azure SQL Database Azure SQL Managed Instance

La compatibilidad integrada con JSON incluye las siguientes funciones integradas que se describen brevemente en este artículo.

• ISJSON prueba si una cadena contiene un valor JSON válido.
• JSON_VALUE extrae un valor escalar de una cadena JSON.
• JSON_QUERY extrae un objeto o una matriz de una cadena JSON.
• JSON_MODIFY actualiza el valor de una propiedad en una cadena JSON y devuelve la cadena JSON actualizada.

Para todas las funciones JSON, revisa Funciones JSON.

Los ejemplos de código de Transact-SQL de este artículo utilizan la base de datos de ejemplo AdventureWorks2022 o AdventureWorksDW2022, que se pueden descargar desde la página principal de Ejemplos y proyectos de la comunidad de Microsoft SQL Server.

Texto de JSON para los ejemplos de esta página

En los ejemplos de esta página se usa el texto JSON similar al contenido que se muestra en el ejemplo siguiente:

{
    "id": "DesaiFamily",
    "parents": [
        { "familyName": "Desai", "givenName": "Prashanth" },
        { "familyName": "Miller", "givenName": "Helen" }
    ],
    "children": [
        {
            "familyName": "Desai",
            "givenName": "Jesse",
            "gender": "female",
            "grade": 1,
            "pets": [
                { "givenName": "Goofy" },
                { "givenName": "Shadow" }
            ]
        },
        {
            "familyName": "Desai",
            "givenName": "Lisa",
            "gender": "female",
            "grade": 8
        }
    ],
    "address": {
        "state": "NY",
        "county": "Manhattan",
        "city": "NY"
    },
    "creationDate": 1431620462,
    "isRegistered": false
}

Este documento JSON, que contiene elementos complejos anidados, se almacena en la tabla de ejemplo siguiente:

CREATE TABLE Families (
    id INT identity CONSTRAINT PK_JSON_ID PRIMARY KEY,
    [doc] NVARCHAR(MAX)
);

Las funciones JSON funcionan igual si el documento JSON se almacena en varchar, nvarchar o en el tipo de datos json nativo.

Validar texto JSON mediante la función ISJSON

La función ISJSON prueba si una cadena contiene un valor JSON válido.

En el ejemplo siguiente, se devuelven las filas en las que la columna JSON contiene texto JSON válido. Sin una restricción JSON explícita, puede escribir cualquier texto en la columna nvarchar:

SELECT *
FROM Families
WHERE ISJSON(doc) > 0;

Para obtener más información, consulta ISJSON.

Extraer un valor de texto JSON mediante la función JSON_VALUE

La función JSON_VALUE extrae un valor escalar de una cadena JSON. La consulta siguiente devuelve los documentos en los que el campo JSON id coincida con el valor DesaiFamily, ordenados por city y los campos JSON state:

SELECT JSON_VALUE(f.doc, '$.id') AS Name,
    JSON_VALUE(f.doc, '$.address.city') AS City,
    JSON_VALUE(f.doc, '$.address.county') AS County
FROM Families f
WHERE JSON_VALUE(f.doc, '$.id') = N'DesaiFamily'
ORDER BY JSON_VALUE(f.doc, '$.address.city') DESC,
    JSON_VALUE(f.doc, '$.address.state') ASC

Los resultados de esta consulta se muestran en la tabla siguiente:

Para obtener más información, consulta JSON_VALUE.

Extraer un objeto o una matriz de texto JSON mediante la función JSON_QUERY

La función JSON_QUERY extrae un objeto o una matriz de una cadena JSON. En el ejemplo siguiente se muestra cómo devolver un fragmento de JSON en los resultados de la consulta.

SELECT JSON_QUERY(f.doc, '$.address') AS Address,
    JSON_QUERY(f.doc, '$.parents') AS Parents,
    JSON_QUERY(f.doc, '$.parents[0]') AS Parent0
FROM Families f
WHERE JSON_VALUE(f.doc, '$.id') = N'DesaiFamily';

Los resultados de esta consulta se muestran en la tabla siguiente:

Para obtener más información, consulta JSON_QUERY.

Análisis de colecciones JSON anidadas

La función OPENJSON permite transformar la submatriz JSON en el conjunto de filas y, después, combinarlo con el elemento primario. Como ejemplo, puede devolver todos los documentos de la familia y "unirlos" con sus objetos children que se almacenan como una matriz de JSON interna:

SELECT JSON_VALUE(f.doc, '$.id') AS Name,
    JSON_VALUE(f.doc, '$.address.city') AS City,
    c.givenName,
    c.grade
FROM Families f
CROSS APPLY OPENJSON(f.doc, '$.children') WITH (
    grade INT,
    givenName NVARCHAR(100)
) c

Los resultados de esta consulta se muestran en la tabla siguiente:

Se devuelven dos filas, porque una fila primaria se combina con dos filas secundarias generadas mediante el análisis de dos elementos de la submatriz secundaria. La función OPENJSON analiza el fragmento children de la columna doc y devuelve grade y givenName de cada elemento como un conjunto de filas. Este conjunto de filas se puede combinar con el documento primario.

Consulta de submatrices JSON jerárquicas anidadas

Puede aplicar varias llamadas CROSS APPLY OPENJSON para consultar estructuras JSON anidadas. El documento JSON que se usa en este ejemplo tiene una matriz anidada denominada children, donde cada elemento secundario tiene una matriz anidada de pets. La consulta siguiente analiza los elementos secundarios de cada documento, devuelve cada objeto de matriz como una fila y, después, analiza la matriz pets:

SELECT c.familyName,
    c.givenName AS childGivenName,
    p.givenName AS petName
FROM Families f
CROSS APPLY OPENJSON(f.doc) WITH (
    familyName NVARCHAR(100),
    children NVARCHAR(MAX) AS JSON
) AS a
CROSS APPLY OPENJSON(children) WITH (
    familyName NVARCHAR(100),
    givenName NVARCHAR(100),
    pets NVARCHAR(max) AS JSON
) AS c
OUTER APPLY OPENJSON(pets) WITH (givenName NVARCHAR(100)) AS p;

La primera llamada a OPENJSON devuelve un fragmento de la matriz children mediante la cláusula AS JSON. Este fragmento de matriz se proporciona a la segunda función OPENJSON que devuelve givenName, firstName de cada elemento secundario, así como la matriz de pets. La matriz de pets se proporciona a la tercera función OPENJSON, que devuelve el valor givenName de la mascota.

Los resultados de esta consulta se muestran en la tabla siguiente:

El documento raíz se combina con dos filas children devueltas por la primera llamada a OPENJSON(children), lo que genera dos filas (o tuplas). Después, cada fila se combina con las filas nuevas generadas por OPENJSON(pets) mediante el operador OUTER APPLY. Jesse tiene dos mascotas, por lo que (Desai, Jesse) se combina con dos filas generadas para Goofy y Shadow. Lisa no tiene mascotas, por lo que OPENJSON(pets) no devuelve filas para esta tupla. Pero como usamos OUTER APPLY, obtenemos NULL en la columna. Si se coloca CROSS APPLY en lugar de OUTER APPLY, Lisa no se devolvería en el resultado, porque no hay ninguna fila de mascotas que se pueda combinar con esta tupla.

Comparación de JSON_VALUE y JSON_QUERY

La diferencia clave entre JSON_VALUE y JSON_QUERY es que JSON_VALUE devuelve un valor escalar, mientras que JSON_QUERY devuelve un objeto o una matriz.

Observe el siguiente ejemplo de texto JSON.

{
    "a": "[1,2]",
    "b": [1, 2],
    "c": "hi"
}

En este ejemplo de texto JSON, los miembros de datos "a" y "c" son valores de cadena, mientras que el miembro de datos "b" es una matriz. JSON_VALUE y JSON_QUERY devuelven los resultados siguientes:

Probar JSON_VALUE y JSON_QUERY con la base de datos de ejemplo AdventureWorks

Pruebe las funciones integradas que se describen en este artículo al ejecutar los ejemplos siguientes con la base de datos de ejemplo AdventureWorks2022. A fin de obtener información sobre cómo agregar datos JSON para pruebas ejecutando un script, vea Compatibilidad con la versión limitada de prueba integrada de JSON.

En los ejemplos siguientes, la columna Info de la tabla SalesOrder_json contiene texto JSON.

Ejemplo 1: devolver columnas estándar y datos JSON

La consulta siguiente devuelve valores de las columnas relacionales estándar y de una columna JSON.

SELECT SalesOrderNumber,
    OrderDate,
    Status,
    ShipDate,
    AccountNumber,
    TotalDue,
    JSON_QUERY(Info, '$.ShippingInfo') ShippingInfo,
    JSON_QUERY(Info, '$.BillingInfo') BillingInfo,
    JSON_VALUE(Info, '$.SalesPerson.Name') SalesPerson,
    JSON_VALUE(Info, '$.ShippingInfo.City') City,
    JSON_VALUE(Info, '$.Customer.Name') Customer,
    JSON_QUERY(OrderItems, '$') OrderItems
FROM Sales.SalesOrder_json
WHERE ISJSON(Info) > 0;

Ejemplo 2: agregar y filtrar valores JSON

La consulta siguiente agrega subtotales por nombre de cliente (almacenados en JSON) y estado (almacenado en una columna normal). Luego, filtra los resultados por ciudad (almacenados en JSON) y OrderDate (almacenados en una columna normal).

DECLARE @territoryid INT;
DECLARE @city NVARCHAR(32);

SET @territoryid = 3;
SET @city = N'Seattle';

SELECT JSON_VALUE(Info, '$.Customer.Name') AS Customer,
    Status,
    SUM(SubTotal) AS Total
FROM Sales.SalesOrder_json
WHERE TerritoryID = @territoryid
    AND JSON_VALUE(Info, '$.ShippingInfo.City') = @city
    AND OrderDate > '1/1/2015'
GROUP BY JSON_VALUE(Info, '$.Customer.Name'),
    Status
HAVING SUM(SubTotal) > 1000;

Actualizar valores de propiedad en texto JSON mediante la función JSON_MODIFY

La función JSON_MODIFY actualiza el valor de una propiedad en una cadena JSON y devuelve la cadena JSON actualizada.

En el ejemplo siguiente, se actualiza el valor de una propiedad JSON en una variable que contiene JSON.

SET @info = JSON_MODIFY(@jsonInfo, '$.info.address[0].town', 'London');

Para obtener más información, consulta JSON_MODIFY.

Contenido relacionado

• ISJSON (Transact-SQL)
• JSON_VALUE (Transact-SQL)
• JSON_QUERY (Transact-SQL)
• JSON_MODIFY (Transact-SQL)
• Expresiones de ruta de acceso JSON (SQL Server)
• Tipo de datos JSON