Neste artigo vamos aprender como podemos inserir dados no formato JSON numa tabela SQL. Vamos ver exemplos de como fazer isso utilizando o MySQL e o SQL Server.

O que você vai aprender neste artigo?

* Como utilizar a função JSON_TABLE no MySQL

* Como utilizar a função OPENJSON no SQL Server

Antes de começar, vamos criar a tabela que vai guardar as informações.

MySQL

CREATE TABLE clientes (

ID int(11) NOT NULL AUTO_INCREMENT,

CODIGO int(11), 

EMAIL varchar(255), 

NOME varchar(255),

PRIMARY KEY (ID));

SQL Server

CREATE TABLE clientes (

ID INT NOT NULL IDENTITY PRIMARY KEY,

CODIGO varchar(255),

EMAIL varchar(255),

NOME varchar(255) 

)

Lendo JSON no MySQL

Começando pelo MySQL, para inserir dados no formato JSON numa tabela utilizamos a função JSON_TABLE. Esta função, que está disponível a partir da versão 8 do MySQL possui a seguinte sintaxe:

JSON_TABLE(origem de dados, caminho do array que deve ser convertido em tabela COLUMNS (

coluna1 tipo_de_dados  PATH '$.nomecampoJson1',

coluna2 tipo_de_dados PATH '$.nomecampoJson2')

) nome_da_tabela;

O primeiro parâmetro recebe a variável com os dados no formato JSON. No segundo parâmetro, informamos qual o nome do campo no nosso JSON com o array contendo os dados que devem ser convertidos numa tabela. O terceiro parâmetro, chamado COLUMNS, permite fazer o mapeamento dos campos, ou seja, escolher quais campos do nosso JSON serão utilizados, perceba que fazemos a tipagem dos dados. Como esta função retorna uma tabela, toda tabela precisa de um alias, ou seja, um apelido.

Os dados a seguir são o JSON que será utilizado nos exemplos deste artigo:

{

 "clientes": [  

   { "CODIGO": 11,

     "EMAIL": "teste@teste.com.br",

     "NOME": "Trajano"

   },

   { "CODIGO": 12,

     "EMAIL": "teste2@teste.com.br",

     "NOME": "Augusto"

   },

   { "CODIGO": 13,

     "EMAIL": "teste3@teste.com.br",

     "NOME": "Nero"

   }]

}

Perceba que neste JSON existe um array chamado clientes e ele possui 3 registros. Agora vamos ler os dados deste JSON usando a função JSON_TABLE:

SET @data = '{

 "clientes": [  

   { "CODIGO": 11,

     "EMAIL": "teste@teste.com.br",

     "NOME": "Trajano"

   },

   { "CODIGO": 12,

     "EMAIL": "teste2@teste.com.br",

     "NOME": "Augusto"

   },

   { "CODIGO": 13,

     "EMAIL": "teste3@teste.com.br",

     "NOME": "Nero"

   }]

}';

SELECT *

FROM JSON_TABLE(@data, "$.clientes[*]" COLUMNS( 

CODIGO INT PATH "$.CODIGO",

EMAIL VARCHAR(255) PATH "$.EMAIL",

NOME VARCHAR(255) PATH "$.NOME")

) AS tabela_json;

Recapitulando o que vimos antes, o primeiro parâmetro é a origem de dados, que em nosso caso é a variável @data, o segundo parâmetro é o campo dentro do JSON com os dados que vão ser lidos, nosso JSON possui um array chamado clientes, então informamos $.clientes[*], e o terceiro parâmetro é o mapeamento das colunas. Perceba que para cada campos usamos PATH e o nome do campo no JSON é informado após o "$". Antes do PATH, você informa a forma como estes dados devem ser tratados pelo MySQL.

Agora vamos ver como podemos inserir estes dados em nossa tabela MySQL

SET @data = '{

 "clientes": [  

   { "CODIGO": 11,

     "EMAIL": "teste@teste.com.br",

     "NOME": "Trajano"

   },

   { "CODIGO": 12,

     "EMAIL": "teste2@teste.com.br",

     "NOME": "Augusto"

   },

   { "CODIGO": 13,

     "EMAIL": "teste3@teste.com.br",

     "NOME": "Nero"

   }]

}';

INSERT INTO clientes (CODIGO, EMAIL, NOME)

SELECT CODIGO, EMAIL, NOME

FROM JSON_TABLE(@data, "$.clientes[*]" COLUMNS( 

CODIGO INT PATH "$.CODIGO",

EMAIL VARCHAR(255) PATH "$.EMAIL",

NOME VARCHAR(255) PATH "$.NOME")

) AS tabela_json;

Você deve estar se perguntando como seria no caso do nosso JSON ter como conteúdo um array sem nome. Neste caso, você faria a leitura das informações da seguinte forma:    

SET @data := '[ 

  { "CODIGO": 11,

     "EMAIL": "teste@teste.com.br",

     "NOME": "Trajano"

   },

   { "CODIGO": 12,

     "EMAIL": "teste2@teste.com.br",

     "NOME": "Augusto"

   },

   { "CODIGO": 13,

     "EMAIL": "teste3@teste.com.br",

     "NOME": "Nero"

   }

]';

SELECT *

FROM JSON_TABLE(@data,"$[*]" COLUMNS( 

CODIGO INT PATH "$.CODIGO",

EMAIL VARCHAR(255) PATH "$.EMAIL",

NOME VARCHAR(255) PATH "$.NOME")

) AS tabela_json;

Lendo JSON no SQL Server

Agora chegou a hora de ver como isso é feito no SQL Server. Neste banco de dados eu tenho uma função chamada OPENJSON, que possui alguma semelhança com a função que vimos no outro banco de dados. Sua sintaxe é a seguinte:

OPENJSON(origem de dados, caminho do array que deve ser convertido em tabela)

WITH  (

        Coluna1 tipo_de_dados '$.nomecampoJson1',  

        Coluna2 tipo_de_dados '$.nomecampoJson2', 

        Coluna3 tipo_de_dados '$.nomecampoJson2'

);

A função OPENJSON recebe dois parâmetros. O primeiro parâmetro recebe a variável com os dados no formato JSON. No segundo parâmetro, informamos qual o nome do campo no nosso JSON com o array contendo os dados que devem ser convertidos numa tabela.

Na continuação utilizamos WITH para fazer o mapeamento dos campos, ou seja, o tipo de dados no SQL Server e o caminho para o campo JSON do array selecionado.

Para retornar os dados do nosso JSON faremos o sequinte:

DECLARE @data NVARCHAR(4000) = N'{ 

 "clientes":[

   { "CODIGO": 11,

     "EMAIL": "teste@teste.com.br",

     "NOME": "Trajano"

   },

   { "CODIGO": 12,

     "EMAIL": "teste2@teste.com.br",

     "NOME": "Augusto"

   },

   { "CODIGO": 13,

     "EMAIL": "teste3@teste.com.br",

     "NOME": "Nero"

   }

 ]

}';

SELECT * 

FROM OPENJSON(@data, '$.clientes')

WITH  (

CODIGO int '$.CODIGO',  

EMAIL varchar(255) '$.EMAIL', 

NOME varchar(255) '$.NOME'

);

Para inserir estes dados no banco de dados basta informar este SELECT depois do comando INSERT

DECLARE @data NVARCHAR(4000) = N'{ 

 "clientes":[

   { "CODIGO": 11,

     "EMAIL": "teste@teste.com.br",

     "NOME": "Trajano"

   },

   { "CODIGO": 12,

     "EMAIL": "teste2@teste.com.br",

     "NOME": "Augusto"

   },

   { "CODIGO": 13,

     "EMAIL": "teste3@teste.com.br",

     "NOME": "Nero"

   }

 ]

}';

INSERT INTO clientes (CODIGO, NOME, EMAIL)

SELECT CODIGO, NOME, EMAIL 

FROM OPENJSON(@data, '$.clientes')

WITH  (

CODIGO int '$.CODIGO',  

EMAIL varchar(255) '$.EMAIL', 

NOME varchar(255) '$.NOME'

);

Da mesma forma que fizemos no MySQL, você deve estar se perguntando como podemos consumir um JSON que tenha um array sem nome. Neste caso, podemos omitir o segundo parâmetro da funão OPENJSON. A leitura deste JSON ficaria da seguinte forma:

DECLARE @data NVARCHAR(4000) = N'[

   { "CODIGO": 11,

     "EMAIL": "teste@teste.com.br",

     "NOME": "Trajano"

   },

   { "CODIGO": 12,

     "EMAIL": "teste2@teste.com.br",

     "NOME": "Augusto"

   },

   { "CODIGO": 13,

     "EMAIL": "teste3@teste.com.br",

     "NOME": "Nero"

   }

 ]';

SELECT * 

FROM OPENJSON(@data)

WITH  (

CODIGO int '$.CODIGO',  

EMAIL varchar(255) '$.EMAIL', 

NOME varchar(255) '$.NOME'

);