Como Manipular Dados de Array no MySQL Usando JSON (Guia Completo com Exemplos)

1. Introdução

A Necessidade de Manipular Dados de Array no MySQL

Os bancos de dados normalmente armazenam dados com base em princípios de design relacional. No entanto, dependendo dos requisitos da aplicação, podem existir casos em que você queira armazenar múltiplos valores em uma única coluna. Nessas situações, uma estrutura de dados semelhante a um “array” torna‑se útil.

Por exemplo, considere os seguintes cenários:

• Armazenar várias tags selecionadas por um usuário.
• Salvar múltiplas URLs de imagens de um produto.
• Combinar histórico ou logs em um único campo.

Benefícios de Usar o Tipo de Dados JSON

O MySQL não fornece um “tipo array” direto, mas ao usar o tipo de dados JSON, você pode manipular estruturas de dados semelhantes a arrays. O tipo JSON é altamente flexível e oferece as seguintes vantagens:

• Suporta estruturas de dados aninhadas.
• Permite fácil manipulação de dados dentro de consultas.
• Possibilita o gerenciamento de múltiplos formatos de dados em um único campo.

Neste artigo, apresentaremos como lidar de forma eficiente com dados de array no MySQL usando o tipo de dados JSON.

2. Conhecimentos Básicos sobre Manipulação de Arrays com MySQL JSON

O Que é o Tipo de Dados JSON?

JSON (JavaScript Object Notation) é um formato leve e simples de intercâmbio de dados. No MySQL, o suporte nativo a JSON foi introduzido na versão 5.7 e posteriores, permitindo armazenar e manipular dados formatados em JSON diretamente no banco de dados.

Exemplo: Abaixo está um exemplo de dados que podem ser armazenados em uma coluna JSON.

{
  "tags": ["PHP", "MySQL", "JSON"],
  "status": "published"
}

Vantagens e Casos de Uso do Tipo de Dados JSON

Os principais benefícios de usar o tipo JSON são os seguintes:

1. Estrutura de Dados Flexível: você pode lidar com dados de comprimento variável sem modificar o esquema relacional.
2. Manipulação de Dados Eficiente: você pode manipular dados facilmente usando funções dedicadas do MySQL (por exemplo, JSON_EXTRACT, JSON_ARRAY).
3. Design Sem Esquema: não há necessidade de modificar o esquema com frequência quando os requisitos da aplicação mudam.

Exemplos de casos de uso:

• Atribuir múltiplas categorias a informações de um produto.
• Salvar configurações personalizadas do usuário.
• Utilizar dados JSON aninhados em aplicações web.

3. Operações Básicas com Arrays JSON

Criando um Array JSON

No MySQL, você pode criar facilmente um array JSON usando a função JSON_ARRAY. Arrays são úteis ao armazenar múltiplos valores em uma única coluna.

Exemplo

A consulta a seguir cria um array JSON chamado tags.

SELECT JSON_ARRAY('PHP', 'MySQL', 'JavaScript') AS tags;

Resultado:

["PHP", "MySQL", "JavaScript"]

Exemplo Prático

O exemplo a seguir mostra como armazenar um array JSON no banco de dados usando uma instrução INSERT.

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tags JSON
);

INSERT INTO articles (tags) 
VALUES (JSON_ARRAY('PHP', 'MySQL', 'JavaScript'));

Extraindo Dados de um Array JSON

Para recuperar dados armazenados em um array JSON, use a função JSON_EXTRACT. Essa função permite extrair facilmente elementos específicos do array.

Exemplo

O exemplo a seguir recupera o segundo elemento do array (o índice começa em 0).

SELECT JSON_EXTRACT('["PHP", "MySQL", "JavaScript"]', '$[1]') AS second_tag;

Resultado:

"MySQL"

Recuperando Múltiplos Elementos

Você também pode recuperar vários elementos de uma só vez.

SELECT JSON_EXTRACT('["PHP", "MySQL", "JavaScript"]', '$[0]', '$[2]') AS extracted_values;

Adicionando, Atualizando e Removendo Dados

Adicionando Dados a um Array

Você pode usar a função JSON_ARRAY_APPEND para adicionar novos dados a um array existente.

SET @tags = '["PHP", "MySQL"]';
SELECT JSON_ARRAY_APPEND(@tags, '$', 'JavaScript') AS updated_tags;

Resultado:

["PHP", "MySQL", "JavaScript"]

Atualizando Dados em um Array

Você pode atualizar um elemento específico no array usando a função JSON_SET.

SET @tags = '["PHP", "MySQL", "JavaScript"]';
SELECT JSON_SET(@tags, '$[1]', 'Python') AS updated_tags;

Resultado:

["PHP", "Python", "JavaScript"]

Removendo Dados de um Array

Você pode remover um elemento específico do array usando a função JSON_REMOVE.

SET @tags = '["PHP", "MySQL", "JavaScript"]';
SELECT JSON_REMOVE(@tags, '$[1]') AS updated_tags;

Resultado:

["PHP", "JavaScript"]

4. Pesquisando e Filtrando Arrays JSON

Pesquisando Arrays que Contêm Dados Específicos

Para verificar se um array JSON contém dados específicos, use a função JSON_CONTAINS. Esta função determina se um valor especificado existe dentro do array JSON.

Exemplo

O exemplo a seguir verifica se o array JSON contém “MySQL”.

SELECT JSON_CONTAINS('["PHP", "MySQL", "JavaScript"]', '"MySQL"') AS is_present;

Resultado:

1  (if present)
0  (if not present)

Exemplo Prático: Busca Condicional

Para buscar linhas que contenham um valor específico em um array JSON dentro de uma tabela de banco de dados, use JSON_CONTAINS na cláusula WHERE.

SELECT * 
FROM articles
WHERE JSON_CONTAINS(tags, '"MySQL"');

Esta consulta recupera linhas onde a coluna tags contém “MySQL”.

Obtendo o Tamanho de um Array

Para obter o número de elementos em um array JSON, use a função JSON_LENGTH. Esta função retorna o número de elementos no array e é útil para análise de dados e lógica condicional.

Exemplo

O exemplo a seguir recupera o número de elementos no array.

SELECT JSON_LENGTH('["PHP", "MySQL", "JavaScript"]') AS array_length;

Resultado:

3

Exemplo Prático: Extraindo Linhas que Atendem a uma Condição Específica

Para extrair linhas onde o número de elementos é maior ou igual a um valor específico, use JSON_LENGTH na cláusula WHERE.

SELECT * 
FROM articles
WHERE JSON_LENGTH(tags) >= 2;

Esta consulta recupera linhas onde a coluna tags contém dois ou mais elementos.

Exemplo Avançado de Consulta Condicional

Você pode combinar múltiplas condições para buscas mais avançadas. A consulta a seguir procura linhas onde o array tags contém “JavaScript” e tem três ou mais elementos.

SELECT * 
FROM articles
WHERE JSON_CONTAINS(tags, '"JavaScript"') 
  AND JSON_LENGTH(tags) >= 3;

5. Exemplos Práticos: Usando Arrays JSON em Casos Reais

Como Armazenar Categorias de Produto como um Array JSON

Em sites de e‑commerce e sistemas semelhantes, um produto pode pertencer a várias categorias. Nesses casos, você pode armazenar eficientemente as informações de categoria usando um array JSON.

Exemplo: Armazenando Dados de Categoria de Produto

Abaixo está um exemplo de criação de uma coluna JSON chamada categories em uma tabela de produtos e armazenando múltiplas categorias.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    categories JSON
);

INSERT INTO products (name, categories) 
VALUES ('Laptop', JSON_ARRAY('Electronics', 'Computers')),
       ('Smartphone', JSON_ARRAY('Electronics', 'Mobile Devices'));

Esta estrutura mantém os dados concisos mesmo quando um produto pertence a várias categorias.

Consulta para Extrair Produtos de uma Categoria Específica

Aproveitando o tipo de dado JSON, você pode buscar facilmente produtos que pertencem a uma categoria específica.

Exemplo de Consulta

A consulta a seguir recupera todos os produtos na categoria “Electronics”.

SELECT name 
FROM products
WHERE JSON_CONTAINS(categories, '"Electronics"');

Resultado:

Laptop
Smartphone

Esta consulta facilita a recuperação de listas de produtos por categoria de forma flexível.

Exemplo: Filtrando por Faixa de Preço

Vamos ver como armazenar dados JSON que incluem informações de preço e então buscar produtos com base em uma faixa de preço.

Dados de Exemplo

O exemplo a seguir armazena informações de preço por produto usando o tipo JSON.

CREATE TABLE products_with_prices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    details JSON
);

INSERT INTO products_with_prices (name, details)
VALUES ('Laptop', '{"price": 150000, "categories": ["Electronics", "Computers"]}'),
       ('Smartphone', '{"price": 80000, "categories": ["Electronics", "Mobile Devices"]}');

Exemplo de Consulta

Para buscar produtos com preço de 100.000 ou mais, use a função JSON_EXTRACT.

SELECT name 
FROM products_with_prices
WHERE JSON_EXTRACT(details, '$.price') >= 100000;

Resultado:

Laptop

Expandindo JSON com JSON_TABLE e Exemplo de Consulta

Se você quiser consultar dados JSON em um formato relacional, a função JSON_TABLE é muito útil. Essa função permite expandir um array JSON em uma tabela virtual.

Exemplo

O exemplo a seguir expande um array JSON e exibe cada categoria como uma linha separada.

SELECT * 
FROM JSON_TABLE(
    '["Electronics", "Computers", "Mobile Devices"]',
    '$[*]' COLUMNS(
        category_name VARCHAR(100) PATH '$'
    )
) AS categories_table;

Resultado:

category_name
--------------
Electronics
Computers
Mobile Devices

6. Considerações Importantes ao Usar o Tipo de Dados JSON

Dicas de Otimização de Performance

Embora o tipo JSON seja flexível, um design inadequado pode impactar negativamente o desempenho do banco de dados. Abaixo estão os principais pontos de otimização.

1. Usando Índices

No MySQL, você não pode criar um índice diretamente em uma coluna JSON, mas pode criar uma coluna gerada e indexar uma chave específica.

Exemplo: Criando um Índice Usando uma Coluna Gerada

No exemplo a seguir, a chave price dentro dos dados JSON é usada como alvo do índice.

ALTER TABLE products_with_prices
ADD COLUMN price INT AS (JSON_EXTRACT(details, '$.price')) STORED,
ADD INDEX idx_price (price);

Ao usar uma coluna gerada, você pode melhorar significativamente o desempenho de busca em dados JSON.

2. Evitar Estruturas JSON Excessivamente Complexas

Estruturas JSON profundamente aninhadas podem reduzir a legibilidade e o desempenho das consultas. Ao projetar os dados, escolha a estrutura JSON mais simples possível.

Bom exemplo:

{
  "categories": ["Electronics", "Computers"],
  "price": 150000
}

Exemplo a evitar:

{
  "product": {
    "details": {
      "price": 150000,
      "categories": ["Electronics", "Computers"]
    }
  }
}

Como Aproveitar Índices

Ao indexar usando colunas geradas, tenha em mente os seguintes pontos:

1. A coluna gerada deve ser STORED.
2. Use a função JSON_EXTRACT para extrair uma chave específica como coluna gerada.

Por exemplo, para extrair o primeiro elemento da chave categories e criar um índice, faça o seguinte.

ALTER TABLE products
ADD COLUMN main_category VARCHAR(255) AS (JSON_EXTRACT(categories, '$[0]')) STORED,
ADD INDEX idx_main_category (main_category);

A Importância da Validação de Dados

Como os dados JSON são flexíveis, também é mais fácil armazenar dados no formato errado. Para manter a integridade dos dados, use as abordagens a seguir.

1. Use Restrições CHECK

No MySQL 8.0 e posteriores, você pode validar a estrutura e o conteúdo JSON usando restrições CHECK.

ALTER TABLE products_with_prices
ADD CONSTRAINT check_price CHECK (JSON_EXTRACT(details, '$.price') >= 0);

2. Validação no Nível da Aplicação

Ao inserir dados, recomenda-se validar o formato JSON no lado da aplicação. Linguagens de programação como PHP e Python podem validar JSON usando suas bibliotecas padrão.

7. Perguntas Frequentes Sobre o Uso de Arrays no MySQL

Q1: O MySQL tem um tipo de dado array?

A1:MySQL não possui um “tipo de dado array” direto. No entanto, usando o tipo JSON, você pode lidar com estruturas de dados semelhantes a arrays. Com o tipo JSON, você pode armazenar múltiplos valores em uma coluna e manipulá-los por meio de consultas.

Example:

SELECT JSON_ARRAY('Value 1', 'Value 2', 'Value 3') AS array_example;

Resultado:

["Value 1", "Value 2", "Value 3"]

Q2: É possível criar um índice em dados JSON?

A2:Não é possível criar um índice diretamente no tipo JSON. No entanto, você pode extrair uma chave ou valor específico para uma coluna gerada e criar um índice nessa coluna gerada.

Example:

ALTER TABLE products_with_prices
ADD COLUMN price INT AS (JSON_EXTRACT(details, '$.price')) STORED,
ADD INDEX idx_price (price);

Isso permite que você pesquise valores dentro de dados JSON de forma eficiente.

Q3: Existe um limite de tamanho para dados JSON?

A3:O tipo JSON do MySQL pode armazenar até 4 GB de dados. Contudo, usar documentos JSON extremamente grandes pode reduzir o desempenho, portanto você deve projetar seus dados com cuidado.

Recomendações:

• Armazene apenas os dados mínimos necessários.
• Evite estruturas JSON profundamente aninhadas.

Q4: Como atualizar um elemento específico dentro de um array JSON?

A4:Você pode atualizar um elemento específico em um array usando a função JSON_SET.

Example:

SET @tags = '["PHP", "MySQL", "JavaScript"]';
SELECT JSON_SET(@tags, '$[1]', 'Python') AS updated_tags;

Resultado:

["PHP", "Python", "JavaScript"]

Q5: Comparando o Tipo JSON vs. Design de Tabela Normal

A5:Embora o tipo JSON seja altamente flexível, ele possui características diferentes em comparação ao design tradicional de bancos de dados relacionais.

8. Resumo

Benefícios de Usar o Tipo de Dados JSON para Operações de Array no MySQL

Neste artigo, explicamos o tipo de dados JSON, que é útil ao trabalhar com dados semelhantes a arrays no MySQL. Abaixo está um resumo dos pontos principais abordados:

1. Por que usar o tipo JSON O MySQL não possui um tipo de array direto, mas ao usar o tipo JSON, você pode armazenar múltiplos valores em uma coluna e obter manipulação flexível de dados.
2. Operações Básicas com JSON

• Abordamos como criar arrays JSON, extrair dados, atualizar dados e remover dados.
• Usando funções convenientes como JSON_ARRAY, JSON_EXTRACT e JSON_SET, você pode manipular dados de array de forma eficiente.

1. Busca e Filtragem

• Como buscar dados que contenham valores específicos usando JSON_CONTAINS.
• Como obter o número de elementos com JSON_LENGTH e realizar filtragem condicional.

1. Exemplos Práticos Por meio de casos de uso reais, como gerenciamento de categorias de produtos e filtragem por preço, aprendemos maneiras concretas de aplicar arrays JSON em aplicações.
2. Considerações e Otimização

• Explicamos como configurar indexação usando colunas geradas e enfatizamos a importância de validar dados JSON.

Próximos Passos ao Usar o Tipo JSON

Ao usar o tipo JSON no MySQL, você pode gerenciar dados de forma mais flexível que em designs de bancos de dados relacionais tradicionais. Contudo, boas considerações de design e desempenho são essenciais.

Tópicos para Aprender a Seguir:

• Usando Índices Compostos Design de índice que combina dados JSON com colunas normais.
• Usando Funções JSON Avançadas Realizar operações mais complexas com funções como JSON_MERGE e JSON_OBJECT.
• Manipulação de Dados no Nível da Aplicação Como manipular eficientemente dados JSON do MySQL usando PHP ou Python.

Resumo

Através deste artigo, você agora deve entender como lidar eficientemente com dados semelhantes a arrays usando o tipo de dados JSON do MySQL. Ao aplicar esse conhecimento, você pode projetar bancos de dados mais flexíveis e escaláveis.