Indexación de datos JSON

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

Puede optimizar las consultas en documentos JSON mediante índices estándar.

El tipo de datos JSON:

• está disponible con carácter general para Azure SQL Database e Instancia administrada de Azure SQL configurada con la directiva de actualización always-up-to-date.
• está en versión preliminar para la versión preliminar de SQL Server 2025 (17.x).

Nota:

En la versión preliminar de SQL Server 2025 (17.x), puede usar la característica CREATE JSON INDEX .

Los índices funcionan de la misma manera en los datos JSON en varchar/nvarchar o en el tipo de datos json nativo.

Los índices de la base de datos mejoran el rendimiento de las operaciones de filtro y ordenación. Sin ellos, SQL Server debe realizar un examen completo de la tabla cada vez que realice consultas de datos.

Indexación de propiedades JSON mediante columnas calculadas

Al almacenar datos JSON en SQL Server, normalmente querrá filtrar u ordenar resultados de consultas por una o varias propiedades de los documentos JSON.

Ejemplo

En este ejemplo se supone que la tabla AdventureWorks.SalesOrderHeader tiene una columna Info, que contiene diversa información en formato JSON sobre pedidos de venta. Por ejemplo, contiene datos no estructurados sobre el cliente, el vendedor, las direcciones de envío y facturación, etc. Podría usar los valores de la columna Info para filtrar los pedidos de venta de un cliente.

De forma predeterminada, la columna Info usada no existe, se puede crear en la AdventureWorks base de datos con el código siguiente. Los ejemplos siguientes no se aplican a la AdventureWorksLT serie de bases de datos de ejemplo.

IF NOT EXISTS (SELECT *
               FROM sys.columns
               WHERE object_id = OBJECT_ID('[Sales].[SalesOrderHeader]')
                     AND name = 'Info')
    ALTER TABLE [Sales].[SalesOrderHeader]
        ADD [Info] NVARCHAR (MAX) NULL;
GO

UPDATE h
SET [Info] =
(
    SELECT [Customer.Name] = concat(p.FirstName, N' ', p.LastName), 
           [Customer.ID] = p.BusinessEntityID, 
           [Customer.Type] = p.[PersonType], 
           [Order.ID] = soh.SalesOrderID, 
           [Order.Number] = soh.SalesOrderNumber, 
           [Order.CreationData] = soh.OrderDate, 
           [Order.TotalDue] = soh.TotalDue
    FROM [Sales].SalesOrderHeader AS soh
        INNER JOIN [Sales].[Customer] AS c
            ON c.CustomerID = soh.CustomerID
        INNER JOIN [Person].[Person] AS p
            ON p.BusinessEntityID = c.CustomerID
    WHERE soh.SalesOrderID = h.SalesOrderID
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
)
FROM [Sales].SalesOrderHeader AS h;

Consulta que se optimizará

Aquí tiene un ejemplo del tipo de consulta que quiere optimizar mediante un índice.

SELECT SalesOrderNumber,
       OrderDate,
       JSON_VALUE(Info, '$.Customer.Name') AS CustomerName
FROM Sales.SalesOrderHeader
WHERE JSON_VALUE(Info, '$.Customer.Name') = N'Aaron Campbell';

Índice de ejemplo

Si quiere acelerar los filtros o las cláusulas ORDER BY en una propiedad de un documento JSON, puede usar los mismos índices que ya use en otras columnas. En cambio, no puede hacer referencia directamente a las propiedades de los documentos JSON.

1. Primero, cree una "columna virtual" que devuelva los valores que quiere utilizar para las operaciones de filtro.
2. Después, cree un índice de esa columna virtual.

En el ejemplo siguiente se crea una columna calculada que se puede usar para la indexación. Después, crea un índice en la nueva columna calculada. En este ejemplo se crea una columna que muestra el nombre del cliente, que se almacena en la ruta de acceso $.Customer.Name de los datos JSON.

ALTER TABLE Sales.SalesOrderHeader
    ADD vCustomerName AS JSON_VALUE(Info, '$.Customer.Name');

CREATE INDEX idx_soh_json_CustomerName
    ON Sales.SalesOrderHeader(vCustomerName);

Esta instrucción devuelve la siguiente advertencia:

Warning! The maximum key length for a nonclustered index is 1700 bytes.
The index 'vCustomerName' has maximum length of 8000 bytes.
For some combination of large values, the insert/update operation will fail.

La función JSON_VALUE podría devolver valores de texto de hasta 8000 bytes, por ejemplo, como el tipo nvarchar(4000). Sin embargo, los valores que tienen más de 1700 bytes no se pueden indexar. Si intenta escribir el valor en la columna calculada indizada que tiene más de 1700 bytes, se produce un error en la operación del lenguaje de manipulación de datos (DML).

Para mejorar el rendimiento, intente convertir el valor que expone con la columna calculada en el tipo de datos aplicable más pequeño. Los tipos int y datetime2 en lugar de los tipos de cadena.

Más información sobre la columna calculada

No se conserva una columna calculada. Una columna calculada solo se calcula cuando es necesario volver a generar el índice. No ocupa espacio adicional en la tabla.

Es importante que cree la columna calculada con la misma expresión que tiene pensado usar en las consultas. En este ejemplo, la expresión es JSON_VALUE(Info, '$.Customer.Name').

No tiene que volver a escribir las consultas. Si se usan expresiones con la función JSON_VALUE, como se muestra en la consulta de ejemplo anterior, SQL Server considera que hay una columna calculada equivalente con la misma expresión y aplica un índice, si es posible.

Plan de ejecución de este ejemplo

Este es el plan de ejecución de la consulta de este ejemplo.

En lugar examinar toda la tabla, SQL Server busca un índice en el índice no agrupado e identifica las filas que satisfacen las condiciones especificadas. Después, realiza una búsqueda de claves en la tabla SalesOrderHeader para capturar las otras columnas a las que se hace referencia en la consulta (en este ejemplo, SalesOrderNumber y OrderDate).

Mayor optimización del índice con la inclusión de columnas

Si agrega columnas necesarias en el índice, puede evitar esta búsqueda adicional en la tabla. Puede agregar estas columnas como columnas incluidas estándar, tal y como se muestra en el ejemplo siguiente, que amplía el ejemplo CREATE INDEX anterior.

CREATE INDEX idx_soh_json_CustomerName
    ON Sales.SalesOrderHeader(vCustomerName)
    INCLUDE(SalesOrderNumber, OrderDate);

En este caso, SQL Server no tiene que leer más datos de la SalesOrderHeader tabla porque todo lo que necesita se incluye en el índice JSON no clúster. Este tipo de índice es una buena forma de combinar datos JSON y de columna en las consultas y de crear índices óptimos para la carga de trabajo.

Los índices JSON son índices de intercalación

Una característica importante de los índices basados en datos JSON es que son compatibles con la intercalación. El resultado de la función JSON_VALUE que usa al crear la columna calculada es un valor de texto que hereda la intercalación de la expresión de entrada. Por tanto, los valores del índice se ordenan con las reglas de intercalación definidas en las columnas de origen.

Para demostrar que los índices son de intercalación, en el ejemplo siguiente se crea una tabla de colección simple con una clave principal y el contenido JSON.

CREATE TABLE JsonCollection
(
    id INT IDENTITY CONSTRAINT PK_JSON_ID PRIMARY KEY,
    [json] NVARCHAR (MAX) COLLATE SERBIAN_CYRILLIC_100_CI_AI
        CONSTRAINT [Content should be formatted as JSON] CHECK (ISJSON(json) > 0)
);

El comando anterior especifica la intercalación del serbio (cirílico) para la columna json. En el ejemplo siguiente se rellena la tabla y crea un índice en la propiedad name.

INSERT INTO JsonCollection
VALUES
    (N'{"name":"Иво","surname":"Андрић"}'),
    (N'{"name":"Андрија","surname":"Герић"}'),
    (N'{"name":"Владе","surname":"Дивац"}'),
    (N'{"name":"Новак","surname":"Ђоковић"}'),
    (N'{"name":"Предраг","surname":"Стојаковић"}'),
    (N'{"name":"Михајло","surname":"Пупин"}'),
    (N'{"name":"Борислав","surname":"Станковић"}'),
    (N'{"name":"Владимир","surname":"Грбић"}'),
    (N'{"name":"Жарко","surname":"Паспаљ"}'),
    (N'{"name":"Дејан","surname":"Бодирога"}'),
    (N'{"name":"Ђорђе","surname":"Вајферт"}'),
    (N'{"name":"Горан","surname":"Бреговић"}'),
    (N'{"name":"Милутин","surname":"Миланковић"}'),
    (N'{"name":"Никола","surname":"Тесла"}');
GO

ALTER TABLE JsonCollection
    ADD vName AS JSON_VALUE(json, '$.name');

CREATE INDEX idx_name
    ON JsonCollection(vName);

Los comandos anteriores crean un índice estándar de la columna calculada vName, que representa el valor de la propiedad JSON $.name. En la página de códigos del serbio cirílico, el orden de las letras es А, Б, В, Г, Д, Ђ, Е, etc. El orden de los elementos del índice cumple las reglas del serbio cirílico porque el resultado de la función JSON_VALUE hereda la intercalación de la columna de origen. En el ejemplo siguiente se realiza una consulta de esta colección y se ordenan los resultados por nombre.

SELECT JSON_VALUE(json, '$.name'),
       *
FROM JsonCollection
ORDER BY JSON_VALUE(json, '$.name');

Si observa el plan de ejecución real, verá que emplea los valores ordenados del índice no agrupado.

Aunque la consulta tiene una cláusula ORDER BY, el plan de ejecución no usa un operador Sort. El índice JSON ya está ordenado según reglas del serbio (cirílico). Por lo tanto, SQL Server puede utilizar el índice no agrupado donde los resultados ya están ordenados.

A pesar de esto, si cambia la intercalación de la expresión ORDER BY (por ejemplo, si agrega COLLATE French_100_CI_AS_SC después de la función JSON_VALUE), recibirá otro plan de ejecución de consulta.

Dado que el orden de los valores del índice no es compatible con las reglas de intercalación francesas, SQL Server no puede usar el índice para ordenar los resultados. Por lo tanto, agrega un operador Sort que ordena los resultados mediante las reglas de intercalación del francés.

Vídeos de Microsoft

Para obtener una introducción visual a la compatibilidad integrada con JSON en SQL Server y Azure SQL Database, consulte el vídeo siguiente:

• JSON as a bridge between NoSQL and relational worlds (JSON como puente entre los universos NoSQL y relacional)

Contenido relacionado

• Optimización del procesamiento de OLTP en memoria JSON
• Datos JSON en SQL Server
• Tipo de datos JSON