UNNEST in PostgreSQL: Expand an Array into Rows, WITH ORDINALITY and ARRAY_AGG

UNNEST takes an array and expands it into a set of rows, one row per element. It is the workhorse for tags, lists, batches of identifiers, and any data that lives inside an array when you need to treat it like an ordinary table.

Basic array expansion

UNNEST is a table function: it belongs in FROM, not in the select list. The simplest case is a literal array.

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

More often the array sits in a column. Say articles has tags text[]. Comma-expanding it in FROM is an implicit LATERAL join: each articles row is multiplied by the number of its tags.

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

Worth knowing:

• An empty array or NULL yields zero rows — the original articles row simply drops out of the result.
• To keep it, use LEFT JOIN LATERAL ... ON true.
• The output of UNNEST has no guaranteed order on its own; if order matters, make it explicit with WITH ORDINALITY.

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

WITH ORDINALITY: the element index

Often you want the element and its position. WITH ORDINALITY adds a counter column that starts at 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;

You name the ordinal column in the alias list t(value, ordinal) — here it is pos. This is the only reliable way to recover the original element order: never lean on the "natural" row order.

Several arrays in parallel

When a row carries two arrays of the same length (item names and their prices, say), unnest them in lockstep with the multi-argument UNNEST(a, b). PostgreSQL zips the arrays by position.

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

Gotcha: when the arrays differ in length, PostgreSQL pads the shorter one with NULL up to the longest, instead of truncating. If you expect strict pairing, check cardinality(a) = cardinality(b) up front, or you will get surprise NULLs.

Unnesting tags and joining

Expanded elements are plain rows: you can filter, group, and join them. Counting the most popular tags becomes a GROUP BY on the unnested column:

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

To relate the expanded values to another table, use an ordinary JOIN. Suppose orders.status_labels holds an array of codes and there is a lookup table:

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

The inverse: ARRAY_AGG

UNNEST and ARRAY_AGG are mirror operations. UNNEST spreads an array into rows; ARRAY_AGG folds rows back into an array. A handy pattern is to flatten, work on the rows, then collapse the result:

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 and ORDER BY inside ARRAY_AGG give you a predictable, deduplicated array.

MySQL and ClickHouse

• MySQL before 8.0 has no UNNEST. From 8.0 you emulate arrays through JSON and JSON_TABLE, which expands a JSON array into rows. It is more verbose and rides on JSON rather than a native array type.
• ClickHouse uses arrayJoin(arr), the direct counterpart of UNNEST. It multiplies the row by the array's elements:

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

For an index ClickHouse has arrayEnumerate, and the inverse fold is groupArray, its ARRAY_AGG. The mapping is easy to keep: UNNEST/ARRAY_AGG in PostgreSQL are arrayJoin/groupArray in ClickHouse.