SQL Academy · Aula

UNNEST e agregação

Transforme matrizes em linhas e vice-versa.

UNNEST e agregação é uma aula grátis de SQL Academy no CoddyKit. Esta é a aula 3 de 4. Você pode ler a aula completa abaixo gratuitamente — depois pratica ao vivo no navegador com um editor de código integrado e um tutor de IA 24/7. Faz parte do caminho de aprendizado de SQL Academy, e seu progresso é sincronizado entre a web e o app CoddyKit. O curso de SQL Academy inclui 4 aulas no total.

O que é UNNEST

O PostgreSQL permite armazenar matrizes dentro de uma única coluna. Mas, às vezes, você precisa trabalhar com cada elemento individualmente — é aí que UNNEST entra em cena.

UNNEST é uma função que retorna um conjunto e expande uma matriz em um conjunto de linhas, uma linha por elemento. Pense nela como o oposto da agregação: em vez de condensar muitas linhas em uma, ela transforma um valor em muitas linhas.

Exemplo básico de UNNEST

O uso mais simples de UNNEST consiste em passar diretamente um literal de matriz. Cada elemento se torna sua própria linha no conjunto de resultados.

Aqui expandimos uma matriz simples de números inteiros em linhas individuais:

SELECT UNNEST(ARRAY[10, 20, 30, 40]) AS value;

UNNEST com matrizes de texto

UNNEST funciona com qualquer tipo de matriz, inclusive texto. Isso é útil quando você tem uma coluna que armazena etiquetas, categorias ou dados no estilo de texto separado por vírgulas, codificados como uma matriz do PostgreSQL.

SELECT UNNEST(ARRAY['apple', 'banana', 'cherry']) AS fruit;

UNNEST a partir de uma coluna de tabela

O verdadeiro poder de UNNEST aparece quando você o usa em uma coluna de uma tabela real. Cada linha da tabela pode ter uma matriz de comprimento diferente, e UNNEST expandirá todas elas em linhas individuais.

Neste exemplo, uma tabela products tem uma coluna de matriz de texto tags:

CREATE TABLE products (
  id   SERIAL PRIMARY KEY,
  name TEXT,
  tags TEXT[]
);

INSERT INTO products (name, tags) VALUES
  ('Laptop',  ARRAY['electronics', 'computing', 'portable']),
  ('Shirt',   ARRAY['clothing', 'casual']),
  ('Blender', ARRAY['kitchen', 'electronics']);

SELECT name, UNNEST(tags) AS tag
FROM products;

Contando ocorrências de etiquetas

Depois de transformar uma coluna de matriz em linhas, você pode tratar os resultados como quaisquer outras linhas e aplicar funções de agregação. Aqui contamos quantos produtos estão associados a cada etiqueta.

O padrão é: UNNEST em uma subconsulta ou CTE e, em seguida, GROUP BY pelo valor transformado em linha:

SELECT tag, COUNT(*) AS product_count
FROM (
  SELECT UNNEST(tags) AS tag
  FROM products
) AS expanded
GROUP BY tag
ORDER BY product_count DESC;

UNNEST com ORDINALITY

Às vezes, a posição de um elemento dentro da matriz é importante. O PostgreSQL fornece WITH ORDINALITY para associar um número de linha a cada elemento expandido, permitindo saber seu índice original na matriz (baseado em 1).

SELECT val, pos
FROM UNNEST(ARRAY['first', 'second', 'third']) WITH ORDINALITY AS t(val, pos);

Usando ORDINALITY em uma tabela

WITH ORDINALITY é especialmente útil quando você armazena listas ordenadas em uma matriz. Por exemplo, uma tabela de lista de reprodução em que a ordem das faixas é importante:

CREATE TABLE playlists (
  id     SERIAL PRIMARY KEY,
  title  TEXT,
  tracks TEXT[]
);

INSERT INTO playlists (title, tracks) VALUES
  ('Morning Mix', ARRAY['Song A', 'Song B', 'Song C']);

SELECT p.title, track, position
FROM playlists p,
     UNNEST(p.tracks) WITH ORDINALITY AS t(track, position)
ORDER BY p.id, position;

Filtrando depois de UNNEST

Como UNNEST transforma elementos de matrizes em linhas, você pode filtrá-los com uma cláusula WHERE comum. Isso permite encontrar todas as linhas cuja matriz contém um valor específico sem usar o operador de matriz que verifica contenção.

SELECT DISTINCT name
FROM products,
     UNNEST(tags) AS tag
WHERE tag = 'electronics';

Agregando novamente em uma matriz

O inverso de UNNEST é ARRAY_AGG. Depois de expandir as linhas e transformá-las ou filtrá-las, você pode coletar os resultados novamente em uma matriz. Esse padrão de ida e volta — transformar em linhas, processar e agregar novamente — é uma prática comum no PostgreSQL.

SELECT ARRAY_AGG(tag ORDER BY tag) AS sorted_tags
FROM (
  SELECT UNNEST(ARRAY['cherry', 'apple', 'banana']) AS tag
) AS t;

Removendo elementos duplicados de uma matriz

Um uso prático do ciclo de desmembramento e agregação é remover elementos duplicados de uma matriz. Aplique UNNEST à matriz, use DISTINCT e depois reúna os elementos com ARRAY_AGG:

SELECT ARRAY_AGG(DISTINCT tag ORDER BY tag) AS unique_tags
FROM UNNEST(ARRAY['sql', 'database', 'sql', 'postgresql', 'database']) AS tag;

Combinando várias colunas de matrizes

Você pode aplicar UNNEST a várias matrizes em paralelo na cláusula FROM. PostgreSQL associa os elementos por posição. Se as matrizes tiverem comprimentos diferentes, a mais curta produzirá valores nulos nas posições excedentes da matriz mais longa.

SELECT key, value
FROM UNNEST(
  ARRAY['name',    'city',      'role'],
  ARRAY['Alice',   'Istanbul',  'DBA']
) AS t(key, value);

Verificação de conhecimentos

Teste sua compreensão de UNNEST e da agregação de matrizes no PostgreSQL.

Recapitulação da lição

Nesta lição, você aprendeu a transformar matrizes em linhas e depois fazer o caminho inverso usando funções integradas do PostgreSQL:

UNNEST — expande uma matriz em uma linha por elemento e funciona tanto com literais quanto com colunas de tabelas.
WITH ORDINALITY — associa um índice posicional a cada elemento desmembrado para que você saiba sua ordem original na matriz.
Filtragem — depois de desmembrar os dados, você pode usar WHERE como faria com quaisquer linhas comuns.
ARRAY_AGG — o inverso de UNNEST; reúne as linhas novamente em uma matriz, opcionalmente com ORDER BY ou DISTINCT.
UNNEST paralelo — várias matrizes na cláusula FROM são expandidas lado a lado e associadas por posição.

Dominar esse padrão de desmembrar, processar e reagregar permite lidar com dados em matrizes usando todo o poder expressivo do SQL.

Perguntas Frequentes

A aula “UNNEST e agregação” é grátis?

Sim — o texto completo de “UNNEST e agregação” é grátis para ler aqui na web. Para praticá-la interativamente (um editor de código integrado e um tutor de IA 24/7) e desbloquear o restante do curso de SQL Academy, atualize para CoddyKit PRO. O curso de SQL Academy inclui 4 aulas no total.

O que vou aprender em “UNNEST e agregação”?

Transforme matrizes em linhas e vice-versa. Você pratica SQL Academy com código prático que executa diretamente no navegador, e um tutor de IA 24/7 responde suas dúvidas enquanto trabalha na aula.

Preciso ter experiência prévia para começar SQL Academy?

Nenhuma experiência prévia é necessária. SQL Academy no CoddyKit é estruturado para alunos iniciantes até avançados, então você pode começar aqui ou desde o início e aprender no seu ritmo. Esta é a aula 3 de 4.

Quanto tempo leva a aula “UNNEST e agregação”?

A maioria das aulas CoddyKit leva cerca de 5–10 minutos. Cada uma é compacta e interativa, então você faz progresso constante e retoma exatamente de onde parou entre web e app.

Posso escrever e executar código nesta aula de SQL Academy?

Sim. Cada aula de SQL Academy inclui um editor de código integrado, então você escreve e executa código real direto no navegador e recebe feedback de IA instantaneamente — nenhuma configuração local necessária.

Todas as aulas deste curso

← Voltar para SQL Academy