Trabalhando com dados JSON no SQL Server 2016


 

Neste post irei demonstrar como ler strings JSON e converter para uma tabela e converter os dados de uma tabela para JSON, um acrônimo para “JavaScript Object Notation”. Esse recurso está disponível a partir da versão 2016 do SQL Server e tem como objetivo a leitura e manipulação de dados no formato JSON.

O SQL Server 2016 agora oferece suporte para ler arquivos JSON que vem ganhando mais espaço nas formas de armazenamento por serem mais compactos que os atuais XML.

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.

Agora para converter esses dados JSON para dados em uma tabela no SQL Server é bem simples usando o OPENJSON, veja a seguir:

— Converte dos dados em tabela e insere em uma tabela temporária

Na cláusula “WITH” devemos definir o esquema de colunas e tipos de dados que serão retornados pelo JSON, é semelhante a declaração de uma tabela que deve possuir a mesma estrutura do JSON.

A função OPENJSON analisa o conteúdo do JSON e combina as propriedades declaradas no mesmo com as definições das colunas informadas na cláusula WITH para retorno dos dados, convertendo seus valores nos tipos especificados.

 

                                                                      Cláusulas JSON

Existem dois modos permitidos para a cláusula FOR JSON, sendo eles:

JSON AUTO: 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.

JSON PATH: Para obter um controle total sobre o formato da saída JSON

Parâmetro ROOT: O parâmetro ROOT é usado para definir um elemento raiz para a string JSON e também para mudar o títulos dos campos.

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.

Observe que em nenhum dos dois formatos JSON retorna o campo com valor null que existe na tabela #Person

Entao se desejar gerar o json com o campos em null, use o parâmetro INCLUDE_NULL_VALUES

SELECT * FROM #PERSON FOR JSON AUTO, INCLUDE_NULL_VALUES
SELECT * FROM #PERSON FOR JSON PATH, INCLUDE_NULL_VALUES

Parâmetro WITHOUT_ARRAY_WRAPPER: Serve para remover os colchetes [ ] da string JSON gerada.

 

                                   Atualizando Tabela usando JSON

Para update na tabela, foi realizado a atualização do “LastName” e os dados do “Sexo” no Json, a comparação foi realizada pelo Id a qual identifica a pessoa.

 

No SQL Server temos a função ISJSON, que é utilizada para verificarmos se a estrutura de um JSON é válida ou não. Se a estrutura do JSON for válida a função retornará o valor “1” (verdadeiro), caso contrário será retornado valor “0” (falso). Para utilizar a função ISJSON basta informamos nossa variável que contém o conteúdo JSON como parâmetro, por exemplo:

IF((SELECT ISJSON(@JSON)) = 1)
SELECT ‘JSON VÁLIDO’;
ELSE
SELECT ‘JSON INVÁLIDO’;

 

                                   Importando JSON a partir de um diretório para forma de tabela

Eu tenho o seguinte diretório aonde se encontra o arquivo JSON: D:\FileJSON\Person.json em minha máquina local

A função OPENJSON pode analisar o conteúdo do JSON e transformá-lo em uma tabela ou em um conjunto de resultados. Neste exemplo a seguir, OPENROWSET (BULK) lê o conteúdo do arquivo e passa esse conteúdo para a função OPENJSON com um esquema definido para a saída. 

O exemplo a seguir carrega o conteúdo, analisa o JSON carregado e retorna os quatro campos como colunas:

 

Agora você pode retornar esta tabela e carregar os dados em outra tabela.

Obrigado e até a proxima.

Abs 🙂

 

SEGUE SCRIPT COMPLETO:
/*
Criado por Joao Vilar
Date: 25-11-2017
Brasília – DF
*/

IF OBJECT_ID(‘tempdb..#Person’) IS NOT NULL
BEGIN
DROP TABLE #Person
END
— Converte dos dasos em tabela e insere em uma tabela temporária
DECLARE @JSON NVARCHAR(MAX) = ‘[
{“id” : 2,”firstName”: “João”, “lastName”: “Braga”, “age”: 28, “dateOfBirth”: “2019-09-22T12:00:00”, “Sexo”: “M”},
{“id” : 3,”firstName”: “Eduarda”, “lastName”: “Oliveira”, “age”: 26, “dateOfBirth”: “2019-02-18T12:00:00″,”Sexo”:”F”},
{“id” : 4,”firstName”: “Daniele”, “lastName”: “Montes”, “age”: 20, “dateOfBirth”: “2019-08-02T12:00:00″,”Sexo”:”F”},
{“id” : 5,”firstName”: “Lucas”, “lastName”: “Pereira”, “age”: 23, “dateOfBirth”: “2019-06-11T12:00:00″,”Sexo”:”M”}
]’;
— Verifica se o json é válido
IF((SELECT ISJSON(@JSON)) = 1)
SELECT ‘JSON VÁLIDO’;
ELSE
SELECT ‘JSON INVÁLIDO’;

SELECT *
INTO #PERSON
FROM OPENJSON(@json)
WITH (id int,
firstName nvarchar(50),
lastName nvarchar(50),
age int,
dateOfBirth datetime2,
Sexo char(2))

SELECT * FROM #PERSON

— Sem colchete
SELECT * FROM #PERSON FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
— Com colchete
SELECT * FROM #PERSON FOR JSON PATH

— Atualiza
DECLARE @JSON NVARCHAR(MAX) = ‘[
{“id” : 2,”firstName”: “João”, “lastName”: “Braga”, “age”: 28, “dateOfBirth”: “2019-09-22T12:00:00”, “Sexo”: “M”},
{“id” : 3,”firstName”: “Eduarda”, “lastName”: “Oliveira”, “age”: 26, “dateOfBirth”: “2019-02-18T12:00:00″,”Sexo”:null},
{“id” : 4,”firstName”: “Daniele”, “lastName”: “Montes”, “age”: 20, “dateOfBirth”: “2019-08-02T12:00:00″,”Sexo”:null},
{“id” : 5,”firstName”: “Lucas”, “lastName”: “Pereira”, “age”: 23, “dateOfBirth”: “2019-06-11T12:00:00″,”Sexo”:null}
]’;
UPDATE #Person
SET firstName = json.firstname,
lastName = json.lastname,
age = json.age,
dateOfBirth = json.dateOfBirth,
Sexo = json.sexo
FROM OPENJSON(@json)
WITH (id int,
firstName nvarchar(50), lastName nvarchar(50),
isAlive bit, age int,
dateOfBirth datetime2, spouse nvarchar(50),
Sexo char(2)) AS json
WHERE #person.id = json.id
SELECT * FROM #PERSON

–======== Inserindo Registros JSON em uma variável================

DECLARE @json AS NVARCHAR(MAX)
SELECT @json = BulkColumn from OPENROWSET (BULK ‘D:\Person.json’, SINGLE_CLOB) as j
SELECT @json

–Inserindo os Registros JSON em uma tabela temporária
SELECT BulkColumn
INTO #temp
FROM OPENROWSET (BULK ‘D:\Person.json’, SINGLE_CLOB) as j
SELECT * FROM #temp

— Importando de um diretório e montando em estrutura de tabelas e linhas

SELECT t.*
FROM OPENROWSET (BULK ‘D:\FileJSON\Person.json’, SINGLE_CLOB) as j
CROSS APPLY OPENJSON(BulkColumn)
WITH( id int,
firstName nvarchar(50),
lastName nvarchar(50),
age int,
dateOfBirth datetime2,
Sexo char(2)) AS t

 

Share on FacebookTweet about this on TwitterShare on LinkedIn


Escreva um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *