UNNEST no PostgreSQL: expandir um array em linhas, WITH ORDINALITY e ARRAY_AGG

UNNEST pega um array e o expande em um conjunto de linhas, uma linha por elemento. E o burro de carga para tags, listas, lotes de identificadores e qualquer dado que vive dentro de um array quando voce precisa trata-lo como uma tabela comum.

Expansao basica de um array

UNNEST e uma funcao de tabela: ela fica no FROM, nao na lista de selecao. O caso mais simples e um array literal.

SELECT tag
FROM UNNEST(ARRAY['sql', 'postgres', 'arrays']) AS tag;

O mais comum e que o array esteja em uma coluna. Suponha que articles tenha tags text[]. Expandi-lo com virgula no FROM e um join LATERAL implicito: cada linha de articles e multiplicada pelo numero de suas tags.

SELECT a.id, tag
FROM articles AS a, UNNEST(a.tags) AS tag;

Vale saber:

• Um array vazio ou NULL produz zero linhas: a linha original de articles simplesmente some do resultado.
• Para mante-la, use LEFT JOIN LATERAL ... ON true.
• A saida de UNNEST nao tem ordem garantida por conta propria; se a ordem importa, torne-a explicita com WITH ORDINALITY.

SELECT a.id, tag
FROM articles AS a
LEFT JOIN LATERAL UNNEST(a.tags) AS tag ON true;

WITH ORDINALITY: o indice do elemento

Muitas vezes voce quer o elemento e a sua posicao. WITH ORDINALITY adiciona uma coluna contadora que comeca em 1.

SELECT a.id, t.tag, t.pos
FROM articles AS a,
     UNNEST(a.tags) WITH ORDINALITY AS t(tag, pos)
ORDER BY a.id, t.pos;

A coluna de ordinal e nomeada na lista de alias t(value, ordinal); aqui ela e pos. E a unica forma confiavel de recuperar a ordem original dos elementos: nunca confie na ordem "natural" das linhas.

Varios arrays em paralelo

Quando uma linha carrega dois arrays do mesmo tamanho (nomes de produtos e seus precos, por exemplo), desaninhe-os em sincronia com o UNNEST(a, b) de varios argumentos. O PostgreSQL combina os arrays por posicao.

SELECT item, price
FROM UNNEST(
       ARRAY['keyboard', 'mouse', 'monitor'],
       ARRAY[49.90, 19.90, 199.00]
     ) AS pair(item, price);

Pegadinha: quando os arrays tem tamanhos diferentes, o PostgreSQL preenche o mais curto com NULL ate o mais longo, em vez de truncar. Se voce espera um pareamento estrito, verifique cardinality(a) = cardinality(b) de antemao, ou recebera NULL inesperados.

Desaninhar tags e fazer join

Os elementos expandidos sao linhas comuns: voce pode filtrar, agrupar e juntar. Contar as tags mais populares se reduz a um GROUP BY sobre a coluna desaninhada:

SELECT tag, COUNT(*) AS uses
FROM articles AS a, UNNEST(a.tags) AS tag
GROUP BY tag
ORDER BY uses DESC
LIMIT 10;

Para relacionar os valores expandidos com outra tabela, use um JOIN comum. Suponha que orders.status_labels guarde um array de codigos e exista uma tabela de referencia:

SELECT o.id, label_ref.title
FROM orders AS o,
     UNNEST(o.status_labels) AS lbl
JOIN label_ref ON label_ref.code = lbl;

A operacao inversa: ARRAY_AGG

UNNEST e ARRAY_AGG sao operacoes espelho. UNNEST espalha um array em linhas; ARRAY_AGG dobra linhas de volta em um array. Um padrao util e achatar, trabalhar nas linhas e depois colapsar o resultado:

SELECT u.id, ARRAY_AGG(DISTINCT tag ORDER BY tag) AS unique_tags
FROM users AS u
JOIN articles AS a ON a.author_id = u.id, UNNEST(a.tags) AS tag
GROUP BY u.id;

DISTINCT e ORDER BY dentro de ARRAY_AGG dao um array previsivel e sem duplicatas.

MySQL e ClickHouse

• MySQL anterior ao 8.0 nao tem UNNEST. A partir do 8.0 voce emula arrays via JSON e JSON_TABLE, que expande um array JSON em linhas. E mais verboso e se apoia em JSON em vez de um tipo de array nativo.
• ClickHouse usa arrayJoin(arr), o equivalente direto de UNNEST. Ele multiplica a linha pelos elementos do array:

-- ClickHouse: one row per tag
SELECT id, arrayJoin(tags) AS tag
FROM articles;

Para um indice, o ClickHouse tem arrayEnumerate, e a dobra inversa e groupArray, o seu ARRAY_AGG. O mapeamento e facil de lembrar: UNNEST/ARRAY_AGG no PostgreSQL sao arrayJoin/groupArray no ClickHouse.