Como processar e converter dados de array de forma eficiente no SQL

Nos bancos de dados SQL, a manipulação de dados relacionais é predominante, mas o tratamento de dados em formato de array tem se tornado cada vez mais comum. Especialmente quando se lida com dados em formatos como JSON ou XML, é necessário um processamento eficiente de arrays. Neste artigo, vamos detalhar métodos específicos e exemplos de consultas para processar e converter dados de array de maneira eficiente em SQL.

Conceitos básicos de dados de array e como manipulá-los no SQL

Dados de array referem-se a uma estrutura de dados onde elementos do mesmo tipo são organizados em uma ordem específica. Embora as funcionalidades para lidar diretamente com arrays no SQL sejam limitadas, os bancos de dados SQL modernos permitem o tratamento de dados de array utilizando formatos como JSON ou XML.

Exemplo de dados de array

Por exemplo, aqui está um dado de array em formato JSON:

[
  {"id": 1, "name": "Alice"},
  {"id": 2, "name": "Bob"},
  {"id": 3, "name": "Charlie"}
]

Tratamento básico de dados de array no SQL

No SQL, utiliza-se tipos como JSON ou XML para armazenar dados de array em tabelas. Bancos de dados como PostgreSQL e MySQL suportam o tipo JSON, que pode ser utilizado para armazenar e manipular dados de array. Abaixo, segue um exemplo de como armazenar dados de array em uma tabela utilizando o tipo JSON no PostgreSQL.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  data JSONB
);

INSERT INTO users (data) VALUES
('[
  {"id": 1, "name": "Alice"},
  {"id": 2, "name": "Bob"},
  {"id": 3, "name": "Charlie"}
]');

Como converter arrays para formato tabular

Converter dados de array para formato tabular facilita a manipulação dos dados com consultas SQL normais. Aqui, vamos apresentar como converter dados de array JSON para formato tabular usando PostgreSQL.

Expandindo arrays em linhas individuais

Com a função jsonb_array_elements do PostgreSQL, você pode expandir um array JSON em linhas individuais.

SELECT jsonb_array_elements('[{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}, {"id": 3, "name": "Charlie"}]') AS element;

Essa consulta retorna o seguinte resultado:

 element
-----------------------------
 {"id": 1, "name": "Alice"}
 {"id": 2, "name": "Bob"}
 {"id": 3, "name": "Charlie"}

Convertendo dados de array expandidos em uma tabela

Após expandir os dados de array em linhas individuais, você pode converter cada elemento em colunas de uma tabela.

SELECT
  element->>'id' AS id,
  element->>'name' AS name
FROM
  jsonb_array_elements('[{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}, {"id": 3, "name": "Charlie"}]') AS element;

Essa consulta retorna o seguinte resultado:

 id |  name
----+---------
 1  | Alice
 2  | Bob
 3  | Charlie

Inserção em uma tabela

Também é possível inserir os dados expandidos em uma nova tabela.

CREATE TABLE users (
  id INT,
  name TEXT
);

INSERT INTO users (id, name)
SELECT
  (element->>'id')::INT,
  element->>'name'
FROM
  jsonb_array_elements('[{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}, {"id": 3, "name": "Charlie"}]') AS element;

Isso converte os dados de array em uma tabela relacional normal, permitindo operações SQL padrão.

Processamento de arrays utilizando dados JSON

Utilizando dados JSON, o processamento de arrays no SQL torna-se mais flexível e eficiente. Aqui, vamos demonstrar métodos para processar arrays usando dados JSON no PostgreSQL.

Inserção e consulta de dados JSON

Primeiro, vejamos como inserir dados JSON em uma tabela e consultá-los para obter as informações necessárias.

Criação da tabela e inserção de dados JSON

Use os seguintes comandos SQL para criar uma tabela que contenha dados JSON e inserir os dados:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  data JSONB
);

INSERT INTO users (data) VALUES
('{"users": [{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}, {"id": 3, "name": "Charlie"}]}');

Consulta de dados JSON

Veja como consultar os dados JSON inseridos para obter as informações necessárias. Por exemplo, para obter o nome de um usuário:

SELECT
  data->'users'->0->>'name' AS first_user_name
FROM
  users;

Esta consulta obtém o nome do primeiro usuário no array.

Acessando cada elemento do array

Para acessar e manipular cada elemento do array, utilize a função jsonb_array_elements.

SELECT
  jsonb_array_elements(data->'users') AS user
FROM
  users;

Essa consulta retorna cada usuário como uma linha separada.

Acessando propriedades de cada elemento

Veja como acessar as propriedades de cada elemento expandido.

SELECT
  user->>'id' AS id,
  user->>'name' AS name
FROM
  users,
  jsonb_array_elements(data->'users') AS user;

Essa consulta obtém o ID e o nome de cada usuário.

Uso de funções JSON

O PostgreSQL oferece diversas funções para manipular dados JSON. Por exemplo, para obter o tamanho de um array, utilize a função jsonb_array_length.

SELECT
  jsonb_array_length(data->'users') AS number_of_users
FROM
  users;

Esta consulta retorna o tamanho do array de usuários.

Ao utilizar dados JSON, é possível realizar operações flexíveis com arrays, tornando o manuseio de dados mais eficiente.

Junção e filtragem de dados de array

Juntando e filtrando dados de array, você pode extrair informações relevantes com mais facilidade. Aqui, vamos demonstrar como juntar e filtrar dados de array usando PostgreSQL.

Junção de dados de array

Ao juntar vários arrays JSON, é possível combinar informações relacionadas de arrays diferentes. A consulta a seguir mostra como juntar vários arrays JSON.

Exemplo: Junção de dados de usuários e pedidos

Primeiro, crie uma tabela com dados de usuários e outra com dados de pedidos, inserindo os respectivos dados JSON.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  data JSONB
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  data JSONB
);

INSERT INTO users (data) VALUES
('{"users": [{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}, {"id": 3, "name": "Charlie"}]}');

INSERT INTO orders (data) VALUES
('{"orders": [{"user_id": 1, "product": "Laptop"}, {"user_id": 2, "product": "Tablet"}, {"user_id": 3, "product": "Smartphone"}]}');

Em seguida, execute a consulta para juntar os dados de usuários e pedidos.

SELECT
  u.user->>'name' AS user_name,
  o.order->>'product' AS product
FROM
  (SELECT jsonb_array_elements(data->'users') AS user FROM users) AS u,
  (SELECT jsonb_array_elements(data->'orders') AS order FROM orders) AS o
WHERE
  u.user->>'id' = o.order->>'user_id';

Esta consulta junta cada usuário ao seu respectivo pedido.

Filtragem de dados de array

Agora, vamos explicar como filtrar dados de array para extrair apenas os elementos que correspondem a critérios específicos.

Exemplo: Filtrando um usuário específico

A consulta a seguir filtra e obtém o usuário cujo nome é “Alice”.

SELECT
  user->>'id' AS id,
  user

->>'name' AS name
FROM
  users,
  jsonb_array_elements(data->'users') AS user
WHERE
  user->>'name' = 'Alice';

Esta consulta retorna apenas o usuário cujo nome é “Alice”.

Exemplo: Filtrando usuários que compraram um produto específico

A consulta a seguir filtra e obtém os usuários que compraram um “Laptop”.

SELECT
  u.user->>'name' AS user_name,
  o.order->>'product' AS product
FROM
  (SELECT jsonb_array_elements(data->'users') AS user FROM users) AS u,
  (SELECT jsonb_array_elements(data->'orders') AS order FROM orders) AS o
WHERE
  u.user->>'id' = o.order->>'user_id' AND
  o.order->>'product' = 'Laptop';

Esta consulta retorna os usuários que compraram um “Laptop” e as informações do pedido correspondente.

Ao utilizar junção e filtragem de dados de array, você pode realizar operações complexas de forma eficiente e extrair as informações necessárias com facilidade.

Processamento avançado de arrays utilizando funções de janela

Com o uso de funções de janela, é possível realizar análises e agregações avançadas em dados de array. Aqui, vamos demonstrar como processar dados de array utilizando funções de janela no PostgreSQL.

Noções básicas de funções de janela

Funções de janela são funções que realizam cálculos sobre um conjunto de linhas relacionadas a uma linha atual. Diferente das funções de agregação tradicionais, as funções de janela permitem obter resultados de cálculo sem agrupar as linhas, sendo ideal para análises detalhadas de dados.

Exemplo: Classificação de dados de array

Vamos inserir em uma tabela os dados de array que contêm a pontuação de usuários e classificá-los com base em suas pontuações.

CREATE TABLE user_scores (
  id SERIAL PRIMARY KEY,
  data JSONB
);

INSERT INTO user_scores (data) VALUES
('{"users": [{"id": 1, "name": "Alice", "score": 85}, {"id": 2, "name": "Bob", "score": 90}, {"id": 3, "name": "Charlie", "score": 75}]}');

Em seguida, execute a consulta para calcular a classificação dos usuários com base na pontuação.

SELECT
  user->>'name' AS name,
  user->>'score' AS score,
  RANK() OVER (ORDER BY (user->>'score')::INT DESC) AS rank
FROM
  user_scores,
  jsonb_array_elements(data->'users') AS user;

Esta consulta retorna a classificação dos usuários com base na pontuação em ordem decrescente.

  name   | score | rank
---------+-------+------
  Bob    | 90    | 1
  Alice  | 85    | 2
  Charlie| 75    | 3

Exemplo: Cálculo de média móvel

Também é possível calcular a média móvel de dados de array utilizando funções de janela. No exemplo a seguir, vamos calcular a média móvel dos dados de vendas mensais de usuários contidos em um array.

CREATE TABLE monthly_sales (
  id SERIAL PRIMARY KEY,
  data JSONB
);

INSERT INTO monthly_sales (data) VALUES
('{"sales": [{"month": "January", "amount": 100}, {"month": "February", "amount": 200}, {"month": "March", "amount": 150}, {"month": "April", "amount": 300}]}');

Em seguida, execute a consulta para calcular a média móvel.

SELECT
  sale->>'month' AS month,
  (sale->>'amount')::INT AS amount,
  AVG((sale->>'amount')::INT) OVER (ORDER BY sale->>'month' ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg
FROM
  monthly_sales,
  jsonb_array_elements(data->'sales') AS sale;

Esta consulta retorna as vendas de cada mês e a média móvel correspondente.

  month    | amount | moving_avg
-----------+--------+------------
  January  | 100    | 150
  February | 200    | 150
  March    | 150    | 216.67
  April    | 300    | 225

Com as funções de janela, é possível realizar análises e agregações avançadas de dados de array, proporcionando insights mais profundos.

Otimização de desempenho para dados de array

Para processar dados de array de forma eficiente, a otimização de desempenho é essencial. Aqui, vamos apresentar as melhores práticas e técnicas para otimizar o processamento de dados de array no SQL.

Uso de índices

O uso de índices pode acelerar a busca e manipulação de dados de array. Especialmente ao utilizar o tipo de dado JSONB, é recomendável o uso de índices GIN.

CREATE INDEX idx_users_data ON users USING GIN (data);

Esse índice permite a busca eficiente por campos específicos dentro de dados JSONB.

Remoção de dados desnecessários

Quando dados desnecessários estão presentes em um array, o processamento pode ser mais lento. Extraindo apenas os campos necessários para o processamento, é possível melhorar o desempenho.

SELECT
  user->>'id' AS id,
  user->>'name' AS name
FROM
  users,
  jsonb_array_elements(data->'users') AS user
WHERE
  user->>'id' IS NOT NULL;

Essa consulta extrai apenas os campos necessários e remove dados desnecessários.

Uso de inserções em massa

Quando se trata de inserir grandes quantidades de dados de array, o uso de inserções em massa é eficiente. Isso minimiza o overhead da inserção.

INSERT INTO users (data) VALUES
('{"users": [{"id": 4, "name": "David"}, {"id": 5, "name": "Eva"}]}'),
('{"users": [{"id": 6, "name": "Frank"}, {"id": 7, "name": "Grace"}]}');

Inserindo múltiplos registros de uma só vez, o processo de inserção torna-se mais eficiente.

Execução regular de VACUUM e ANALYZE

No PostgreSQL, a execução regular de VACUUM e ANALYZE ajuda a evitar o inchaço das tabelas e mantém as estatísticas atualizadas.

VACUUM ANALYZE users;

Isso garante que o otimizador de consultas selecione o melhor plano de execução baseado em estatísticas atualizadas.

Escolha do tipo de dado apropriado

A escolha do tipo de dado também influencia o desempenho. Por exemplo, ao trabalhar com dados JSON, o tipo JSONB geralmente oferece melhor desempenho que o tipo JSON, sendo recomendado o uso de JSONB.

Uso de processamento paralelo

Ao lidar com conjuntos de dados grandes, o uso de processamento paralelo pode melhorar o desempenho. No PostgreSQL, é possível utilizar consultas paralelas para aproveitar múltiplos núcleos de CPU.

SET max_parallel_workers_per_gather = 4;

Essa configuração permite a execução paralela de consultas, aumentando a velocidade de processamento.

Ao utilizar essas técnicas de otimização, você pode processar dados de array de maneira mais eficiente e melhorar significativamente o desempenho das consultas SQL.

Conclusão

Para processar e converter dados de array de forma eficiente no SQL, é importante dominar alguns pontos chave. Primeiro, utilize tipos de dados como JSONB para facilitar o manuseio de arrays e use funções como jsonb_array_elements para converter arrays em formato tabular, possibilitando operações SQL padrão. Além disso, o uso de funções de janela, índices, remoção de dados desnecessários, inserções em massa, execução regular de VACUUM e ANALYZE, escolha do tipo de dado apropriado e processamento paralelo são técnicas de otimização que podem maximizar o desempenho. Combinando essas técnicas, é possível realizar um processamento eficiente de dados de array.