Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

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

Visualizações: 5.491 views
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.