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

SQL Server 2016 – Utilizando o suporte nativo a JSON (JSON_VALUE, JSON_QUERY, OPENJSON, FOR JSON, ISJSON, JSON_MODIFY)
5 (100%) 1 voto

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ê 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 último exemplo, vou demonstrar como importar dados de uma string JSON multinível para tabela.

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.

SQL, sql server, sql server 2008, sql server 2008 R2, Oracle, Oracle Database, Oracle 11g, Oracle 10g, Oracle 12c, MySQL, Firebird, Consultoria, Consultor, Programador, Programação. Desenvolvedor, Analista de Sistemas, DBA, Criação de website, Criação de Sistema Web, Vitória, Vila Velha, Guarapari, Espírito Santo, ES, Consultoria SQL em VItória, Treinamento, Curso, Prestação de serviço, prestar serviço, freelancer, freela, banco de dados, consultoria em banco de dados, consultor de banco de dados

5 Comments

  1. Não ficou claro para mim como se armazena um JSON dentro de uma tabela. Existe um tipo de dados para JSON nativamente? Você tem operadores para este tipo de dados para utilizar numa cláusula WHERE? É possível indexar um JSON?

    1. Telles,
      Bom dia.

      O JSON é armazenado como string (VARCHAR ou NVARCHAR), diferente do XML, que possui um tipo de dado específico para ele. Os operadores para trabalhar com JSON em um WHERE são os mesmos do SELECT, como ISJON, JSON_VALUE, etc.. Assim como uma string qualquer, você pode indexar as strings JSON normalmente.

      Vou editar o post depois para adicionar esses exemplos. 🙂

Deixe uma resposta