Partilhar via

Armazenar documentos JSON

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores Azure SQL Database Azure SQL Managed InstanceBase de dados SQL no Microsoft Fabric

O Mecanismo de Banco de Dados SQL fornece funções JSON nativas que permitem analisar documentos JSON usando a linguagem SQL padrão. Você pode armazenar documentos JSON no Mecanismo de Banco de Dados SQL e consultar dados JSON como em um banco de dados NoSQL. Este artigo descreve as opções para armazenar documentos JSON.

Formato de armazenamento JSON

A primeira decisão de design de armazenamento é como armazenar documentos JSON nas tabelas. Existem duas opções disponíveis:

  • Armazenamento LOB - documentos JSON podem ser armazenados as-is em colunas com o tipo de dados json ou nvarchar. Esta é a melhor maneira de carregamento e ingestão rápida de dados, porque a velocidade de carregamento corresponde à velocidade de carregamento das colunas de cadeia de caracteres. Essa abordagem pode introduzir uma penalidade de desempenho adicional no tempo de consulta/análise se a indexação em valores JSON não for executada, porque os documentos JSON brutos devem ser analisados enquanto as consultas estão em execução.
  • Armazenamento relacional - Os documentos JSON podem ser analisados enquanto são inseridos na tabela usando as funções OPENJSON, JSON_VALUE ou JSON_QUERY. Os fragmentos dos documentos JSON de entrada podem ser armazenados nas colunas que contêm subelementos JSON com tipos de dados json ou nvarchar. Essa abordagem aumenta o tempo de carregamento porque a análise JSON é feita durante a carga; no entanto, as consultas correspondem ao desempenho das consultas clássicas nos dados relacionais.
  • Atualmente, no SQL Server, JSON não é um tipo de dados interno.

Observação

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 o SQL Server 2025 (17.x) e a base de dados SQL em Fabric.

Mesas clássicas

A maneira mais simples de armazenar documentos JSON no SQL Server ou no Banco de Dados SQL do Azure é criar uma tabela de duas colunas que contenha a ID do documento e o conteúdo do documento. Por exemplo:

create table WebSite.Logs (
    [_id] bigint primary key identity,
    [log] nvarchar(max)
);

Ou, onde suportado:

create table WebSite.Logs (
    [_id] bigint primary key identity,
    [log] json
);

Essa estrutura é equivalente às coleções que você pode encontrar em bancos de dados de documentos clássicos. O _id de chave primária é um valor de incremento automático que fornece um identificador exclusivo para cada documento e permite pesquisas rápidas. Essa estrutura é uma boa opção para os cenários NoSQL clássicos em que você deseja recuperar um documento por ID ou atualizar um documento armazenado por ID.

  • Use o tipo de dados nativo json quando disponível para armazenar documentos JSON.
  • O tipo de dados nvarchar(max) permite armazenar documentos JSON com até 2 GB de tamanho. No entanto, se tiver certeza de que seus documentos JSON não têm mais de 8 KB, recomendamos que você use nvarchar(4000) em vez de nvarchar(max) por motivos de desempenho.

A tabela de exemplo criada no exemplo anterior pressupõe que documentos JSON válidos sejam armazenados na coluna log. Se quiser ter certeza de que JSON válido é salvo na coluna log, você pode adicionar uma restrição CHECK na coluna. Por exemplo:

ALTER TABLE WebSite.Logs
    ADD CONSTRAINT [Log record should be formatted as JSON]
                   CHECK (ISJSON([log])=1)

Sempre que alguém insere ou atualiza um documento na tabela, essa restrição verifica se o documento JSON está formatado corretamente. Sem a restrição, a tabela é otimizada para inserções, porque qualquer documento JSON é adicionado diretamente à coluna sem qualquer processamento.

Ao armazenar seus documentos JSON na tabela, você pode usar a linguagem Transact-SQL padrão para consultar os documentos. Por exemplo:

SELECT TOP 100 JSON_VALUE([log], '$.severity'), AVG( CAST( JSON_VALUE([log],'$.duration') as float))
 FROM WebSite.Logs
 WHERE CAST( JSON_VALUE([log],'$.date') as datetime) > @datetime
 GROUP BY JSON_VALUE([log], '$.severity')
 HAVING AVG( CAST( JSON_VALUE([log],'$.duration') as float) ) > 100
 ORDER BY AVG( CAST( JSON_VALUE([log],'$.duration') as float) ) DESC

É uma vantagem poderosa que você pode usar qualquer função T-SQL e cláusula de consulta para consultar documentos JSON. O SQL Server e o Banco de dados SQL não introduzem restrições nas consultas que você pode usar para analisar documentos JSON. Você pode extrair valores de um documento JSON com a função JSON_VALUE e usá-la na consulta como qualquer outro valor.

Essa capacidade de usar sintaxe de consulta T-SQL avançada é a principal diferença entre o SQL Server e o Banco de dados SQL e os bancos de dados NoSQL clássicos - em Transact-SQL você provavelmente tem qualquer função necessária para processar dados JSON.

Indexes

Se você descobrir que suas consultas frequentemente pesquisam documentos por alguma propriedade (por exemplo, uma propriedade severity em um documento JSON), poderá adicionar um índice não clusterizado de armazenamento de linhas na propriedade para acelerar as consultas.

Você pode criar uma coluna computada que exponha valores JSON das colunas JSON no caminho especificado (ou seja, no caminho $.severity) e criar um índice padrão nessa coluna calculada. Por exemplo:

create table WebSite.Logs (
    [_id] bigint primary key identity,
    [log] nvarchar(max),
    [severity] AS JSON_VALUE([log], '$.severity'),
    index ix_severity (severity)
);

A coluna computada usada neste exemplo é uma coluna virtual ou não persistente que não adiciona espaço adicional à tabela. Ele é usado pelo índice ix_severity para melhorar o desempenho das consultas, como o exemplo a seguir:

SELECT [log]
FROM Website.Logs
WHERE JSON_VALUE([log], '$.severity') = 'P4'

Uma característica importante deste índice é que ele é sensível à colação. Se a coluna original nvarchar tiver uma propriedade COLLATION (por exemplo, diferenciação de maiúsculas e minúsculas ou idioma japonês), o índice será organizado de acordo com as regras de idioma ou as regras de diferenciação de maiúsculas e minúsculas associadas à coluna nvarchar. Esse reconhecimento de agrupamento pode ser um recurso importante se você estiver desenvolvendo aplicativos para mercados globais que precisam usar regras de linguagem personalizadas ao processar documentos JSON.

Tabelas grandes & formato columnstore

Se você espera ter um grande número de documentos JSON em sua coleção, recomendamos adicionar um índice columnstore clusterizado na coleção, conforme mostrado no exemplo a seguir:

create sequence WebSite.LogID as bigint;
go
create table WebSite.Logs (
    [_id] bigint default(next value for WebSite.LogID),
    [log] nvarchar(max),
    INDEX cci CLUSTERED COLUMNSTORE
);

Um índice columnstore clusterizado fornece uma alta compactação de dados (até 25x) que pode reduzir significativamente os requisitos de espaço de armazenamento, diminuir o custo de armazenamento e aumentar o desempenho de E/S na sua carga de trabalho. Além disso, os índices columnstore clusterizados são otimizados para verificações de tabelas e análises em seus documentos JSON, portanto, esse tipo de índice pode ser a melhor opção para análise de log.

O exemplo anterior usa um objeto sequence para atribuir valores à coluna _id. Sequências e identidades são opções válidas para a coluna ID.

Alteração frequente de documentos em tabelas otimizadas para memória &

Se você espera um grande número de operações de atualização, inserção e exclusão em suas coleções, pode armazenar seus documentos JSON em tabelas com otimização de memória. As coleções JSON otimizadas para memória sempre mantêm os dados na memória, portanto, não há sobrecarga de E/S de armazenamento. Além disso, as coleções JSON otimizadas para memória são completamente livres de bloqueio, ou seja, as ações em documentos não bloqueiam nenhuma outra operação.

A única coisa que você precisa fazer para converter uma coleção clássica em uma coleção com otimização de memória é especificar a opção WITH (MEMORY_OPTIMIZED=ON) após a definição da tabela, conforme mostrado no exemplo a seguir. Em seguida, você tem uma versão otimizada para memória da coleção JSON.

CREATE TABLE WebSite.Logs (
  [_id] bigint IDENTITY PRIMARY KEY NONCLUSTERED,
  [log] nvarchar(max)
) WITH (MEMORY_OPTIMIZED=ON)

Uma tabela com otimização de memória é a melhor opção para alterar documentos com frequência. Quando estiver considerando tabelas com otimização de memória, considere também o desempenho. Use o tipo de dados nvarchar(4000) em vez de nvarchar(max) para documentos JSON em suas coleções otimizadas para memória, se possível, porque isso pode melhorar drasticamente o desempenho. O tipo de dados json não é suportado com tabelas com otimização de memória.

Assim como nas tabelas clássicas, você pode adicionar índices nos campos que está expondo em tabelas com otimização de memória usando colunas computadas. Por exemplo:

CREATE TABLE WebSite.Logs (

  [_id] bigint IDENTITY PRIMARY KEY NONCLUSTERED,
  [log] nvarchar(max),

  [severity] AS cast(JSON_VALUE([log], '$.severity') as tinyint) persisted,
  INDEX ix_severity (severity)

) WITH (MEMORY_OPTIMIZED=ON)

Para maximizar o desempenho, converta o valor JSON para o menor tipo possível que pode ser usado para manter o valor da propriedade. No exemplo anterior, o tinyint é utilizado.

Você também pode colocar consultas SQL que atualizam documentos JSON em procedimentos armazenados para obter o benefício da compilação nativa. Por exemplo:

CREATE PROCEDURE WebSite.UpdateData(@Id int, @Property nvarchar(100), @Value nvarchar(100))
WITH SCHEMABINDING, NATIVE_COMPILATION

AS BEGIN
    ATOMIC WITH (transaction isolation level = snapshot,  language = N'English')

    UPDATE WebSite.Logs
    SET [log] = JSON_MODIFY([log], @Property, @Value)
    WHERE _id = @Id;

END

Este procedimento compilado nativamente pega a consulta e cria .DLL código que executa a consulta. Um procedimento compilado nativamente é a abordagem mais rápida para consultar e atualizar dados.

Conclusion

As funções JSON nativas no SQL Server e no Banco de dados SQL permitem que você processe documentos JSON como em bancos de dados NoSQL. Cada banco de dados - relacional ou NoSQL - tem alguns prós e contras para o processamento de dados JSON. O principal benefício de armazenar documentos JSON no SQL Server ou no Banco de dados SQL é o suporte total à linguagem SQL. Você pode usar a linguagem Transact-SQL avançada para processar dados e configurar uma variedade de opções de armazenamento, desde índices columnstore para alta compactação e análises rápidas até tabelas otimizadas para memória para processamento sem bloqueio. Ao mesmo tempo, você obtém o benefício de recursos maduros de segurança e internacionalização que você pode facilmente reutilizar em seu cenário NoSQL. Os motivos descritos neste artigo são excelentes motivos para considerar o armazenamento de documentos JSON no SQL Server ou no Banco de dados SQL.

Saiba mais sobre JSON no Mecanismo de Banco de Dados SQL

Para obter uma introdução visual ao suporte JSON integrado, consulte os seguintes vídeos:

Conteúdo relacionado

  • Last updated on