Indexar datos JSON

Aplica a: SQL Server 2016 (13.x) y versiones posteriores Azure SQL DatabaseAzure SQL Managed InstanceBase de datos SQL en Microsoft Fabric

Puedes optimizar tus consultas sobre documentos JSON usando índices estándar de árbol B. Los índices de la base de datos mejoran el rendimiento de las operaciones de filtro y ordenación. Sin índices, el Motor de base de datos tiene que realizar un escaneo completo de la tabla cada vez que consultas datos.

Hay dos formas de indexar datos JSON, dependiendo de cómo los almacenes:

Indexar una columna JSON con un índice JSON

Aplica a: Azure SQL Database, Azure SQL Managed Instance, SQL Server 2025 (17.x) y base de datos SQL en Microsoft Fabric

Puedes crear un índice JSON directamente sobre una columna json . El índice JSON indexa las rutas dentro del documento JSON, por lo que un único índice puede optimizar muchas búsquedas sobre el documento sin una columna calculada separada para cada propiedad.

El siguiente ejemplo crea una tabla con una columna json y un índice JSON en esa columna:

CREATE TABLE Orders
(
    OrderID INT IDENTITY PRIMARY KEY,
    OrderDetails JSON NOT NULL
);

CREATE JSON INDEX IX_OrderDetails
    ON Orders (OrderDetails);

Con el índice JSON implementado, el Motor de base de datos puede utilizarlo para optimizar consultas que utilicen JSON_VALUE, JSON_PATH_EXISTS, o JSON_CONTAINS. Por ejemplo, la siguiente consulta puede usar el índice JSON para filtrar pedidos por nombre del cliente:

SELECT OrderID
FROM Orders
WHERE JSON_VALUE(OrderDetails, '$.Customer.Name') = N'Aaron Campbell';

Para la sintaxis completa, las opciones de índice y la lista completa de predicados soportados, véase CREATE JSON INDEX.

Indexación de propiedades JSON mediante columnas calculadas

Usa un índice de columna calculado cuando almacenes JSON como texto varchar o nvarchar , o cuando quieras exponer una sola propiedad JSON como una columna fuertemente tipada para filtrar o ordenar. Normalmente quieres filtrar o ordenar los resultados de las consultas por una o más propiedades de los documentos JSON.

Example

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 concat(p.FirstName, N' ', p.LastName) AS [Customer.Name],
            p.BusinessEntityID AS [Customer.ID],
            p.[PersonType] AS [Customer.Type],
            soh.SalesOrderID AS [Order.ID],
            soh.SalesOrderNumber AS [Order.Number],
            soh.OrderDate AS [Order.CreationDate],
            soh.TotalDue AS [Order.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 para 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. Sin embargo, no puede hacer referencia directamente a propiedades en 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 declaració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 JSON_VALUE función puede 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. Use tipos int y datetime2 en lugar de 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 de columna calculados tienen en cuenta la intercalación

Una característica importante de los índices de columnas calculadas sobre datos JSON es que son compatibles con la intercalación. El resultado de la función JSON_VALUE que se usa al crear la columna calculada es un valor de texto que hereda su 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 cirílico serbia 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, 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