Armazenar documentos JSON
Aplica-se a: 
SQL Server 2016 (13.x) e posteriores Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure
O Mecanismo de Banco de Dados SQL têm funções nativas do JSON que permitem analisar documentos JSON usando a linguagem SQL padrão. Você pode armazenar documentos JSON no SQL Server ou no Banco de Dados SQL e consultar os dados JSON 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 projeto de armazenamento é como armazenar os documentos JSON nas tabelas. Há duas opções disponíveis:
Armazenamento de LOB – os documentos JSON podem ser armazenados como estão em colunas com o tipo de dados json ou nvarchar. Essa é a melhor maneira para um carregamento e ingestão de dados rápido, pois a taxa de velocidade corresponde à velocidade de carregamento das colunas de cadeia de caracteres. Essa abordagem pode apresentar penalidades de desempenho adicionais no tempo de consulta/análise se a indexação nos valores JSON não for realizada, pois os documentos JSON brutos devem ser analisados enquanto as consultas estiverem em execução.
Armazenamento relacional – os documentos JSON podem ser analisados enquanto estiverem inseridos na tabela, usando as funções
OPENJSON,JSON_VALUEouJSON_QUERY. Os fragmentos de documentos JSON de entrada podem ser armazenados nas colunas que contêom subelementos JSON com os tipos de dados json ou nvarchar. Essa abordagem aumenta o tempo de carregamento porque a análise JSON é realizada durante o carregamento. No entanto, as consultas correspondem ao desempenho de consultas clássicas nos dados relacionais.O tipo de dados JSON está atualmente em versão prévia para o Banco de Dados SQL do Azure e a Instância Gerenciada de SQL do Azure (configurada com a política de atualização Sempre atualizada).
Atualmente, no SQL Server, JSON não é um tipo de dados interno.
Tabelas clássicas
A maneira mais simples de armazenar documentos JSON no Azure SQL Server ou no Banco de Dados SQL do Azure é criar uma tabela de duas colunas que contenha a ID e o conteúdo do documento. Por exemplo:
create table WebSite.Logs (
[_id] bigint primary key identity,
[log] nvarchar(max)
);
Ou, quando há suporte:
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. A chave primária _id é um valor de incremento automático que fornece um identificador exclusivo para cada documento e possibilita pesquisas rápidas. Essa estrutura é uma boa escolha para os cenários clássicos de NoSQL em que você deseja recuperar um documento pela ID ou atualizar um documento armazenado pela ID.
- Use o tipo de dados json nativo quando disponível para armazenar documentos JSON.
- O tipo de dados nvarchar(max) permite armazenar documentos JSON com até 2 GB. No entanto, caso tenha certeza de que seus documentos JSON não são maiores que 8 KB, é recomendável usar nvarchar(4000), em vez de nvarchar(max) por questões de desempenho.
A tabela de exemplo criada no exemplo anterior pressupõe que os documentos JSON válidos sejam armazenados na coluna log. Caso deseje ter certeza de que o JSON válido é salvo na coluna log, adicione uma restrição CHECK à 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 nenhum processamento.
Ao armazenar seus documentos JSON na tabela, use 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 grande vantagem poder 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 nenhuma restrição nas consultas que podem ser usadas 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 a 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 – no Transact-SQL, provavelmente, você terá qualquer função necessária para processar dados JSON.
Índices
Se você descobrir que as consultas frequentemente pesquisam documentos por alguma propriedade (por exemplo, uma propriedade severity em um documento JSON), adicione um índice não clusterizado rowstore à propriedade para acelerar as consultas.
Crie uma coluna computada que expõe valores JSON das colunas JSON no caminho especificado (ou seja, no caminho $.severity) e crie um índice padrão nessa coluna computada. 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 não persistente ou virtual que não adiciona espaço extra à tabela. Ela é usada pelo índice ix_severity para melhorar o desempenho das consultas com o seguinte exemplo:
SELECT [log]
FROM Website.Logs
WHERE JSON_VALUE([log], '$.severity') = 'P4'
Uma característica importante desse índice é o reconhecimento de ordenação. Se a coluna nvarchar original 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 do idioma ou as regras de diferenciação de maiúsculas e minúsculas associadas com a coluna nvarchar. Esse reconhecimento de ordenação poderá ser um recurso importante caso você esteja desenvolvendo aplicativos para mercados globais que precisam usar regras de idioma personalizadas durante o processamento de documentos JSON.
Tabelas grandes e formato columnstore
Se você espera ter um número grande de documentos JSON em sua coleção, recomendamos adicionar um índice columnstore clusterizado à coleção, conforme mostrado no seguinte exemplo:
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 alta compactação de dados (até 25x) que pode reduzir consideravelmente os requisitos de espaço de armazenamento, reduzir o custo de armazenamento e aumentar o desempenho de E/S da carga de trabalho. Além disso, os índices colunmstore clusterizados são otimizados para análise e verificações de tabela em documentos JSON, portanto, esse tipo de índice pode ser a melhor opção para análise de log.
O exemplo anterior usa um objeto de sequência para atribuir valores à coluna _id. As sequências e as identidades são opções válidas para a coluna de ID.
Documentos com alterações frequentes e tabelas com otimização de memória
Se você espera uma grande quantidade de operações de atualização, inserção e exclusão em suas coleções, armazene os documentos JSON em tabelas com otimização de memória. As coleções de JSON com otimização de memória sempre mantêm os dados na memória e, portanto, não há nenhuma sobrecarga de E/S de armazenamento. Além disso, as coleções de JSON otimizado 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 de tabela, conforme mostrado no exemplo a seguir. Em seguida, você terá uma versão com otimização de memória da coleção de 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 documentos com alterações frequentes. Quando estiver considerando o uso de 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 os documentos JSON nas coleções com otimização de memória, se possível, porque ele pode melhorar drasticamente o desempenho. O tipo de dados json não é compatível com tabelas com otimização de memória.
Assim como ocorre com as tabelas clássicas, é possível adicionar índices aos campos que estão sendo expostos 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 no menor tipo possível que pode ser usado para armazenar o valor da propriedade. No exemplo anterior, tinyint é usado.
Também coloque 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
Esse procedimento compilado nativamente usa a consulta e cria um código .DLL que a executa. Um procedimento compilado nativamente é a abordagem mais rápida para consultar e atualizar dados.
Conclusão
As funções nativas do JSON no SQL Server e no Banco de Dados SQL possibilitam processar documentos JSON da mesma forma como em bancos de dados NoSQL. Todo banco de dados – relacional ou NoSQL – tem alguns prós e contras em relação ao processamento de dados JSON. O principal benefício de armazenar documentos JSON no SQL Server ou no Banco de Dados SQL é o suporte completo à linguagem SQL. Use a linguagem Transact-SQL avançada para processar dados e configurar uma variedade de opções de armazenamento, de índices columnstore para alta compactação alta e análise rápida a tabelas com otimização de memória para processamento sem bloqueio. Ao mesmo tempo, você obtém o benefício dos recursos de internacionalização e segurança maduros que podem ser facilmente reutilizados no cenário de NoSQL. Os motivos descritos nesse artigo são excelentes para considerar o armazenamento de documentos JSON no SQL Server ou no Banco de Dados SQL.
Saiba mais sobre JSON no SQL Server e no Banco de Dados SQL do Azure
Para obter uma introdução visual ao suporte interno para JSON no SQL Server e no Banco de Dados SQL do Azure, consulte os seguintes vídeos: