Microsoft SQL Server
JSON en Sql Server

Sintaxis

• JSON_VALUE (expresión, ruta): extrae un valor escalar de una cadena JSON.
• JSON_QUERY (expresión [, ruta]) - Extrae un objeto o una matriz de una cadena JSON.
• OPENJSON (jsonExpression [, ruta]) - función de valor de tabla que analiza el texto JSON y devuelve objetos y propiedades en JSON como filas y columnas.
• ISJSON (expresión): comprueba si una cadena contiene JSON válido.
• JSON_MODIFY (expresión, ruta, newValue): actualiza el valor de una propiedad en una cadena JSON y devuelve la cadena JSON actualizada.

Parámetros

Observaciones

ALTER DATABASE <Database-Name-Here> SET COMPATIBILITY_LEVEL = 130

Formato de resultados de consultas como JSON con FOR JSON

Datos de la tabla de entrada (tabla de personas)

Consulta

SELECT Id, Name, Age
FROM People
FOR JSON PATH

Resultado

[
    {"Id":1,"Name":"John","Age":23},
    {"Id":2,"Name":"Jane","Age":31}
]

Analizar texto JSON

Las funciones JSON_VALUE y JSON_QUERY analizan el texto JSON y devuelven valores escalables u objetos / matrices en la ruta en el texto JSON.

DECLARE @json NVARCHAR(100) = '{"id": 1, "user":{"name":"John"}, "skills":["C#","SQL"]}'

SELECT
    JSON_VALUE(@json, '$.id') AS Id,
    JSON_VALUE(@json, '$.user.name') AS Name,
    JSON_QUERY(@json, '$.user') AS UserObject,
    JSON_QUERY(@json, '$.skills') AS Skills,
    JSON_VALUE(@json, '$.skills[0]') AS Skill0

Resultado

Únase a las entidades JSON principales y secundarias utilizando CROSS APPLY OPENJSON

Unir los objetos principales con sus entidades secundarias, por ejemplo, queremos una tabla relacional de cada persona y sus aficiones

DECLARE @json nvarchar(1000) =
N'[
    {
        "id":1,
        "user":{"name":"John"},
        "hobbies":[
            {"name": "Reading"},
            {"name": "Surfing"}
        ]
    },
    {
        "id":2,
        "user":{"name":"Jane"},
        "hobbies":[
            {"name": "Programming"},
            {"name": "Running"}
        ]
    }
 ]'

Consulta

SELECT 
    JSON_VALUE(person.value, '$.id') as Id,
    JSON_VALUE(person.value, '$.user.name') as PersonName,
    JSON_VALUE(hobbies.value, '$.name') as Hobby
FROM OPENJSON (@json) as person
    CROSS APPLY OPENJSON(person.value, '$.hobbies') as hobbies

Alternativamente, esta consulta puede escribirse usando la cláusula WITH.

SELECT 
    Id, person.PersonName, Hobby
FROM OPENJSON (@json)
WITH(
    Id int '$.id',
    PersonName nvarchar(100) '$.user.name',
    Hobbies nvarchar(max) '$.hobbies' AS JSON
) as person
CROSS APPLY OPENJSON(Hobbies)
WITH(
    Hobby nvarchar(100) '$.name'
)

Resultado

Índice en las propiedades JSON mediante el uso de columnas calculadas

Al almacenar documentos JSON en SQL Server, debemos poder filtrar y clasificar de manera eficiente los resultados de las consultas en las propiedades de los documentos JSON.

CREATE TABLE JsonTable
(
    id int identity primary key,
    jsonInfo nvarchar(max),
    CONSTRAINT [Content should be formatted as JSON]
    CHECK (ISJSON(jsonInfo)>0)
)

INSERT INTO JsonTable
VALUES(N'{"Name":"John","Age":23}'),
(N'{"Name":"Jane","Age":31}'),
(N'{"Name":"Bob","Age":37}'),
(N'{"Name":"Adam","Age":65}')
GO

Dada la tabla anterior Si queremos encontrar la fila con el nombre = 'Adam', ejecutaríamos la siguiente consulta.

SELECT * 
FROM JsonTable Where 
JSON_VALUE(jsonInfo, '$.Name') = 'Adam'

Sin embargo, esto requerirá que el servidor SQL realice una tabla completa que en una tabla grande no es eficiente.

Para acelerar esto, nos gustaría agregar un índice, sin embargo, no podemos hacer referencia directamente a las propiedades en el documento JSON. La solución es agregar una columna computada en la ruta JSON $.Name , luego agregar un índice en la columna computada.

ALTER TABLE JsonTable
ADD vName as JSON_VALUE(jsonInfo, '$.Name')

CREATE INDEX idx_name
ON JsonTable(vName)

Ahora, cuando ejecutamos la misma consulta, en lugar de una tabla completa, el servidor SQL usa un índice para buscar en el índice no agrupado y encontrar las filas que satisfacen las condiciones especificadas.

Nota: para que el servidor SQL use el índice, debe crear la columna calculada con la misma expresión que planea usar en sus consultas; en este ejemplo, JSON_VALUE(jsonInfo, '$.Name') , también puede usar el nombre de la columna computada vName

Formatee una fila de la tabla como un solo objeto JSON usando FOR JSON

La opción WITHOUT_ARRAY_WRAPPER en la cláusula FOR JSON eliminará los soportes de matriz de la salida JSON. Esto es útil si está devolviendo una sola fila en la consulta.

Nota: esta opción producirá una salida JSON no válida si se devuelve más de una fila.

Datos de la tabla de entrada (tabla de personas)

Consulta

SELECT Id, Name, Age
FROM People
WHERE Id = 1
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

Resultado

{"Id":1,"Name":"John","Age":23}

Analizar texto JSON utilizando la función OPENJSON.

La función OPENJSON analiza el texto JSON y devuelve varias salidas. Los valores que deben devolverse se especifican utilizando las rutas definidas en la cláusula WITH. Si no se especifica una ruta para alguna columna, el nombre de la columna se usa como una ruta. Esta función convierte valores devueltos a los tipos de SQL definidos en la cláusula WITH. La opción AS JSON debe especificarse en la definición de columna si se debe devolver algún objeto / matriz.

DECLARE @json NVARCHAR(100) = '{"id": 1, "user":{"name":"John"}, "skills":["C#","SQL"]}'

SELECT * 
FROM OPENJSON (@json)
    WITH(Id int '$.id',
        Name nvarchar(100) '$.user.name',
        UserObject nvarchar(max) '$.user' AS JSON,
        Skills nvarchar(max) '$.skills' AS JSON,
        Skill0 nvarchar(20) '$.skills[0]')

Resultado