ARRAY_AGG no PostgreSQL: agrupar valores em um array com ORDER BY e FILTER

ARRAY_AGG e uma funcao de agregacao que reune os valores de todas as linhas de um grupo em um unico array. Ela transforma "muitas linhas por chave" em "uma linha com um array", util para o historico de uma entidade, listas de tags ou uma exportacao compacta sem uma segunda consulta.

Sintaxe e exemplo basico

A assinatura coincide com qualquer agregado: ARRAY_AGG(expression), geralmente ao lado de um GROUP BY. Dentro dos parenteses voce pode adicionar ORDER BY e FILTER, e e justamente isso que separa uma saida legivel de uma ordem aleatoria.

SELECT
    user_id,
    ARRAY_AGG(amount ORDER BY created_at) AS amounts
FROM orders
GROUP BY user_id;

Alguns comportamentos que vale conhecer:

• A ordem dos elementos nao e garantida sem um ORDER BY dentro do agregado. Nunca confie na ordem das linhas da tabela.
• O tipo do resultado e um array do tipo da expressao: numeric[], text[] e assim por diante.
• Valores NULL tambem entram no array. Se nao os quiser, adicione FILTER (WHERE expr IS NOT NULL).
• Um grupo vazio produz NULL, nao um array vazio {}.

ORDER BY e FILTER dentro do agregado

O mais valioso e ordenar e filtrar dentro da propria chamada. Assim se constroi, por exemplo, um historico cronologico de valores de pedidos, limitado aos pagos:

SELECT
    user_id,
    ARRAY_AGG(amount ORDER BY created_at)
        FILTER (WHERE status = 'paid') AS paid_history
FROM orders
GROUP BY user_id;

ARRAY_AGG(DISTINCT ...) remove duplicatas, otimo para um conjunto de paises ou status:

SELECT
    country,
    ARRAY_AGG(DISTINCT status) AS seen_statuses
FROM orders o
JOIN users u ON u.id = o.user_id
GROUP BY country;

Pegadinha: DISTINCT e ORDER BY convivem em um mesmo ARRAY_AGG apenas se a expressao do ORDER BY coincidir com a agregada. ARRAY_AGG(DISTINCT amount ORDER BY created_at) falha, porque voce nao pode ordenar por uma coluna que nao esta na projecao apos o DISTINCT. Ordene pela mesma coluna: ARRAY_AGG(DISTINCT amount ORDER BY amount).

Historico por linha em uma unica consulta

Uma necessidade comum e um relatorio compacto de "usuario, lista da sua atividade". ARRAY_AGG monta isso sem subconsultas nem codigo de aplicacao:

SELECT
    u.id,
    u.email,
    ARRAY_AGG(o.id ORDER BY o.created_at DESC) AS order_ids,
    ARRAY_AGG(o.amount ORDER BY o.created_at DESC) AS amounts
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email;

Para uma hierarquia de funcionarios voce pode reunir todos os subordinados diretos de cada gerente:

SELECT
    manager_id,
    ARRAY_AGG(name ORDER BY salary DESC) AS reports
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id;

Voltar do array para as linhas

A operacao inversa do ARRAY_AGG e UNNEST, que expande um array em linhas. Use-a para percorrer os elementos ou para unir com outra tabela:

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

-- with original position via WITH ORDINALITY
SELECT id, t.tag, t.pos
FROM articles a, UNNEST(a.tags) WITH ORDINALITY AS t(tag, pos);

O par ARRAY_AGG mais UNNEST e um ciclo completo: dobra em um array, processa e desdobra de volta.

MySQL: sem arrays, apenas JSON

O MySQL nao tem um tipo array de verdade, entao tambem nao ha um ARRAY_AGG direto. O mais proximo e JSON_ARRAYAGG, que reune valores em um array JSON:

-- MySQL 8: collect amounts into a JSON array
SELECT user_id, JSON_ARRAYAGG(amount) AS amounts
FROM orders
GROUP BY user_id;

Diferencas que vale lembrar:

• JSON_ARRAYAGG nao tem um ORDER BY interno. Para impor uma ordem, agregue sobre uma subconsulta ja ordenada.
• Para uma simples juncao em texto existe GROUP_CONCAT(amount ORDER BY created_at SEPARATOR ','), que e texto, nao um array.
• No ClickHouse o equivalente e groupArray(amount), com groupArraySorted ou arraySort(groupArray(...)) para um resultado ordenado.

Quando voce realmente precisa de um array com ordem previsivel e filtragem, o PostgreSQL com ARRAY_AGG ... ORDER BY ... FILTER continua sendo a ferramenta mais direta; no MySQL, planeje desde o inicio o JSON e a ordenacao no nivel da subconsulta.