Tipo de dados JSON MySQL

Resumo : neste tutorial, você aprenderá como usar o tipo de dados MySQL JSON para armazenar documentos JSON no banco de dados.

O que é JSON

JSON significa “Notação de Objeto JavaScript”. JSON é um formato leve de intercâmbio de dados que é fácil de ler e escrever para humanos e simples para computadores analisarem e gerarem.

JSON é construído em duas estruturas de dados: objetos e arrays .

Objetos

Um objeto é uma coleção não ordenada de pares de valores-chave entre chaves {}. Cada chave é uma string, seguida por dois pontos ( :) e, em seguida, o valor associado. Por exemplo:

{
    "name": "John Doe",
    "age": 22
}  Linguagem de código:  JSON/JSON com comentários  ( json )

Observe que você deve colocar a chave em um documento JSON entre aspas duplas ( ").

Matrizes

Uma matriz é uma lista ordenada de valores fechados entre colchetes []. Uma matriz pode conter valores de qualquer tipo de dados JSON válido, incluindo objetos e outras matrizes. Por exemplo:

["John Doe", 22]Linguagem de código:  JSON/JSON com comentários  ( json )

Neste exemplo, temos um array JSON de dois valores que representam o nome e a idade de uma pessoa.

JSON oferece suporte a vários tipos de dados, incluindo:

• Sequência: “JSON”
• Número: 10, 12,25
• Booleano: verdadeiro e falso.
• Nulo nulo

Na prática, você usa JSON no desenvolvimento web para:

• Arquivos de configuração
• Troca de dados entre um cliente e um servidor

Introdução ao tipo de dados MySQL JSON

MySQL suporta o tipo de dados JSON nativo definido pela  RFC 7159   a partir da versão 5.7.8. O tipo de dados JSON nativo oferece as seguintes vantagens:

• O MySQL valida os documentos JSON armazenados na coluna JSON e emite um erro se forem inválidos.
• O MySQL armazena os documentos JSON em formato binário otimizado para pesquisas rápidas.

O MySQL usa aproximadamente o mesmo armazenamento para documentos JSON e para dados LONGBLOG ou LONGTEXT.

Veja a seguir como definir uma coluna de tabela com o tipo de dados JSON:

column_name JSONLinguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A coluna JSON pode armazenar valores NULL. Além disso, a partir do MySQL 8.0.13, uma coluna JSON pode aceitar um valor padrão.

Não é possível indexar diretamente uma coluna JSON. Em vez disso, você pode usar um índice funcional para criar um índice em valores extraídos da coluna JSON.

Ao consultar dados de uma coluna JSON, o otimizador MySQL procurará índices funcionais que correspondam às expressões JSON.

Exemplo de tipo de dados MySQL JSON

Daremos um exemplo de uso do tipo de dados JSON.

1) Criando uma tabela com uma coluna JSON

A instrução a seguir cria uma nova tabela chamada productscom uma coluna JSON:

CREATE TABLE products(
   id INT AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(255) NOT NULL,
   price DECIMAL(10,2) NOT NULL,
   properties JSON
);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A productstabela possui quatro colunas:

• id: esta é uma coluna de chave primária de incremento automático que identifica exclusivamente um produto.
• name: Esta coluna armazena o nome do produto.
• price: Esta coluna armazena o preço do produto.
• properties: Esta coluna armazena as propriedades do produto como cores e tamanhos.

2) Inserindo dados JSON na tabela

A instrução a seguir insere uma nova linha com dados JSON na productstabela:

INSERT INTO products(name, price, properties)
VALUES('T-Shirt', 25.99, '{"sizes":["S","M","L","XL"], "colors": ["white","black"]}');Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Neste exemplo, inserimos uma nova linha na productstabela. O valor da propertiescoluna é um documento JSON com o seguinte formato:

{"sizes":["S","M","L","XL"],"colors":["white","black"]}Linguagem de código:  JSON/JSON com comentários  ( json )

E colocamos o documento JSON entre aspas simples (‘).

Quando a instrução é executada, o MySQL executa as seguintes etapas:

• Primeiro, valide a validade do documento JSON.
• Segundo, converta a string JSON em formato binário e armazene-a na coluna JSON.

3) Consultando dados JSON da tabela

A consulta a seguir recupera dados da productstabela:

SELECT name, properties 
FROM products;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

+---------+----------------------------------------------------------------+
| name    | properties                                                     |
+---------+----------------------------------------------------------------+
| T-Shirt | {"sizes": ["S", "M", "L", "XL"], "colors": ["white", "black"]} |
+---------+----------------------------------------------------------------+
1 row in set (0.00 sec)Linguagem de código:  texto simples  ( texto simples )

Se a saída for difícil de ler, você poderá usar a JSON_PRETTY()função para formatá-la.

SELECT JSON_PRETTY(properties)
FROM products;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

+------------------------------------------------------------------------------------------------------+
| JSON_PRETTY(properties)                                                                              |
+------------------------------------------------------------------------------------------------------+
| {
  "sizes": [
    "S",
    "M",
    "L",
    "XL"
  ],
  "colors": [
    "white",
    "black"
  ]
} |
+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)Linguagem de código:  texto simples  ( texto simples )

4) Obtendo as chaves de um documento JSON

O seguinte usa a JSON_KEYS()função para listar todas as chaves do documento JSON:

SELECT JSON_KEYS(properties)
FROM products;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

+-----------------------+
| JSON_KEYS(properties) |
+-----------------------+
| ["sizes", "colors"]   |
+-----------------------+
1 row in set (0.01 sec)
Linguagem de código:  texto simples  ( texto simples )

Neste exemplo, usamos a JSON_KEYS()função para obter todas as chaves do documento JSON armazenadas na propertiescoluna.

A função retorna um array JSON contendo todas as chaves do documento JSON.

5) Extraindo dados de um documento JSON

Para especificar um local em um documento JSON, use uma expressão de caminho JSON . Uma expressão de caminho permite navegar pela estrutura de um documento JSON para acessar dados específicos.

• Use o cifrão ( $) para representar o documento atual.
• Use uma chave ou índice de array para especificar a localização exata.

Por exemplo, para obter as primeiras cores do produto, você utiliza o seguinte caminho:

$.colors[0]Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Para extrair a primeira cor do documento JSON com o caminho acima, você usa a JSON_EXTRACT()função. Por exemplo:

SELECT JSON_EXTRACT(properties, "$.colors[0]")
FROM products;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

+-----------------------------------------+
| JSON_EXTRACT(properties, "$.colors[0]") |
+-----------------------------------------+
| "white"                                 |
+-----------------------------------------+
1 row in set (0.00 sec)Linguagem de código:  texto simples  ( texto simples )

Resumo

• MySQL suporta tipos de dados JSON nativos a partir da versão 5.7.8.
• MySQL armazena JSON em formato binário otimizado para pesquisas rápidas.
• Use a JSON_PRETTY()função para formatar os documentos JSON.
• Use a JSON_KEY()função para obter todas as chaves de um documento JSON.
• Use a JSON_EXTRACT()função para extrair dados de um documento JSON.