Indexar propriedades JSON tipadas no SQL Server

Guardar JSON é conveniente quando uma integração envia atributos variáveis. O problema aparece quando um filtro frequente precisa analisar o documento em muitas linhas. Uma coluna calculada estreita e tipada expõe a propriedade importante a um índice comum preservando o documento original.

Diferencie atributos flexíveis de chaves de negócio. Se a identidade do cliente controla junções, autorização e consultas, merece contrato explícito. JSON não elimina a necessidade de tipo, presença obrigatória e intervalo válido.

Extrair o tipo pretendido

O exemplo utiliza funções JSON do SQL Server 2016 ou posterior e cria objetos permanentes somente em uma base de prática.

-- Create in a disposable practice database.
CREATE TABLE dbo.JsonOrderDemo (
    DocumentId int NOT NULL PRIMARY KEY,
    Payload nvarchar(max) NOT NULL,
    CustomerId AS TRY_CONVERT(bigint, JSON_VALUE(Payload, '$.customerId')) PERSISTED,
    CONSTRAINT CK_JsonOrderDemo_Json CHECK (ISJSON(Payload) = 1),
    CONSTRAINT CK_JsonOrderDemo_Customer CHECK (CustomerId IS NOT NULL AND CustomerId > 0)
);
CREATE INDEX IX_JsonOrderDemo_Customer ON dbo.JsonOrderDemo(CustomerId);
INSERT dbo.JsonOrderDemo(DocumentId, Payload) VALUES
(1, N'{"customerId":42,"status":"new"}'),
(2, N'{"customerId":43,"status":"new"}'),
(3, N'{"customerId":42,"status":"paid"}');
SELECT DocumentId FROM dbo.JsonOrderDemo WHERE CustomerId = 42;

A busca inicial retorna documentos 1 e 3. CustomerId é derivado como bigint, produzindo chaves numéricas em vez de textos extensos. Uma tabela minúscula ainda pode receber scan porque é mais barato. O exemplo oferece um caminho possível, não força um plano.

TRY_CONVERT transforma valores não convertíveis em NULL. O segundo CHECK rejeita explicitamente NULL e valores não positivos. Verificar apenas CustomerId > 0 permitiria UNKNOWN para NULL e não imporia presença.

ISJSON valida sintaxe, não todo o esquema de negócio. Um array válido ou objeto sem customerId não é uma ordem válida neste contrato. O controle da coluna identifica a chave numérica ausente; outros atributos obrigatórios continuam precisando de regras próprias.

A conversão aceita tanto número JSON quanto string numérica convertível. Se a API precisa distinguir representações, valide o tipo de token, por exemplo com OPENJSON. Escolha conscientemente em vez de depender de uma conversão acidental.

Alinhar extração e busca

Consultar CustomerId diretamente esclarece acesso e tipo dos parâmetros. O SQL Server pode reconhecer algumas expressões calculadas equivalentes, mas pequenas diferenças podem mudar essa oportunidade. Um contrato estável é melhor que exigir a mesma expressão JSON de cada consumidor.

Nomes de propriedades JSON distinguem maiúsculas nos caminhos. customerId e CustomerId não são intercambiáveis só porque a collation ignora caixa em comparações comuns. Teste as convenções dos serializadores que produzem os documentos.

JSON_VALUE possui comportamento próprio de tamanho. No caminho nvarchar tradicional, um escalar maior que 4.000 caracteres pode resultar em NULL no modo lax ou erro no strict. Não é a ferramenta para uma descrição ilimitada. OPENJSON pode expor escalares extensos com um esquema apropriado.

Evite indexar nvarchar(4000) quando o dado é realmente um número ou código curto. Limites de chave e custos de comparação continuam existindo. Por outro lado, converter para um texto arbitrariamente curto pode cortar valores significativos; valide tamanho antes de estreitar.

Avaliar gravações e evolução

Atualizar o documento recalcula a propriedade e mantém o índice.

UPDATE dbo.JsonOrderDemo
SET Payload = JSON_MODIFY(Payload, '$.customerId', 43)
WHERE DocumentId = 1;
SELECT DocumentId, CustomerId FROM dbo.JsonOrderDemo ORDER BY DocumentId;

O documento 1 passa ao cliente 43. Não é necessário atualizar separadamente uma coluna espelho pela aplicação. Um risco de inconsistência desaparece, mas análise, restrições, armazenamento persistido e manutenção do índice continuam tendo custo.

PERSISTED é uma escolha de projeto, não pré-requisito universal de todo índice calculado. Determinismo, precisão, tipos suportados e opções SET precisam seguir as regras do mecanismo. Compare espaço e custo de escrita com a redução de leituras.

Renomear customerId ou alterar seu tipo é uma migração de esquema mesmo em documentos flexíveis. Coordene validação, extração, dados existentes e consumidores. Uma implantação não deve transformar propriedades antigas em NULL silenciosamente nem bloquear gravações esperadas.

Teste chaves ausentes, caixa incorreta, arrays, JSON inválido, valores não numéricos, estouro e alterações normais. Meça consultas representativas e vazão de ingestão. O índice é útil quando a interpretação da propriedade permanece correta durante a evolução dos documentos.

Referências técnicas: Microsoft Learn: Index JSON data · Microsoft Learn: JSON_VALUE · Microsoft Learn: ISJSON.