Как развернуть массив (UNNEST) в SQL

Зачем это знать

В современных event-таблицах данные часто лежат не одним значением на строку, а массивом: tags = ['sql', 'python'], items_bought = [{id:1}, {id:2}]. Хранить их так удобно и компактно, но как только нужно посчитать «сколько раз встречался тег sql» или «средний чек по товару», массив приходится развернуть — превратить одну строку с массивом из N элементов в N отдельных строк. Именно это делает UNNEST.

В BigQuery и ClickHouse массивы — обыденность, там без разворота массива не построить почти ни один аналитический запрос. В Postgres они встречаются реже, но тоже регулярно: теги, список категорий, история действий. На собеседованиях уровня middle и выше умение развернуть массив спрашивают почти всегда — часто прямо в живой задаче на SQL.

UNNEST в Postgres

Самый простой случай — развернуть литеральный массив:

SELECT unnest(ARRAY[1, 2, 3]) AS n;

Запрос вернёт три строки: 1, 2 и 3. Каждый элемент массива стал отдельной строкой результата.

Чаще массив лежит в колонке таблицы. Тогда unnest ставят в FROM рядом с таблицей — это неявный CROSS JOIN, который для каждой строки разворачивает её массив:

SELECT user_id, tag
FROM users, unnest(tags) AS tag;

Здесь для каждого пользователя вернётся столько строк, сколько тегов у него в массиве tags. Если у пользователя три тега — получим три строки с одним и тем же user_id и разными tag.

UNNEST в BigQuery

В BigQuery синтаксис почти такой же — массив разворачивается через UNNEST в FROM:

SELECT user_id, tag
FROM users, UNNEST(tags) AS tag;

Более явный и читаемый вариант — через CROSS JOIN. Он делает ровно то же самое, но сразу видно, что это соединение:

SELECT user_id, tag
FROM users
CROSS JOIN UNNEST(tags) AS tag;

arrayJoin в ClickHouse

В ClickHouse аналог UNNEST называется arrayJoin. Он размножает строку по числу элементов массива:

SELECT user_id, arrayJoin(tags) AS tag
FROM users;

Работает как UNNEST, но пишется как обычная функция в SELECT, а не в FROM. Есть и функциональный вариант через ARRAY JOIN (отдельная секция запроса) — он полезен, когда нужно развернуть сразу несколько массивов параллельно.

С индексом элемента

Иногда нужно знать не только значение, но и позицию элемента в массиве — например, чтобы восстановить порядок шагов воронки. В Postgres для этого есть WITH ORDINALITY:

SELECT user_id, tag, idx
FROM users, unnest(tags) WITH ORDINALITY AS t(tag, idx);

idx вернёт номер элемента, начиная с 1. В BigQuery та же идея реализована через WITH OFFSET, только нумерация идёт с 0:

SELECT user_id, tag, idx
FROM users, UNNEST(tags) AS tag WITH OFFSET idx;

Массив внутри массива

Если элемент — сам массив (двумерная структура), разворачивают в два прохода: сначала внешний массив, потом внутренний:

SELECT inner_val
FROM t, unnest(outer_array) AS arr, unnest(arr) AS inner_val;

Первый unnest даёт по строке на каждый внутренний массив, второй разворачивает уже его элементы.

JSON-массивы

Данные нередко приходят не типизированным массивом, а JSON. Для него — свои функции. В Postgres это jsonb_array_elements:

SELECT user_id, elem
FROM t, jsonb_array_elements(items) AS elem;

В BigQuery JSON-массив сначала превращают в массив значений через JSON_EXTRACT_ARRAY, а потом разворачивают привычным UNNEST:

SELECT user_id, elem
FROM t, UNNEST(JSON_EXTRACT_ARRAY(items)) AS elem;

Агрегация после разворота

Типичная задача — посчитать частоту элементов. Разворачиваем массив и группируем по значению:

SELECT tag, COUNT(*) AS cnt
FROM users, unnest(tags) AS tag
GROUP BY tag
ORDER BY cnt DESC;

Так получаем топ тегов по популярности — это самый частый практический сценарий и почти дословно повторяет задачи с собесов.

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

Разворот массива — это одна сторона медали, сборка обратно — другая. array_agg собирает строки в массив, и с ORDER BY внутри сохраняет нужный порядок:

SELECT user_id, array_agg(action ORDER BY ts) AS actions
FROM events
GROUP BY user_id;

Так из построчного лога событий получают массив действий на пользователя — удобно для хранения и последующего анализа последовательностей.

LATERAL для разворота

Когда массив, который разворачиваем, зависит от полей той же строки, в Postgres корректнее использовать явный LATERAL:

SELECT u.*, t.tag
FROM users u,
     LATERAL unnest(u.tags) AS t(tag);

LATERAL разрешает функции справа ссылаться на колонки таблицы слева. Без него в более сложных запросах Postgres может выдать ошибку о том, что колонка u.tags недоступна в этом контексте.

Где применяется

Теги и категории

Если нужно не проанализировать все элементы, а просто найти строки с конкретным значением, разворачивать массив не обязательно. Проверка вхождения через ANY быстрее и её можно ускорить индексом:

SELECT * FROM users
WHERE 'sql' = ANY(tags);

Это быстрее, чем unnest с последующим фильтром, потому что не размножает строки. Разворот нужен именно тогда, когда важны все элементы (агрегация, джойны по элементам), а не факт наличия одного.

Массивы событий

В event-ориентированном хранении часто держат массив действий пользователя в одной строке. Чтобы анализировать действия по отдельности (считать переходы, строить воронки), массив разворачивают в строки.

JSON-путь

Если нужен конкретный элемент по индексу, разворот избыточен — достаточно обращения по пути:

SELECT data->'items'->>0 FROM logs;  -- первый элемент массива items

Но как только анализ идёт по всем элементам сразу, возвращаемся к развороту.

Производительность

UNNEST на больших массивах — операция недешёвая: она кратно увеличивает число строк, а без индексов ещё и заставляет сканировать всё целиком. Если разворот массива нужен в каждом втором запросе, это сигнал, что структура таблицы выбрана неудачно — возможно, стоило хранить элементы отдельными строками, а не массивом.

Как это спрашивают на собесе

Классическая формулировка: «Есть таблица users с колонкой tags типа array. Найди топ-10 самых популярных тегов». Интервьюер смотрит, догадаетесь ли вы развернуть массив и сгруппировать по элементу, а не пытаться считать что-то по массиву целиком.

SELECT tag, COUNT(*) AS cnt
FROM users, unnest(tags) AS tag
GROUP BY tag
ORDER BY cnt DESC
LIMIT 10;

Частый уточняющий вопрос — про строки без тегов: если массив пустой или NULL, обычный unnest в FROM просто выкинет такую строку из результата (пустой разворот отсекается CROSS JOIN'ом). Если пользователей без тегов терять нельзя, используют LEFT JOIN LATERAL unnest(tags) ... ON true.

Связанные темы

• 50 вопросов по SQL
• Задачи SQL на собесе

FAQ

Есть ли UNNEST в MySQL?

Нативной функции UNNEST в MySQL нет. Начиная с версии 8.0 массив внутри JSON разворачивают через JSON_TABLE — она превращает JSON-массив в набор строк. До 8.0 приходилось эмулировать разворот через вспомогательную таблицу чисел и джойн.

Что быстрее — WHERE ... = ANY(arr) или UNNEST?

Для проверки «есть ли элемент в массиве» быстрее = ANY(arr): она не размножает строки и в Postgres ускоряется GIN-индексом по массиву. UNNEST нужен, когда требуется анализ всех элементов — агрегация, джойны, подсчёт частот. То есть это разные задачи, а не два способа сделать одно.

Что происходит с NULL при разворотe?

Здесь важно различать два случая. Если сам элемент массива — NULL (массив {1, NULL, 3}), то unnest вернёт его как обычную строку со значением NULL — элемент не пропускается. А вот если NULL целиком вся колонка-массив, то разворот даёт ноль строк, и в неявном CROSS JOIN такая строка исчезает из результата.

Как развернуть массив и не потерять строки без элементов?

Обычный unnest в FROM работает как CROSS JOIN и выкидывает строки с пустым или NULL-массивом. Чтобы сохранить их, в Postgres используют LEFT JOIN LATERAL unnest(tags) AS t(tag) ON true — тогда для строки без тегов вернётся одна запись с NULL в колонке тега. В BigQuery аналогично помогает LEFT JOIN UNNEST(...).

Можно ли развернуть сразу несколько массивов параллельно?

Да, но нужно понимать разницу. Если развернуть два массива через обычный unnest в FROM, получится их декартово произведение (все пары). Чтобы развернуть их поэлементно (первый с первым, второй со вторым), в Postgres есть unnest(a, b) с несколькими аргументами, а в ClickHouse — секция ARRAY JOIN a, b, которая идёт по массивам синхронно.

Тренируйте SQL — откройте тренажёр с 1500+ вопросами для собесов.