ARRAY_AGG en PostgreSQL: agrupar valores en un array con ORDER BY y FILTER

ARRAY_AGG es una funcion de agregacion que reune los valores de todas las filas de un grupo en un solo array. Convierte "muchas filas por clave" en "una fila con un array", util para el historial de una entidad, listas de etiquetas o una exportacion compacta sin una segunda consulta.

Sintaxis y ejemplo basico

La firma coincide con cualquier agregado: ARRAY_AGG(expression), normalmente junto a un GROUP BY. Dentro de los parentesis puedes anadir ORDER BY y FILTER, y eso es justo lo que distingue una salida legible de un orden aleatorio.

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

Algunos comportamientos que conviene conocer:

• El orden de los elementos no esta garantizado sin un ORDER BY dentro del agregado. Nunca dependas del orden de las filas en la tabla.
• El tipo del resultado es un array del tipo de la expresion: numeric[], text[], etc.
• Los valores NULL tambien entran en el array. Si no los quieres, anade FILTER (WHERE expr IS NOT NULL).
• Un grupo vacio produce NULL, no un array vacio {}.

ORDER BY y FILTER dentro del agregado

Lo mas valioso es ordenar y filtrar dentro de la propia llamada. Asi se construye, por ejemplo, un historial cronologico de importes de pedidos, limitado a los pagados:

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 ...) elimina duplicados, ideal para un conjunto de paises o estados:

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

Trampa: DISTINCT y ORDER BY conviven en un mismo ARRAY_AGG solo si la expresion del ORDER BY coincide con la agregada. ARRAY_AGG(DISTINCT amount ORDER BY created_at) falla, porque no puedes ordenar por una columna que no esta en la proyeccion tras DISTINCT. Ordena por la misma columna: ARRAY_AGG(DISTINCT amount ORDER BY amount).

Historial por fila en una sola consulta

Una necesidad habitual es un informe compacto de "usuario, lista de su actividad". ARRAY_AGG lo arma sin subconsultas ni codigo de aplicacion:

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 una jerarquia de empleados puedes reunir todos los subordinados directos de cada jefe:

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

Volver del array a las filas

La operacion inversa de ARRAY_AGG es UNNEST, que expande un array en filas. Usala para recorrer los elementos o para unir con otra tabla:

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);

El par ARRAY_AGG mas UNNEST es un ciclo completo: pliega en un array, procesa y desplega de vuelta.

MySQL: sin arrays, solo JSON

MySQL no tiene un tipo array real, asi que tampoco hay un ARRAY_AGG directo. Lo mas cercano es JSON_ARRAYAGG, que reune valores en un array JSON:

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

Diferencias que conviene recordar:

• JSON_ARRAYAGG no tiene un ORDER BY interno. Para imponer un orden, agrega sobre una subconsulta ya ordenada.
• Para una simple union en texto existe GROUP_CONCAT(amount ORDER BY created_at SEPARATOR ','), que es texto, no un array.
• En ClickHouse el equivalente es groupArray(amount), con groupArraySorted o arraySort(groupArray(...)) para un resultado ordenado.

Cuando de verdad necesitas un array con orden predecible y filtrado, PostgreSQL con ARRAY_AGG ... ORDER BY ... FILTER sigue siendo la herramienta mas directa; en MySQL, planifica desde el principio el JSON y la ordenacion a nivel de subconsulta.