Manipulação de Dados JSON no MySQL

Introdução

Neste laboratório, você aprenderá como usar efetivamente o tipo de dado JSON no MySQL. Você realizará operações fundamentais como inserir documentos JSON, consultar campos específicos usando funções como JSON_EXTRACT e o operador ->>, modificar dados dentro de uma coluna JSON e otimizar consultas criando um índice em uma propriedade JSON.

Ao longo deste laboratório, você se conectará a um servidor MySQL, criará um banco de dados e uma tabela dedicados e, em seguida, realizará uma série de tarefas práticas para aprimorar suas habilidades no gerenciamento de dados JSON em um contexto de banco de dados relacional.

Conectar ao MySQL e Criar o Banco de Dados

Nesta primeira etapa, você se conectará ao servidor MySQL e configurará o banco de dados e a tabela necessários para o laboratório.

Primeiro, abra o terminal a partir do seu desktop.

Conecte-se ao servidor MySQL com privilégios de usuário root. Neste ambiente de laboratório, sudo permite que você se conecte sem senha.

sudo mysql -u root

Uma vez conectado, o prompt de comando mudará para mysql>, indicando que você está no shell do MySQL.

Em seguida, crie um novo banco de dados chamado jsondb. A cláusula IF NOT EXISTS garante que o comando seja executado sem erros se o banco de dados já existir.

CREATE DATABASE IF NOT EXISTS jsondb;

Agora, mude para o seu banco de dados recém-criado para torná-lo o banco de dados ativo para os comandos subsequentes.

USE jsondb;

Finalmente, crie uma tabela chamada products. Esta tabela incluirá uma coluna com o tipo de dado JSON para armazenar informações detalhadas do produto.

CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(255),
    product_details JSON
);

Esta instrução define uma tabela com três colunas:

• id: Um inteiro único e auto-incrementável para cada registro.
• product_name: Uma string para o nome do produto.
• product_details: Uma coluna JSON para armazenar dados estruturados.

Você configurou com sucesso o banco de dados e a tabela necessários. Mantenha o shell do MySQL aberto para a próxima etapa.

Inserir e Consultar Dados JSON

Com a tabela criada, você agora inserirá um registro contendo um documento JSON e, em seguida, realizará uma consulta básica para recuperá-lo.

No mesmo shell do MySQL, execute a seguinte instrução INSERT para adicionar um novo produto.

INSERT INTO products (product_name, product_details) VALUES (
    'Laptop',
    '{
        "brand": "Dell",
        "model": "XPS 13",
        "specs": {
            "processor": "Intel Core i7",
            "memory": "16GB",
            "storage": "512GB SSD"
        },
        "price": 1200
    }'
);

Este comando insere um registro de 'Laptop'. A coluna product_details é preenchida com um objeto JSON que inclui dados aninhados, como specs.

Para confirmar que os dados foram inseridos corretamente, consulte a tabela products para visualizar seu conteúdo.

SELECT * FROM products;

A saída deve exibir a linha que você acabou de inserir. Observe como os dados JSON são armazenados na coluna product_details.

+----+--------------+--------------------------------------------------------------------------------------------------------------------------------+
| id | product_name | product_details                                                                                                                |
+----+--------------+--------------------------------------------------------------------------------------------------------------------------------+
|  1 | Laptop       | {"brand": "Dell", "model": "XPS 13", "price": 1200, "specs": {"memory": "16GB", "storage": "512GB SSD", "processor": "Intel Core i7"}} |
+----+--------------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Você inseriu com sucesso um registro com dados JSON. Na próxima etapa, você aprenderá como extrair informações específicas deste objeto JSON.

Extrair Dados de Campos JSON

Armazenar dados em JSON é útil, mas você também precisa ser capaz de consultar campos individuais dentro dele. Nesta etapa, você usará a função JSON_EXTRACT e JSON_UNQUOTE para extrair valores específicos da coluna product_details.

A função JSON_EXTRACT permite selecionar um valor de um documento JSON usando uma expressão de caminho (path expression). O caminho começa com $ para representar a raiz do documento.

Vamos extrair a brand (marca) do laptop.

SELECT JSON_EXTRACT(product_details, '$.brand') AS brand FROM products WHERE product_name = 'Laptop';

Esta consulta retorna a marca, mas observe que o resultado é uma string JSON, que inclui aspas duplas.

+--------+
| brand  |
+--------+
| "Dell" |
+--------+
1 row in set (0.00 sec)

Para um resultado mais limpo, você pode usar JSON_UNQUOTE combinado com JSON_EXTRACT. Esta combinação extrai o valor e remove as aspas, retornando uma string padrão.

SELECT JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.brand')) AS brand FROM products WHERE product_name = 'Laptop';

A saída agora é o texto puro Dell.

+-------+
| brand |
+-------+
| Dell  |
+-------+
1 row in set (0.00 sec)

Você também pode usar expressões de caminho para acessar valores em objetos aninhados. Para obter o processor (processador) do objeto specs, use o caminho $.specs.processor.

SELECT JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.specs.processor')) AS processor FROM products WHERE product_name = 'Laptop';

Isso extrairá corretamente o valor aninhado.

+-----------------+
| processor       |
+-----------------+
| Intel Core i7   |
+-----------------+
1 row in set (0.00 sec)

Essas funções também são úteis em cláusulas WHERE para filtrar linhas. Para encontrar todos os produtos com preço acima de 1000, você deve CAST (converter) o valor JSON extraído para um tipo numérico para comparação.

SELECT product_name, JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.price')) AS price FROM products WHERE CAST(JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.price')) AS SIGNED) > 1000;

Esta consulta demonstra como filtrar registros com base em um valor numérico dentro de um campo JSON.

+--------------+-------+
| product_name | price |
+--------------+-------+
| Laptop       | 1200  |
+--------------+-------+
1 row in set (0.00 sec)

Agora você sabe como extrair e filtrar dados com base em campos JSON.

Atualizar e Adicionar Campos JSON

Os dados mudam com o tempo, e você precisa de uma maneira de modificar documentos JSON armazenados em seu banco de dados. Nesta etapa, você usará a função JSON_SET para atualizar valores existentes e adicionar novos pares chave-valor.

A função JSON_SET modifica um documento JSON, recebendo como argumentos a coluna de destino, um caminho para o campo e o novo valor.

Primeiro, vamos atualizar o price (preço) do laptop de 1200 para 1250.

UPDATE products
SET product_details = JSON_SET(product_details, '$.price', 1250)
WHERE product_name = 'Laptop';

Para verificar a alteração, consulte o preço novamente.

SELECT JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.price')) AS price FROM products WHERE product_name = 'Laptop';

A saída agora deve mostrar o novo preço.

+-------+
| price |
+-------+
| 1250  |
+-------+
1 row in set (0.00 sec)

Se o caminho especificado não existir, JSON_SET adicionará a nova chave e o novo valor. Vamos adicionar uma propriedade color (cor) ao produto.

UPDATE products
SET product_details = JSON_SET(product_details, '$.color', 'Silver')
WHERE product_name = 'Laptop';

Agora, consulte todo o objeto JSON para ver o campo recém-adicionado.

SELECT product_details FROM products WHERE product_name = 'Laptop';

A saída mostrará o documento product_details, que agora inclui a propriedade color.

+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| product_details                                                                                                                                     |
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| {"brand": "Dell", "color": "Silver", "model": "XPS 13", "price": 1250, "specs": {"memory": "16GB", "storage": "512GB SSD", "processor": "Intel Core i7"}} |
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Você modificou e estendeu com sucesso um documento JSON dentro de uma tabela.

Criar um Índice numa Propriedade JSON

Para tabelas grandes, consultar campos JSON pode ser lento. Para melhorar o desempenho, você pode criar um índice em um valor extraído de uma coluna JSON. No MariaDB, isso é alcançado primeiro adicionando uma coluna virtual baseada no campo JSON e, em seguida, criando um índice nessa coluna virtual.

Nesta etapa, você criará uma coluna virtual para a propriedade price e, em seguida, a indexará para acelerar consultas baseadas em preço.

Primeiro, adicione uma coluna virtual que extrai o preço dos dados JSON:

ALTER TABLE products ADD COLUMN price_virtual INT AS (CAST(JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.price')) AS SIGNED)) STORED;

Este comando adiciona uma coluna virtual chamada price_virtual que calcula e armazena automaticamente o valor do preço a partir dos dados JSON.

Agora crie um índice nesta coluna virtual:

CREATE INDEX idx_product_price ON products (price_virtual);

Essa abordagem permite que o MariaDB procure eficientemente por linhas com base no preço numérico, utilizando a coluna virtual indexada.

Para confirmar que o índice foi criado, use o comando SHOW INDEXES.

SHOW INDEXES FROM products;

A saída listará todos os índices na tabela products, incluindo seu novo idx_product_price.

+----------+------------+-------------------+...
| Table    | Non_unique | Key_name          |...
+----------+------------+-------------------+...
| products |          0 | PRIMARY           |...
| products |          1 | idx_product_price |...
+----------+------------+-------------------+...

A parte mais importante é verificar se o otimizador usa o índice. Você pode verificar isso com o comando EXPLAIN.

EXPLAIN SELECT product_name FROM products WHERE price_virtual > 1200;

Na saída do EXPLAIN, observe as colunas possible_keys e key. Você deverá ver idx_product_price listado, confirmando que o MariaDB está usando seu índice para executar a consulta de forma eficiente.

Você também pode consultar usando a expressão JSON original, e o otimizador do MariaDB ainda deverá ser capaz de usar o índice na coluna virtual:

EXPLAIN SELECT product_name FROM products WHERE CAST(JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.price')) AS SIGNED) > 1200;

Você criou com sucesso uma coluna virtual e a indexou para otimizar consultas de propriedades JSON.

Você agora pode sair do shell do MySQL.

exit

Resumo

Neste laboratório, você ganhou experiência prática no manuseio de dados JSON no MariaDB. Você aprendeu o fluxo de trabalho completo, desde a configuração da estrutura do banco de dados até a execução de operações avançadas.

Você inseriu com sucesso dados JSON estruturados, consultou campos específicos usando JSON_EXTRACT e JSON_UNQUOTE, e filtrou registros com base em valores dentro do documento JSON. Você também praticou a modificação desses dados com JSON_SET para atualizar e adicionar novas propriedades. Finalmente, você aprendeu uma técnica de otimização chave criando uma coluna virtual para uma propriedade JSON e indexando-a para melhorar o desempenho das consultas.

Essas habilidades são valiosas para projetar esquemas de banco de dados flexíveis e gerenciar eficientemente dados semiestruturados no MariaDB.