SQL Server 2016 – Utilizando o suporte nativo a JSON (JSON_VALUE, JSON_QUERY, OPENJSON, FOR JSON, ISJSON, JSON_MODIFY)

Visualizações: 1.471
Tempo de Leitura: 12 minutos

Olá pessoal,
Tudo bem com vocês ?

Neste post vou demonstrar como utilizar o suporte nativo do banco de dados SQL Server ao JSON, padrão de documento muito utilizado em integrações e Webservices atualmente. Esse recurso está disponível a partir da versão 2016 do SQL Server e tem como objetivo, permitir a exportação de dados do banco para uma string no formato JSON e a leitura e manipulação de dados no formato JSON.

Caso você esteja utilizando uma versão anterior ao SQL Server 2016, ou seja, sem suporte nativo a JSON, mas mesmo assim precisa trabalhar com strings JSON, leia o meu post Lendo strings JSON, importando para o banco e exportando para XML no SQL Server e saiba como fazer isso.

Caso você precise importar/exportar as strings JSON de/para arquivos texto fisicamente, confira esses dois posts:

Exportando dados do banco para uma string JSON

Visualizar conteúdo
Pensando na necessidade dos DBA’s e Desenvolvedores de exportarem dados do banco de dados para strings JSON, a Microsoft criou uma variante da FOR XML para tratar especificamente de JSON, que é a FOR JSON.

Sua sintaxe é muito parecida com a do XML, então se você já sabe manipular XML pelo SQL Server, você já estará bem familiarizado com esse novo recurso da versão 2016. Caso você não conheça, veja mais acessando o post SQL Server – Como ler, importar e exportar dados de arquivos XML.

Exemplo 1

Neste primeiro exemplo, vou utilizar o modo padrão do JSON, que é o auto. Ele simplesmente vai gerar o JSON de acordo com os dados informados, sem fazer nenhuma modificação em sua estrutura.

Tabela original:

JSON gerado:

Exemplo 2

Agora neste exemplo, vou utilizar o parâmetro ROOT para definir um elemento raiz para a minha string JSON e também vou mudar o títulos dos campos.

Tabela original:

JSON gerado:

Exemplo 3

Neste exemplo, vou demonstrar 2 opções para se usar com o JSON. O parâmetro INCLUDE_NULL_VALUES serve para incluir as colunas com valor NULL na string JSON gerada (por padrão, colunas com valor NULL não são geradas). O parâmetro WITHOUT_ARRAY_WRAPPER serve para remover os caracteres “[” e “]” da string JSON gerada.

Resultado:

Exemplo 4

Neste exemplo, vou demonstrar como exportar os dados de uma tabela e armazenar a string JSON gerada em uma variável.

Resultado:

Exemplo 5

Neste última exemplo, vou demonstrar como controlar completamente a estrutura da sua string JSON utilizando o parâmetro PATH.

JSON gerado:

Manipulando dados em strings JSON com JSON_VALUE

Visualizar conteúdo
Um outro recurso bacana para manipulação de strings JSON é a função JSON_VALUE, que permite manipular strings JSON e retornar as informações desejadas a partir de uma string JSON.

Exemplo 1

Neste primeiro exemplo, vou demonstrar como extrair informações rapidamente utilizando a função JSON_VALUE.

Resultado:

Exemplo 2

Neste exemplo, vou demonstrar como ler dados de strings JSON que possuem dados multivalorados (arrays) e a leitura será feita utilizando índices de arrays.

Resultado:

Exemplo 3

Neste exemplo, vou demonstrar como utilizar o JSON_VALUE utilizando uma string embutida no próprio comando.

Resultado:

Exemplo 4

Por fim, neste exemplo vou demonstrar como utilizar a função JSON_VALUE como coluna computada, onde insiro uma string JSON na tabela, e os dados são calculados automaticamente pra mim.

Resultado:

Manipulando dados em strings JSON com JSON_QUERY

Visualizar conteúdo
Após aprender como utilizar o JSON_VALUE para trabalhar com informações escalares, vou demonstrar como utilizar a função JSON_QUERY para retornar objetos e arrays de valores JSON.

Exemplo 1

Neste primeiro exemplo, vou demonstrar algumas utilizações básicas da função JSON_QUERY

Resultado:

Exemplo 2

Neste exemplo, vou demonstrar como retornar arrays utilizando índices-N a partir de uma string JSON.

Resultado:

Exemplo 3

Neste exemplo, vou fazer algumas comparações entre o JSON_QUERY e JSON_VALUE, para demonstrar a diferença das 2 funções.

Resultado:

Validando strings JSON com a função ISJSON

Visualizar conteúdo
Uma função muito útil para se trabalhar com JSON é a ISJSON, que permite verificar se uma string é uma string JSON é está em conformidade com os padrões de JSON reconhecidos e que podem ser lidos pelo SQL Server.

Vou demonstrar alguns exemplos para vocês entenderem facilmente como se utilizar essa função.

Exemplo 1

Resultado:
1 (JSON válido)

Exemplo 2

Neste exemplo, vou usar exatamente o mesmo JSON do exemplo anterior, mas vou remover um bracket “]” da string JSON para que ele não seja mais válido.

Resultado:
0 (JSON inválido)

Exemplo 3

Por fim, vou demonstrar alguns exemplos rápidos de validação de JSON.

Resultado:

Importando JSON para tabela com OPENJSON

Visualizar conteúdo
Utilizando a função OPENJSON, podemos facilmente importar os dados de uma string JSON para uma tabela do SQL Server. Reparem que neste exemplo, o JSON possui um elemento raiz (databases). Por isso, vou utilizar o seletor ‘$.databases’ na função OPENJSON para facilitar a navegação nos elementos sem precisar ter que sempre informar o elemento raiz.

Exemplo 1

Resultado:

Exemplo 2

Neste exemplo, vou demonstrar como importar outro JSON, desta vez sem elemento raiz e vou converter a data para DATETIME.

Resultado:

Exemplo 3

Neste exemplo, vou demonstrar como importar dados de uma string JSON multinível para tabela.

Resultado:

Exemplo 4

Neste exemplo, vou demonstrar como importar dados de uma string JSON multinível, retornando apenas a listagem, para tabela. Nesse cenário, terei que utilizar CROSS APPLY entre os nós do JSON para percorrê-lo.

Resultado:

Exemplo 5

Neste último exemplo, vou demonstrar novamente como importar dados de uma string JSON multinível, retornando apenas a listagem, para tabela. Nesse cenário, terei que utilizar CROSS APPLY entre os nós do JSON para percorrê-lo.

Resultado:

Modificando strings JSON com JSON_MODIFY

Visualizar conteúdo
Utilizando a função JSON_MODIFY, pode-se facilmente alterar os dados de uma string JSON e trabalhar posteriormente com esses dados de acordo com a sua necessidade.

Exemplo de uso

Resultado:

É isso aí, pessoal!
Espero que tenham gostado desse post.
Até a próxima.