UNNEST en PostgreSQL: expandir un array en filas, WITH ORDINALITY y ARRAY_AGG

UNNEST toma un array y lo expande en un conjunto de filas, una fila por elemento. Es el caballo de batalla para etiquetas, listas, lotes de identificadores y cualquier dato que viva dentro de un array cuando necesitas tratarlo como una tabla normal.

Expansion basica de un array

UNNEST es una funcion de tabla: va en el FROM, no en la lista de seleccion. El caso mas simple es un array literal.

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

Lo habitual es que el array este en una columna. Supongamos que articles tiene tags text[]. Expandirlo con coma en el FROM es un join LATERAL implicito: cada fila de articles se multiplica por el numero de sus etiquetas.

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

Conviene saber:

• Un array vacio o NULL produce cero filas: la fila original de articles simplemente desaparece del resultado.
• Para conservarla, usa LEFT JOIN LATERAL ... ON true.
• La salida de UNNEST no tiene orden garantizado por si sola; si el orden importa, hazlo explicito con WITH ORDINALITY.

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

WITH ORDINALITY: el indice del elemento

A menudo quieres el elemento y su posicion. WITH ORDINALITY agrega una columna contador que empieza en 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;

La columna de ordinal se nombra en la lista de alias t(value, ordinal); aqui es pos. Es la unica forma fiable de recuperar el orden original de los elementos: nunca confies en el orden "natural" de las filas.

Varios arrays en paralelo

Cuando una fila lleva dos arrays de la misma longitud (nombres de productos y sus precios, por ejemplo), se desanidan al unisono con el UNNEST(a, b) de varios argumentos. PostgreSQL combina los arrays por posicion.

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

Trampa: cuando los arrays tienen longitudes distintas, PostgreSQL rellena el mas corto con NULL hasta el mas largo, en lugar de truncar. Si esperas un emparejamiento estricto, comprueba cardinality(a) = cardinality(b) de antemano, o te llevaras NULL inesperados.

Desanidar etiquetas y hacer join

Los elementos expandidos son filas normales: puedes filtrarlos, agruparlos y unirlos. Contar las etiquetas mas populares se reduce a un GROUP BY sobre la columna desanidada:

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 los valores expandidos con otra tabla, usa un JOIN normal. Supongamos que orders.status_labels guarda un array de codigos y existe una tabla 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;

La operacion inversa: ARRAY_AGG

UNNEST y ARRAY_AGG son operaciones espejo. UNNEST despliega un array en filas; ARRAY_AGG pliega filas de vuelta en un array. Un patron util es aplanar, trabajar con las filas y luego colapsar el 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 y ORDER BY dentro de ARRAY_AGG te dan un array predecible y sin duplicados.

MySQL y ClickHouse

• MySQL anterior a 8.0 no tiene UNNEST. Desde 8.0 se emulan arrays mediante JSON y JSON_TABLE, que expande un array JSON en filas. Es mas verboso y se apoya en JSON en lugar de un tipo de array nativo.
• ClickHouse usa arrayJoin(arr), el equivalente directo de UNNEST. Multiplica la fila por los elementos del array:

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

Para un indice, ClickHouse tiene arrayEnumerate, y el plegado inverso es groupArray, su ARRAY_AGG. El mapeo es facil de recordar: UNNEST/ARRAY_AGG en PostgreSQL son arrayJoin/groupArray en ClickHouse.