Indexar dados JSON

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores Azure SQL Database AzureSQL Managed Instance SQL database in Microsoft Fabric

Você pode otimizar suas consultas em documentos JSON usando índices padrão.

Note

No SQL Server 2025 (17.x), pode usar a funcionalidade CRIAR ÍNDICE JSON (Transact-SQL).

Os índices funcionam da mesma forma em dados JSON no varchar/nvarchar ou no nativo json tipo de dados.

Os índices de banco de dados melhoram o desempenho das operações de filtragem e classificação. Sem índices, o SQL Server precisa executar uma verificação de tabela completa toda vez que você consulta dados.

Note

O tipo de dados JSON:

está geralmente disponível para o Banco de Dados SQL do Azure e a Instância Gerenciada SQL do Azure com a política de atualizaçãodo SQL Server 2025 ou Always-up-to-date.
está em pré-visualização para SQL Server 2025 (17.x) e para a base de dados SQL em Fabric.

Indexar propriedades JSON usando colunas computadas

Quando você armazena dados JSON no SQL Server, normalmente deseja filtrar ou classificar os resultados da consulta por uma ou mais propriedades dos documentos JSON.

Example

Neste exemplo, suponha que a tabela AdventureWorks.SalesOrderHeader tenha uma coluna Info que contenha várias informações no formato JSON sobre ordens de venda. Por exemplo, ele contém dados não estruturados sobre clientes, vendedores, endereços de entrega e faturamento, e assim por diante. Você pode usar valores da coluna Info para filtrar ordens de venda de um cliente.

Por padrão, a coluna Info usada não existe, ela pode ser criada no AdventureWorks banco de dados com o código a seguir. Os exemplos a seguir não se aplicam à AdventureWorksLT série de bancos de dados de exemplo.

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 para otimizar

Aqui está um exemplo do tipo de consulta que você deseja otimizar usando um índice.

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

Exemplo de índice

Se quiser acelerar os filtros ou ORDER BY cláusulas sobre uma propriedade em um documento JSON, você pode usar os mesmos índices que já está usando em outras colunas. No entanto, não é possível diretamente propriedades de referência nos documentos JSON.

Primeiro, crie uma "coluna virtual" que retorne os valores que você deseja usar para filtragem.
Em seguida, crie um índice nessa coluna virtual.

O exemplo a seguir cria uma coluna computada que pode ser usada para indexação. Em seguida, ele cria um índice na nova coluna calculada. Este exemplo cria uma coluna que expõe o nome do cliente, que é armazenado no caminho $.Customer.Name nos dados JSON.

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

CREATE INDEX idx_soh_json_CustomerName
    ON Sales.SalesOrderHeader(vCustomerName);

Esta instrução retorna o seguinte aviso:

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.

A função JSON_VALUE pode retornar valores de texto de até 8000 bytes (por exemplo, como o nvarchar(4000) tipo). No entanto, os valores com mais de 1700 bytes não podem ser indexados. Se você tentar inserir o valor na coluna computada indexada que é maior que 1700 bytes, a operação de linguagem de manipulação de dados (DML) falhará.

Para obter um melhor desempenho, tente converter o valor exposto usando uma coluna computada no menor tipo de dados aplicável. Use int e tipos de datetime2 em vez de tipos de cadeia de caracteres.

Mais informações sobre a coluna computada

Uma coluna computada não é persistente. Uma coluna computada só é computada quando o índice precisa ser reconstruído. Não ocupa espaço adicional na mesa.

É importante que você crie a coluna computada com a mesma expressão que planeja usar em suas consultas - neste exemplo, a expressão é JSON_VALUE(Info, '$.Customer.Name').

Você não precisa reescrever suas consultas. Se você usar expressões com a função JSON_VALUE, conforme mostrado na consulta de exemplo anterior, o SQL Server verá que há uma coluna computada equivalente com a mesma expressão e aplicará um índice, se possível.

Plano de execução para este exemplo

Aqui está o plano de execução para a consulta neste exemplo.

Captura de tela mostrando o plano de execução para este exemplo.

Em vez de uma verificação de tabela completa, o SQL Server usa uma busca de índice no índice não clusterizado e localiza as linhas que satisfazem as condições especificadas. Em seguida, ele usa uma pesquisa de chave na tabela SalesOrderHeader para buscar as outras colunas que são referenciadas na consulta - neste exemplo, SalesOrderNumber e OrderDate.

Otimize ainda mais o índice com colunas incluídas

Se você adicionar colunas necessárias no índice, poderá evitar essa pesquisa extra na tabela. Você pode adicionar essas colunas como colunas incluídas padrão, conforme mostrado no exemplo a seguir, que estende o exemplo anterior CREATE INDEX.

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

Nesse caso, o SQL Server não precisa ler mais dados da tabela porque tudo o SalesOrderHeader que ele precisa está incluído no índice JSON não clusterizado. Esse tipo de índice é uma boa maneira de combinar dados JSON e de coluna em consultas e criar índices ideais para sua carga de trabalho.

Os índices JSON são índices com reconhecimento de agrupamento

Uma característica importante dos índices sobre dados JSON é que os índices reconhecem agrupamento. O resultado da função JSON_VALUE que você usa ao criar a coluna computada é um valor de texto que herda seu agrupamento da expressão de entrada. Portanto, os valores no índice são ordenados usando as regras de agrupamento definidas nas colunas de origem.

Para demonstrar que os índices reconhecem agrupamento, o exemplo a seguir cria uma tabela de coleta simples com uma chave primária e conteúdo 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)
);

O comando anterior especifica a ordenação cirílica sérvia para a coluna json. O exemplo a seguir preenche a tabela e cria um índice na propriedade "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);

Os comandos anteriores criam um índice padrão na coluna calculada vName, que representa o valor da propriedade JSON $.name. Na página de código cirílico sérvio, a ordem das letras é А, Б, В, Г, Д, Ђ, Е, etc. A ordem dos itens no índice é compatível com as regras cirílicas sérvias porque o resultado da função JSON_VALUE herda seu agrupamento da coluna de origem. O exemplo a seguir consulta essa coleção e classifica os resultados por nome.

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

Se você examinar o plano de execução real, verá que ele usa valores classificados do índice não clusterizado.

Embora a consulta tenha uma cláusula ORDER BY, o plano de execução não usa um operador Sort. O índice JSON já está ordenado de acordo com as regras cirílicas sérvias. Portanto, o SQL Server pode usar o índice não clusterizado onde os resultados já estão classificados.

No entanto, se você alterar o agrupamento da expressão ORDER BY - por exemplo, se você adicionar COLLATE French_100_CI_AS_SC após a função JSON_VALUE - você obterá um plano de execução de consulta diferente.

Como a ordem dos valores no índice não é compatível com as regras de agrupamento em francês, o SQL Server não pode usar o índice para ordenar os resultados. Portanto, ele adiciona um operador Sort que classifica os resultados usando regras de agrupamento em francês.

Vídeos da Microsoft

Para obter uma introdução visual ao suporte JSON interno, consulte o seguinte vídeo:

JSON como uma ponte entre NoSQL e mundos relacionais

Otimize o processamento JSON com OLTP na memória
dados JSON no SQL Server
Tipo de dados JSON

Comentários

Esta página foi útil?

Last updated on 2025-11-18