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

В PostgreSQL массив — это удобная упаковка нескольких значений в одной колонке или в одном параметре.

Например, у статьи может быть массив тегов:

{sql,postgresql,arrays}

У пользователя может быть массив ролей:

{admin,editor}

А приложение может передать в запрос массив id:

{101,102,103}

Но часто с массивом нужно работать не как с одним значением, а как с обычной таблицей.

Например:

• посчитать, сколько раз встречается каждый тег;
• найти статьи с конкретным тегом;
• присоединить элементы массива к справочнику;
• развернуть список id из приложения и найти соответствующие строки;
• проверить роли пользователя;
• очистить массив, отфильтровать значения и собрать обратно.

Для таких задач в PostgreSQL есть функция UNNEST.

Она берёт массив и возвращает по одной строке на каждый элемент.

Что делает UNNEST простыми словами

Допустим, есть массив:

ARRAY['sql', 'postgresql', 'arrays']

UNNEST превращает его в строки:

tag
----------
sql
postgresql
arrays

Запрос:

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

Результат:

tag
----------
sql
postgresql
arrays

То есть UNNEST делает обратное тому, что делает ARRAY_AGG.

• ARRAY_AGG собирает строки в массив.
• UNNEST разворачивает массив обратно в строки.

Можно запомнить так:

ARRAY_AGG: строки -> массив
UNNEST:    массив -> строки

UNNEST — это табличная функция

UNNEST возвращает не одно значение, а набор строк.

Поэтому нормальное место для UNNEST — секция FROM.

Правильно:

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

Здесь:

UNNEST(ARRAY['sql', 'postgresql', 'arrays'])

создаёт временный набор строк.

А:

AS t(tag)

задаёт псевдонимы:

• t — имя временной таблицы;
• tag — имя колонки с элементом массива.

После этого можно обращаться к tag как к обычной колонке.

Пример: развернуть теги статьи

Допустим, есть таблица articles:

id | title              | tags
---+--------------------+-----------------------------
1  | PostgreSQL arrays  | {sql,postgresql,arrays}
2  | SQL joins          | {sql,joins}
3  | JSON in PostgreSQL | {postgresql,json}

Колонка tags имеет тип text[], то есть массив строк.

Чтобы получить по одной строке на каждый тег, используем UNNEST.

SELECT
  a.id,
  a.title,
  t.tag
FROM articles a
CROSS JOIN LATERAL UNNEST(a.tags) AS t(tag);

Результат:

id | title              | tag
---+--------------------+------------
1  | PostgreSQL arrays  | sql
1  | PostgreSQL arrays  | postgresql
1  | PostgreSQL arrays  | arrays
2  | SQL joins          | sql
2  | SQL joins          | joins
3  | JSON in PostgreSQL | postgresql
3  | JSON in PostgreSQL | json

Что произошло?

Каждая статья размножилась на столько строк, сколько тегов было в массиве.

У первой статьи 3 тега — получилось 3 строки. У второй статьи 2 тега — получилось 2 строки. У третьей статьи 2 тега — получилось 2 строки.

Теперь с тегами можно работать как с обычными строками.

Зачем здесь LATERAL

В запросе выше мы написали:

CROSS JOIN LATERAL UNNEST(a.tags) AS t(tag)

LATERAL означает, что правая часть JOIN может обращаться к колонкам левой части.

То есть UNNEST(a.tags) может использовать a.tags из текущей строки таблицы articles.

Без этой идеи PostgreSQL не смог бы для каждой статьи взять именно её массив тегов.

Можно думать так:

Для каждой строки articles возьми её массив tags и разверни его в строки.

В PostgreSQL часто можно встретить и короткую запись через запятую:

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

Она тоже работает, но явный CROSS JOIN LATERAL обычно читается лучше, особенно для новичка.

Что будет с пустым массивом

Допустим, есть статья без тегов:

id | title        | tags
---+--------------+------
4  | Empty tags   | {}

Если использовать обычный CROSS JOIN LATERAL:

SELECT
  a.id,
  a.title,
  t.tag
FROM articles a
CROSS JOIN LATERAL UNNEST(a.tags) AS t(tag);

статья с пустым массивом не попадёт в результат.

Почему?

Потому что UNNEST('{}'::text[]) возвращает ноль строк.

А если правая часть CROSS JOIN вернула ноль строк, исходная строка тоже исчезает из результата.

То же самое будет, если массив равен NULL: UNNEST(NULL::text[]) тоже не даст элементов.

Это важно понимать.

Пустой массив и NULL при обычном UNNEST дают ноль строк.

Как сохранить строки с пустыми массивами

Если нужно сохранить статью даже без тегов, используйте LEFT JOIN LATERAL.

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

Результат:

id | title              | tag
---+--------------------+------------
1  | PostgreSQL arrays  | sql
1  | PostgreSQL arrays  | postgresql
1  | PostgreSQL arrays  | arrays
2  | SQL joins          | sql
2  | SQL joins          | joins
4  | Empty tags         | NULL

Статья без тегов осталась в результате, но tag для неё равен NULL.

Это очень важный паттерн для отчётов.

Если вы используете обычный CROSS JOIN LATERAL, сущности без элементов исчезают. Если используете LEFT JOIN LATERAL, сущности остаются.

Можно запомнить так:

CROSS JOIN LATERAL UNNEST(...) -- оставить только строки, где есть элементы
LEFT JOIN LATERAL UNNEST(...)  -- сохранить исходные строки даже без элементов

Пример: посчитать популярность тегов

После UNNEST элементы массива становятся обычными строками. Значит, их можно группировать.

Например, хотим узнать, какие теги встречаются чаще всего.

SELECT
  t.tag,
  COUNT(*) AS uses
FROM articles a
CROSS JOIN LATERAL UNNEST(a.tags) AS t(tag)
GROUP BY t.tag
ORDER BY uses DESC;

Результат:

tag        | uses
-----------+-----
sql        | 2
postgresql | 2
arrays     | 1
joins      | 1
json       | 1

Это классический сценарий для UNNEST: массив лежит внутри строки, но нам нужно посчитать элементы как отдельные значения.

Пример: найти статьи с конкретным тегом

Допустим, нужно найти статьи, где среди тегов есть sql.

Через UNNEST можно написать так:

SELECT DISTINCT
  a.id,
  a.title
FROM articles a
CROSS JOIN LATERAL UNNEST(a.tags) AS t(tag)
WHERE t.tag = 'sql';

Результат:

id | title
---+-------------------
1  | PostgreSQL arrays
2  | SQL joins

Почему нужен DISTINCT?

Потому что если в массиве случайно будет один и тот же тег несколько раз, статья может появиться в результате несколько раз.

Например:

{sql,sql,postgresql}

Для поиска по массивам в PostgreSQL есть и специальные операторы, например @>, но UNNEST полезен, когда нужно именно развернуть элементы и дальше работать с ними как со строками.

WITH ORDINALITY: получить позицию элемента

Иногда важен не только сам элемент массива, но и его позиция.

Например, массив тегов:

{sql,postgresql,arrays}

Позиции:

1: sql
2: postgresql
3: arrays

Для этого используется WITH ORDINALITY.

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

Результат:

id | tag        | pos
---+------------+----
1  | sql        | 1
1  | postgresql | 2
1  | arrays     | 3
2  | sql        | 1
2  | joins      | 2

pos — это номер элемента в массиве.

Нумерация начинается с 1.

Это важно: в PostgreSQL массивы обычно индексируются с единицы, а не с нуля.

Почему WITH ORDINALITY лучше, чем надеяться на порядок

UNNEST разворачивает элементы массива в порядке элементов, но в SQL нельзя полагаться на порядок строк результата без ORDER BY.

Если порядок важен, не стоит просто смотреть на то, как строки «сами» вывелись на тестовых данных.

Надёжный вариант:

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

WITH ORDINALITY даёт позицию. ORDER BY t.pos явно фиксирует порядок вывода.

Это особенно важно для массивов, где порядок имеет смысл:

• шаги инструкции;
• этапы процесса;
• приоритеты;
• сортировка тегов;
• выбранные пользователем элементы;
• элементы интерфейса.

Главное правило:

Если порядок элементов важен, используйте WITH ORDINALITY и сортируйте по позиции.

Развернуть массив id из приложения

UNNEST часто используют на границе между приложением и базой.

Например, приложение передаёт массив id:

{101,102,103}

И нужно получить соответствующие заказы.

Можно написать:

SELECT
  o.id,
  o.user_id,
  o.amount,
  o.status
FROM UNNEST(ARRAY[101, 102, 103]) AS ids(order_id)
JOIN orders o ON o.id = ids.order_id;

Результат:

id  | user_id | amount | status
----+---------+--------+--------
101 | 1       | 1500   | paid
102 | 1       | 2300   | paid
103 | 2       | 900    | failed

В реальном приложении вместо литерала ARRAY[101, 102, 103] обычно будет параметр.

Например, условно:

SELECT
  o.id,
  o.user_id,
  o.amount,
  o.status
FROM UNNEST($1::int[]) AS ids(order_id)
JOIN orders o ON o.id = ids.order_id;

Это удобнее и безопаснее, чем собирать динамический SQL со списком IN (...).

Плохая идея:

WHERE id IN (101, 102, 103)

если вы строите этот список строкой в приложении.

Лучше передать массив параметром и развернуть его через UNNEST.

Сохранить порядок id из входного массива

Иногда приложение передало id в конкретном порядке, и результат нужно вернуть в том же порядке.

Например:

{103,101,102}

Если просто сделать JOIN, база не обязана вернуть строки в этом порядке.

Нужен WITH ORDINALITY.

SELECT
  o.id,
  o.user_id,
  o.amount,
  ids.pos
FROM UNNEST(ARRAY[103, 101, 102]) WITH ORDINALITY AS ids(order_id, pos)
JOIN orders o ON o.id = ids.order_id
ORDER BY ids.pos;

Результат:

id  | user_id | amount | pos
----+---------+--------+----
103 | 2       | 900    | 1
101 | 1       | 1500   | 2
102 | 1       | 2300   | 3

Так можно вернуть строки в том же порядке, в котором id пришли из API.

Это очень полезно для задач вроде:

• пользователь отсортировал элементы вручную;
• фронтенд передал порядок карточек;
• нужно сохранить порядок рекомендаций;
• нужно вернуть данные в порядке входного списка.

Несколько массивов параллельно

PostgreSQL умеет разворачивать несколько массивов одновременно.

Например, есть массив товаров и массив цен:

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

Результат:

item     | price
---------+--------
keyboard | 49.90
mouse    | 19.90
monitor  | 199.00

Здесь элементы соединяются по позициям:

keyboard -> 49.90
mouse    -> 19.90
monitor  -> 199.00

Это удобно, когда приложение передало два связанных массива.

Например:

item_ids = {10, 20, 30}
qty      = {1,  2,  5}

Можно развернуть их в таблицу:

SELECT
  item_id,
  qty
FROM UNNEST(
  ARRAY[10, 20, 30],
  ARRAY[1, 2, 5]
) AS x(item_id, qty);

Результат:

item_id | qty
--------+----
10      | 1
20      | 2
30      | 5

Ловушка: массивы разной длины

Если разворачивать несколько массивов разной длины, PostgreSQL не обрежет результат по самому короткому массиву.

Он дополнит недостающие значения NULL до длины самого длинного массива.

Пример:

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

Результат:

item     | price
---------+-------
keyboard | 49.90
mouse    | 19.90
monitor  | NULL

У товара monitor цена стала NULL, потому что во втором массиве не было третьего элемента.

В реальном проекте это может быть опасно.

Например, приложение передало:

product_ids = {10,20,30}
quantities  = {1,2}

И в результате у товара 30 количество станет NULL.

Если массивы должны совпадать строго, проверяйте длину заранее.

Проверка длины массивов через CARDINALITY

Функция CARDINALITY возвращает количество элементов массива.

Пример:

SELECT CARDINALITY(ARRAY[10, 20, 30]) AS len;

Результат:

len
---
3

Если нужно развернуть два массива только при одинаковой длине, можно сделать проверку.

Например:

WITH input AS (
  SELECT
    ARRAY[10, 20, 30] AS item_ids,
    ARRAY[1, 2, 5] AS quantities
)
SELECT
  x.item_id,
  x.qty
FROM input i
CROSS JOIN LATERAL UNNEST(i.item_ids, i.quantities) AS x(item_id, qty)
WHERE CARDINALITY(i.item_ids) = CARDINALITY(i.quantities);

Если длины разные, запрос не вернёт строк.

В реальном приложении такую проверку лучше делать и на стороне приложения, и в SQL, если ошибка критична.

Главное правило:

Если несколько массивов связаны по позициям, проверяйте, что их длины совпадают.

JOIN после UNNEST

После UNNEST элементы массива можно соединять с обычными таблицами.

Допустим, в таблице orders есть массив меток:

id | status_labels
---+-------------------------
1  | {paid,priority}
2  | {refunded,manual_check}

А есть справочник меток label_ref:

code         | title
-------------+-------------------
paid         | Paid order
priority     | Priority order
refunded     | Refunded order
manual_check | Manual check needed

Можно развернуть метки и присоединить справочник:

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

Результат:

id | code         | title
---+--------------+---------------------
1  | paid         | Paid order
1  | priority     | Priority order
2  | refunded     | Refunded order
2  | manual_check | Manual check needed

То есть массив стал обычным набором строк, который можно джойнить.

Это лучше, чем вытаскивать массив в приложение, крутить цикл и делать отдельный запрос за каждой меткой.

Фильтрация элементов массива

После разворота можно фильтровать элементы.

Например, получить только технические теги, которые начинаются с sys_.

SELECT
  a.id,
  t.tag
FROM articles a
CROSS JOIN LATERAL UNNEST(a.tags) AS t(tag)
WHERE t.tag LIKE 'sys_%';

Или убрать пустые элементы:

SELECT
  a.id,
  t.tag
FROM articles a
CROSS JOIN LATERAL UNNEST(a.tags) AS t(tag)
WHERE t.tag IS NOT NULL
  AND t.tag <> '';

Если нужно потом собрать очищенный массив обратно, используйте ARRAY_AGG.

Обратная операция: ARRAY_AGG

UNNEST и ARRAY_AGG часто используются вместе.

Например, есть статьи с массивами тегов. Нужно:

1. развернуть теги;
2. привести их к нижнему регистру;
3. убрать пустые значения;
4. убрать дубликаты;
5. собрать обратно в массив.

SELECT
  a.id,
  ARRAY_AGG(DISTINCT LOWER(t.tag) ORDER BY LOWER(t.tag)) AS clean_tags
FROM articles a
CROSS JOIN LATERAL UNNEST(a.tags) AS t(tag)
WHERE t.tag IS NOT NULL
  AND TRIM(t.tag) <> ''
GROUP BY a.id;

Результат:

id | clean_tags
---+-------------------------
1  | {arrays,postgresql,sql}
2  | {joins,sql}

Но здесь есть важный нюанс: если у статьи нет тегов, она исчезнет из результата, потому что используется CROSS JOIN LATERAL.

Чтобы сохранить все статьи, нужен LEFT JOIN LATERAL.

SELECT
  a.id,
  COALESCE(
    ARRAY_AGG(DISTINCT LOWER(TRIM(t.tag)) ORDER BY LOWER(TRIM(t.tag)))
      FILTER (WHERE t.tag IS NOT NULL AND TRIM(t.tag) <> ''),
    ARRAY[]::text[]
  ) AS clean_tags
FROM articles a
LEFT JOIN LATERAL UNNEST(a.tags) AS t(tag) ON true
GROUP BY a.id;

Теперь статья без тегов получит пустой массив {}, а не исчезнет.

Пример: собрать уникальные теги автора

Допустим, есть пользователи и статьи.

users:

id | email
---+----------------
1  | anna@mail.com
2  | bob@mail.com

articles:

id | author_id | tags
---+-----------+-------------------------
1  | 1         | {sql,postgresql}
2  | 1         | {sql,arrays}
3  | 2         | {mysql,json}

Хотим получить для каждого автора уникальный список тегов.

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

Результат:

id | email         | unique_tags
---+---------------+-------------------------
1  | anna@mail.com | {arrays,postgresql,sql}
2  | bob@mail.com  | {json,mysql}

Здесь:

• UNNEST развернул теги в строки;
• DISTINCT убрал повторы;
• ORDER BY t.tag внутри ARRAY_AGG сделал порядок стабильным;
• ARRAY_AGG собрал строки обратно в массив.

Это хороший пример полного цикла:

массив -> строки -> очистка/агрегация -> массив

UNNEST и ANY: когда что выбрать

Если вам нужно просто проверить, что значение есть в массиве, не всегда нужен UNNEST.

Например, найти статьи с тегом sql можно так:

SELECT
  id,
  title
FROM articles
WHERE 'sql' = ANY(tags);

Это короче, чем разворачивать массив.

Но если нужно:

• посчитать теги;
• соединить теги со справочником;
• получить позицию элемента;
• отфильтровать и собрать обратно;
• сохранить порядок элементов;
• работать с каждым элементом как со строкой,

тогда нужен UNNEST.

Можно запомнить так:

ANY / @>  -- проверить наличие элемента
UNNEST    -- превратить элементы в строки и работать с ними как с таблицей

UNNEST не должен заменять нормальную модель данных

UNNEST удобен, но он не должен становиться оправданием для хранения всего подряд в массивах.

Если элементы массива живут самостоятельной жизнью, им часто нужна отдельная таблица.

Например, теги статьи можно хранить в массиве:

articles(id, title, tags)

Но если по тегам нужно часто искать, считать, связывать со справочником, хранить описания и права доступа, лучше сделать нормальную связующую таблицу:

article_tags(article_id, tag_id)

или:

article_tags(article_id, tag)

Почему отдельная таблица часто лучше?

Потому что её проще:

• индексировать;
• валидировать;
• связывать внешними ключами;
• обновлять;
• чистить;
• анализировать;
• проверять на дубликаты;
• использовать в JOIN.

Массив хорош для компактных списков, параметров запроса и финальной упаковки результата.

Но если вы регулярно разворачиваете одну и ту же колонку через UNNEST, чтобы искать по элементам, возможно, данные стоит нормализовать.

Главная мысль:

Массив удобен как упаковка. Отдельная таблица удобнее как модель данных.

UNNEST в MySQL

В MySQL нет прямого аналога PostgreSQL-массивов и UNNEST.

Обычно похожие задачи решают через JSON.

Например, если в колонке лежит JSON-массив:

["sql", "mysql", "json"]

в MySQL 8 можно использовать JSON_TABLE.

Пример:

SELECT
  a.id,
  jt.tag
FROM articles a,
JSON_TABLE(
  a.tags_json,
  '$[*]' COLUMNS (
    tag VARCHAR(100) PATH '$'
  )
) AS jt;

Это похоже по смыслу на UNNEST, но работает с JSON, а не с нативным массивом PostgreSQL.

То есть в PostgreSQL:

UNNEST(tags)

а в MySQL чаще:

JSON_TABLE(tags_json, ...)

Подход похожий: превратить список внутри одного значения в табличный набор строк.

UNNEST в ClickHouse

В ClickHouse похожую задачу решает arrayJoin.

Например:

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

Если tags содержит:

['sql', 'clickhouse', 'arrays']

результат будет:

id | tag
---+------------
1  | sql
1  | clickhouse
1  | arrays

Обратная операция в ClickHouse — groupArray.

Например:

SELECT
  author_id,
  groupArray(tag) AS tags
FROM article_tags
GROUP BY author_id;

Можно запомнить так:

PostgreSQL: UNNEST / ARRAY_AGG
ClickHouse: arrayJoin / groupArray

Идея одна и та же: развернуть массив в строки или собрать строки обратно в массив.

Практические шаблоны

Развернуть массив-литерал

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

Развернуть массив из колонки

SELECT
  a.id,
  t.tag
FROM articles a
CROSS JOIN LATERAL UNNEST(a.tags) AS t(tag);

Сохранить строки с пустыми массивами

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

Получить позицию элемента

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

Посчитать популярность тегов

SELECT
  t.tag,
  COUNT(*) AS uses
FROM articles a
CROSS JOIN LATERAL UNNEST(a.tags) AS t(tag)
GROUP BY t.tag
ORDER BY uses DESC;

Найти статьи с тегом

SELECT DISTINCT
  a.id,
  a.title
FROM articles a
CROSS JOIN LATERAL UNNEST(a.tags) AS t(tag)
WHERE t.tag = 'sql';

Развернуть массив id из приложения

SELECT
  o.id,
  o.amount,
  o.status
FROM UNNEST($1::int[]) AS ids(order_id)
JOIN orders o ON o.id = ids.order_id;

Сохранить порядок входного массива

SELECT
  o.id,
  o.amount
FROM UNNEST($1::int[]) WITH ORDINALITY AS ids(order_id, pos)
JOIN orders o ON o.id = ids.order_id
ORDER BY ids.pos;

Развернуть два массива параллельно

SELECT
  item_id,
  qty
FROM UNNEST(
  ARRAY[10, 20, 30],
  ARRAY[1, 2, 5]
) AS x(item_id, qty);

Проверить длину массивов

SELECT
  CARDINALITY(ARRAY[10, 20, 30]) AS items_count,
  CARDINALITY(ARRAY[1, 2, 5]) AS quantities_count;

Развернуть и собрать обратно чистый массив

SELECT
  a.id,
  COALESCE(
    ARRAY_AGG(DISTINCT LOWER(TRIM(t.tag)) ORDER BY LOWER(TRIM(t.tag)))
      FILTER (WHERE t.tag IS NOT NULL AND TRIM(t.tag) <> ''),
    ARRAY[]::text[]
  ) AS clean_tags
FROM articles a
LEFT JOIN LATERAL UNNEST(a.tags) AS t(tag) ON true
GROUP BY a.id;

Аналог в ClickHouse

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

Что важно запомнить

UNNEST разворачивает массив в строки.

Пример:

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

Результат:

tag
----------
sql
postgresql
arrays

Главные правила:

• UNNEST лучше использовать в FROM;
• для массива из колонки удобно писать CROSS JOIN LATERAL UNNEST(...);
• пустой массив и NULL дают ноль строк;
• если исходную строку нужно сохранить, используйте LEFT JOIN LATERAL ... ON true;
• для позиции элемента используйте WITH ORDINALITY;
• порядок результата фиксируйте через ORDER BY;
• несколько массивов можно разворачивать параллельно;
• массивы разной длины дополняются NULL;
• для строгих пар проверяйте CARDINALITY;
• после UNNEST элементы можно фильтровать, группировать и соединять через JOIN;
• обратная операция — ARRAY_AGG.

Короткий вывод

UNNEST нужен, когда массив нужно снова превратить в табличные строки.

Например:

SELECT
  a.id,
  t.tag
FROM articles a
CROSS JOIN LATERAL UNNEST(a.tags) AS t(tag);

Так теги статьи становятся обычными строками, которые можно считать, фильтровать и джойнить.

Главная мысль:

UNNEST превращает массив в таблицу.

Это удобно для тегов, ролей, списков id из API, меток, кодов и других компактных наборов значений.

Но если элементы массива часто участвуют в поиске, связях и аналитике, лучше подумать о нормальной дочерней таблице. Массив хорош как упаковка, а UNNEST — как способ временно развернуть эту упаковку обратно в строки.