Introdução
JavaScript Object Notation (JSON) é um formato popular de troca de dados porque é fácil de serializar e desserializar tanto por humanos quanto por máquinas. JSON também é amplamente suportado pela maioria das principais linguagens de programação, e existem centenas de bibliotecas para gerar e analisar JSON.
No MySQL, você pode definir JSON como um tipo de dados ao criar colunas de tabela. Embora você possa armazenar praticamente os mesmos dados JSON em um campo de texto, o tipo de dados JSON tem algumas vantagens. Um, o formato de armazenamento otimizado permite acessar os elementos do documento de forma eficiente. Dois, os campos definidos com o tipo de dados JSON oferecem validação automática garantindo que apenas documentos sintaticamente válidos sejam salvos no banco de dados.
Neste guia, você verá um conjunto de funções e tipos de dados MySQL suportados que permitem operações de valores JSON para fornecer acesso a pares chave-valor JSON individuais. Você também verá a flexibilidade e o poder de algumas dessas funções quando se trata de validar, criar, pesquisar e manipular documentos JSON.
Pré-requisitos
Para seguir este tutorial, você precisa do seguinte:
- Um Ubuntu 20.04 server. Embora este guia seja testado em um servidor Ubuntu 20.04, ele deve funcionar muito bem em qualquer outra distribuição que suporte MySQL.
- Um usuário não root com privilégios
sudo. - Um Servidor MySQL.
1. Criar um Banco de Dados de Exemplo
Conecte-se ao seu servidor e faça login no MySQL como root.
$ sudo mysql -u root -p
Em seguida, insira a senha de root do MySQL e pressione ENTER para prosseguir. Depois de conectado ao servidor MySQL, execute a declaração abaixo para criar um banco de dados de exemplo json_test.
mysql> CREATE DATABASE json_test;
Saída.
Query OK, 1 row affected (0.00 sec)
Mude para o novo banco de dados json_test.
mysql> USE json_test;
Saída.
Database changed
Em seguida, defina uma tabela customers. Esta tabela identificará os clientes usando uma coluna AUTO_INCREMENT customer_id, que atua como a PRIMARY KEY. Você também coletará os nomes dos clientes usando os campos first_name e last_name definidos com o tipo de dados VARCHAR. Primeiro, porém, você capturará as informações de endereço dos clientes usando o tipo de dados JSON. Este campo lhe dará a flexibilidade de aceitar diferentes informações de endereço, que serão dinâmicas. Por exemplo, alguns clientes podem ter um único número de telefone enquanto outros podem ter dois contatos diferentes para suas casas e escritórios.
Crie a tabela customers.
mysql> CREATE table customers (
customer_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
address JSON
) ENGINE = InnoDB;
Saída.
Query OK, 0 rows affected (0.02 sec)
Depois de definir um banco de dados e uma tabela, você aprenderá agora sobre os diferentes tipos de dados JSON suportados no MySQL.
2. Tipos de Dados de Documento MySQL
No MySQL, você deve escrever valores JSON como strings ao executar instruções SQL. O MySQL analisa o texto e gera um erro se você fornecer um documento JSON inválido. Antes de começar a trabalhar com as funções JSON do MySQL, familiarize-se com os seguintes tipos de dados JSON:
-
String JSON: Este é um tipo de dado baseado em texto que deve ser cercado por aspas(
"). Por exemplo, no trecho JSON abaixo, "PRODUTO AMOSTRA 1" é uma string JSON, enquanto "nome_do_produto" é uma chave JSON.{ "nome_do_produto":"PRODUTO AMOSTRA 1" } -
Número JSON: O formato de número JSON aceita inteiros e valores com ponto decimal flutuante. No seguinte documento, "25" e "37.89" são números JSON atribuídos aos valores "preço_de_custo" e "preço_de_venda" respectivamente.
{ "nome_do_produto":"PRODUTO AMOSTRA 1", "preço_de_custo":25, "preço_de_venda":37.89 } -
Booleano JSON: Este é um tipo de dado com apenas dois resultados. Ou seja, true ou false. Você pode usar esse tipo de dado em diferentes situações. Por exemplo, em um banco de dados de funcionários, você pode criar uma chave is_casado e defini-la como true ou false dependendo do estado civil de um funcionário. Além disso, em um catálogo de produtos, você pode ativar e desativar recursos de produtos usando o tipo de dado booleano, como mostrado abaixo.
{ "nome_do_produto":"PRODUTO AMOSTRA 1", "preço_de_custo":25, "preço_de_venda":37.89 "disponível_para_venda":true "é_produto_físico":false "com_desconto":false } -
Objeto JSON: Este é um conjunto de pares chave-valor cercados por chaves { e }. Todos os documentos usados nos exemplos anteriores são objetos JSON. No entanto, o objeto JSON é útil quando você está aninhando valores para uma única chave. Por exemplo, no exemplo abaixo, o valor da chave extended_price é um objeto JSON.
{ "nome_do_produto":"PRODUTO AMOSTRA 1", "preço_de_custo":25, "preço_de_venda":37.89, "extended_price":{ "preço_com_desconto":34.26, "preço_de_atacado":30.50, "custo_de_envio":5.21 } } -
Array JSON: Esta é uma lista de valores separados por vírgulas e cercados por colchetes. Ou seja, [ e ]. Por exemplo, para exibir os atributos de dois produtos diferentes, você pode usar o seguinte array JSON.
[ { "nome_do_produto":"PRODUTO AMOSTRA 1", "preço_de_custo":25, "preço_de_venda":37.89 }, { "nome_do_produto":"PRODUTO AMOSTRA 2", "preço_de_custo":180.85, "preço_de_venda":256.25 } ]
Agora que você está familiarizado com a maioria dos tipos de dados JSON, você agora validará e salvará alguns registros baseados em JSON na tabela customers.
3. Validar, Verificar Tipo e Salvar Documento JSON na Tabela MySQL
Depois de decidir sobre o formato que deseja usar em uma coluna JSON, você pode obter seu documento e inseri-lo em sua tabela. Nesta demonstração, você começará inserindo um registro de amostra de JOHN DOE com os seguintes dados.
-
first_name:
JOHN -
last_name:
DOE -
address: Nesta coluna, você capturará o endereço, cidade, estado, CEP e os diferentes números de telefone do cliente usando um objeto JSON como mostrado abaixo.
{ "street":"97 SIMPLE RD. NW #2", "town":"NEW TOWN", "state":"AZ", "zip":1013, "phone":{ "home":111111, "work":222222 }, "available_in_day_time":true }
Antes de inserir os dados na tabela customers, use a função JSON_VALID incorporada do MySQL para verificar se a sintaxe do documento é válida.
mysql> SELECT JSON_VALID('{
"street":"97 SIMPLE RD. NW #2",
"town":"NEW TOWN",
"state":"AZ",
"zip":1013,
"phone":{
"home":111111,
"work":222222
},
"available_in_day_time":true
}') AS is_valid;
A resposta abaixo confirma que as informações de endereço acima são um documento JSON válido.
+----------+
| is_valid |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
Em seguida, prossiga e salve as informações do cliente no banco de dados juntamente com as informações de endereço JSON. Para facilitar o acompanhamento do guia, primeiro coloque as informações de contato em uma variável @address executando o comando abaixo.
mysql> SET @address = ('{
"street":"97 SIMPLE RD. NW #2",
"town":"NEW TOWN",
"state":"AZ",
"zip":1013,
"phone":{
"home":111111,
"work":222222
},
"available_in_day_time":true
}');
Saída.
Query OK, 0 rows affected (0.00 sec)
Em seguida, confirme o tipo de documento usando a função JSON_TYPE.
mysql> SELECT JSON_TYPE(@address);
A saída abaixo confirma que, de fato, o endereço é um objeto JSON.
+---------------------+
| JSON_TYPE(@address) |
+---------------------+
| OBJECT |
+---------------------+
1 row in set (0.00 sec)
Em seguida, execute a instrução INSERT abaixo para salvar o registro do cliente e use a variável @address para capturar as informações de endereço.
mysql> INSERT INTO customers (first_name, last_name, address) VALUES ('JOHN', 'DOE', @address);
O MySQL executa sua instrução INSERT sem erros e exibe a saída abaixo.
Query OK, 1 row affected (0.01 sec)
Confirme o registro executando uma instrução SELECT contra a tabela customers.
mysql> SELECT
customer_id,
first_name,
last_name,
address
FROM customers;
O MySQL lista o registro como mostrado abaixo.
+-------------+------------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| customer_id | first_name | last_name | address |
+-------------+------------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | JOHN | DOE | {"zip": 1013, "town": "NEW TOWN", "phone": {"home": 111111, "work": 222222}, "state": "AZ", "street": "97 SIMPLE RD. NW #2", "available_in_day_time": true} |
+-------------+------------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Como mencionado anteriormente, o MySQL restringe a inserção de dados inválidos em colunas JSON. Você pode testar isso tentando inserir o seguinte registro. Primeiro, atribua um novo valor à variável de endereço e deixe intencionalmente a vírgula entre as chaves town e state, como mostrado abaixo.
mysql> SET @address = ('{
"street":"97 SIMPLE RD. NW #2",
"town":"SECOND TOWN"
"state":"NY",
"zip":5070,
"phone":{
"home":444444,
"work":777777
},
"available_in_day_time":TRUE
}');
Certifique-se de que a variável está definida, confirmando a seguinte saída.
Query OK, 0 rows affected (0.00 sec)
Em seguida, tente criar um novo cliente com os dados inválidos definidos.
mysql> INSERT INTO customers (first_name, last_name, address) VALUES ('MARY', 'ROE', @address);
O MySQL falha na execução do comando e exibe o seguinte erro. Se você estivesse salvando os dados JSON diretamente em um campo VARCHAR, o MySQL não ofereceria nenhum tipo de validação e você enfrentaria erros técnicos ao recuperar e tentar analisar os dados posteriormente. Portanto, sempre use o tipo de dados JSON ao trabalhar com documentos JSON.
teste 123
ERROR 3140 (22032): Invalid JSON text: "Missing a comma or '}' after an object member." at position 145 in value for column 'customers.address'.
Você pode ver que a mensagem de erro acima é detalhada o suficiente e fornece uma melhor compreensão sobre como corrigir seu documento JSON. No entanto, embora esse método funcione bem, ele não validará os valores de chave individuais contra sua lógica de negócios. Felizmente, você verá como pode definir seu esquema JSON e usá-lo com uma CHECK CONSTRAINT para validar elementos JSON individuais.
4. Validar Documento JSON Contra um Esquema
Ao definir uma coluna JSON, o MySQL oferece a flexibilidade de implementar uma CHECK CONSTRAINT para validar elementos individuais em relação a um esquema. Para testar essa funcionalidade, crie um esquema simples que valide a parte do zip do endereço do cliente. Por exemplo, no esquema abaixo, especifique que um código zip válido deve ser um número entre 1 e 9999 executando o código abaixo.
mysql> SET @valid_zip = '{
"type":"object",
"properties":{
"zip":{
"type":"number",
"minimum":1,
"maximum":9999
}
}
}';
Saída.
Query OK, 0 rows affected (0.00 sec)
Em seguida, defina um novo endereço que viole a regra. Neste caso, defina o zip para um valor grande de 999999.
mysql> SET @address = ('{
"street":"101 2nd RD.",
"town":"NEW TOWN",
"state":"NJ",
"zip":999999,
"phone":{
"home":444444,
"work":888888
},
"available_in_day_time":true
}');
Saída.
Query OK, 0 rows affected (0.00 sec)
Agora, use a função JSON_SCHEMA_VALID do MySQL para testar se o endereço @address está em conformidade com o esquema definido @valid_zip.
mysql> SELECT JSON_SCHEMA_VALID(@valid_zip, @address);
Você pode ver que a saída exibe 0, o que significa que os dados são inválidos. Um valor válido deve retornar 1.
+-----------------------------------------+
| JSON_SCHEMA_VALID(@valid_zip, @address) |
+-----------------------------------------+
| 0 |
+-----------------------------------------+
1 row in set (0.00 sec)
Defina um código zip válido em um novo endereço, por exemplo, 3630.
mysql> SET @address = ('{
"street":"101 2nd RD.",
"town":"NEW TOWN",
"state":"NJ",
"zip":3630,
"phone":{
"home":444444,
"work":888888
},
"available_in_day_time":true
}');
Saída.
Query OK, 0 rows affected (0.00 sec)
Verifique se o novo valor do endereço está dentro do intervalo executando o comando abaixo.
mysql> SELECT JSON_SCHEMA_VALID(@valid_zip, @address);
O valor de 1 abaixo confirma que o valor de 3630 é válido para a chave zip no endereço.
+-----------------------------------------+
| JSON_SCHEMA_VALID(@valid_zip, @address) |
+-----------------------------------------+
| 1 |
+-----------------------------------------+
1 row in set (0.00 sec)
Ao trabalhar em um ambiente de banco de dados multiusuário, você não pode confiar que todos os usuários validarão os dados JSON antes de executar uma instrução INSERT. Nesse caso, você deve implementar um CHECK CONSTRAINT ao definir sua tabela.
Como você já criou a tabela customers, altere-a usando o comando abaixo para definir a restrição do código zip.
mysql> ALTER TABLE customers
ADD CONSTRAINT zip_validator
CHECK(JSON_SCHEMA_VALID('{
"type":"object",
"properties":{
"zip":{
"type":"number",
"minimum":1,
"maximum":9999
}
}
}', address));
Certifique-se de receber a seguinte confirmação.
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
Em seguida, tente inserir um novo registro inválido na tabela customers. Comece colocando as informações de endereço na variável @address.
mysql> SET @address = ('{
"street":"1 SAMPLE STREET",
"town":"THIRD TOWN",
"state":"NY",
"zip":10000,
"phone":{
"home":222222,
"work":666666
},
"available_in_day_time":false
}');
Saída.
Query OK, 0 rows affected (0.00 sec)
Em seguida, execute a instrução INSERT abaixo.
mysql> INSERT INTO customers (first_name, last_name, address) VALUES ('JANE', 'SMITH', @address);
O MySQL exibe o erro abaixo detalhando que a CHECK CONSTRAINT definida foi violada.
ERROR 3819 (HY000): Check constraint 'zip_validator' is violated.
Altere o código zip no novo endereço para um valor dentro do intervalo definido pela restrição.
mysql> SET @address = ('{
"street":"1 SAMPLE STREET",
"town":"THIRD TOWN",
"state":"NY",
"zip": 7630,
"phone":{
"home":222222,
"work":666666
},
"available_in_day_time":false
}');
Saída.
Query OK, 0 rows affected (0.00 sec)
Em seguida, execute novamente a instrução INSERT e verifique a resposta.
mysql> INSERT INTO customers (first_name, last_name, address) VALUES ('JANE', 'SMITH', @address);
O MySQL deve agora inserir a nova linha e exibir a saída de confirmação abaixo.
Query OK, 1 row affected (0.01 sec)
Na próxima etapa, você analisará documentos JSON de uma tabela MySQL.
5. Analisar Documento JSON
Você pode desserializar um documento JSON e recuperar o valor de qualquer chave nomeada usando a função MySQL JSON_EXTRACT. Por exemplo, para recuperar as informações dos clientes juntamente com seus nomes de town individuais, execute o comando SQL abaixo em sua tabela de exemplo.
mysql> SELECT
first_name,
last_name,
JSON_EXTRACT(address, '$.town') as town
FROM customers;
Você pode ver na saída abaixo que os nomes das town foram extraídos.
+------------+-----------+--------------+
| first_name | last_name | town |
+------------+-----------+--------------+
| JOHN | DOE | "NEW TOWN" |
| JANE | SMITH | "THIRD TOWN" |
+------------+-----------+--------------+
2 rows in set (0.00 sec)
Da mesma forma, se você quiser extrair um valor de uma chave aninhada um nível mais profundo no documento JSON, por exemplo, os números de telefone home, use a sintaxe abaixo.
mysql> SELECT
first_name,
last_name,
JSON_EXTRACT(address, '$.phone.home') as phone
FROM customers;
Agora você tem os números de telefone home como mostrado abaixo.
+------------+-----------+--------+
| first_name | last_name | phone |
+------------+-----------+--------+
| JOHN | DOE | 111111 |
| JANE | SMITH | 222222 |
+------------+-----------+--------+
2 rows in set (0.00 sec)
Usando a mesma sintaxe de extração de caminho, recupere os números de telefone work executando o comando abaixo.
mysql> SELECT
first_name,
last_name,
JSON_EXTRACT(address, '$.phone.work') as phone
FROM customers;
Saída.
+------------+-----------+--------+
| first_name | last_name | phone |
+------------+-----------+--------+
| JOHN | DOE | 222222 |
| JANE | SMITH | 666666 |
+------------+-----------+--------+
2 rows in set (0.00 sec)
Além disso, quando você deseja recuperar valores JSON sem análise adicional, pode torná-los mais legíveis usando a função JSON_PRETTY(). Por exemplo, para recuperar uma lista de informações de endereço de clientes formatadas de forma mais legível, execute o comando abaixo.
mysql> SELECT
JSON_PRETTY(address)
FROM customers;
As informações de endereço devem ser impressas em um formato organizado como mostrado abaixo.
+---------------------------------------+
| JSON_PRETTY(address) |
+---------------------------------------+
| {
"zip": 1013,
"town": "NEW TOWN",
"phone": {
"home": 111111,
"work": 222222
},
"state": "AZ",
"street": "97 SIMPLE RD. NW #2",
"available_in_day_time": true
} |
| {
"zip": 7630,
"town": "THIRD TOWN",
"phone": {
"home": 222222,
"work": 666666
},
"state": "NY",
"street": "1 SAMPLE STREET",
"available_in_day_time": false
} |
+---------------------------------------+
2 rows in set (0.00 sec)
Como você pode ver nos resultados do MySQL, as funções JSON estão funcionando conforme o esperado.
Conclusão
Neste tutorial, você criou um banco de dados de teste de amostra e aprendeu todos os tipos de dados JSON suportados pelo MySQL, incluindo strings, números, arrays, objetos e variáveis booleanas. Você também passou pelos passos de validar documentos JSON em relação a esquemas personalizados. No final, você analisou e extraiu valores JSON do banco de dados usando uma sintaxe elegante de extração de caminho. Use o conhecimento que você ganhou neste guia na próxima vez que você trabalhar com documentos JSON no MySQL.
FAQ
Pergunta e respostas frequentes sobre o conteúdo.
JSON
O tipo de dados JSON oferece validação automática para garantir que apenas documentos JSON sintaticamente válidos sejam armazenados no banco de dados, além de permitir o acesso eficiente aos elementos do documento.
Você pode usar a função JSON_VALID para verificar se a sintaxe de um documento JSON é válida em MySQL.
Você pode usar a função JSON_EXTRACT em MySQL para extrair um valor de uma chave específica de um documento JSON.
Os tipos de dados JSON suportados pelo MySQL incluem strings, números, arrays, objetos e variáveis booleanas.
Você pode usar a função JSON_PRETTY para formatar os valores JSON de forma mais legível ao recuperá-los de um banco de dados MySQL.
Você pode implementar um CHECK CONSTRAINT ao definir uma tabela no MySQL para validar elementos individuais de um documento JSON em relação a um esquema personalizado.
A função MySQL usada para verificar se um documento JSON atende a um determinado esquema é JSON_SCHEMA_VALID.
Os passos para inserir um documento JSON em uma tabela MySQL incluem validar o documento, definir o documento em uma variável e usar a variável no comando INSERT INTO.
Você pode usar a função JSON_EXTRACT com a sintaxe correta para extrair valores aninhados de um documento JSON em MySQL.
Você pode implementar um CHECK CONSTRAINT ao definir a tabela no MySQL para validar os dados JSON em relação a um esquema personalizado.
O tipo de dados JSON em MySQL oferece validação automática para garantir que apenas documentos JSON sintaticamente válidos sejam armazenados no banco de dados.
Você pode usar a função JSON_SCHEMA_VALID em MySQL para validar um documento JSON em relação a um esquema personalizado.
Você pode usar a função JSON_EXTRACT em MySQL para extrair valores específicos de um documento JSON.
Você pode usar a função JSON_PRETTY em MySQL para formatar os valores JSON armazenados em uma tabela de forma legível.
Especialista linux, desenvolvedor web full-stack, PHP no sangue, graduando em Direito, pai da Lais e esposo da Simone
