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

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.

Result:

Exemplo 4

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

Result:

Exemplo 5

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

JSON gerado:

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.

Result:

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.

Result:

Exemplo 3

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

Result:

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.

Result:

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

Result:

Exemplo 2

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

Result:

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.

Result:

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

Result:
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.

Result:
0 (JSON inválido)

Exemplo 3

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

Result:

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

Result:

Exemplo 2

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

Result:

Exemplo 3

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

Result:

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.

Result:

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.

Result:

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

Result: