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

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

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

В BigQuery и ClickHouse массивы — норма. В Postgres тоже часто встречаются. На собесах middle+ знание UNNEST — обязательно.

UNNEST в Postgres

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

Вернёт 3 строки.

С таблицей:

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

users.tags — массив → развернёт.

UNNEST в BigQuery

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

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

Аналог UNNEST.

С индексом

Если нужен индекс элемента:

Postgres:

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

BigQuery:

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

Массив из массива

Двумерный array → unnest дважды:

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

JSON arrays

Postgres:

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

BigQuery:

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

Аггрегация после UNNEST

Top tags:

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

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

Свернуть обратно в массив:

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

LATERAL для UNNEST

Когда массив зависит от row:

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

Без LATERAL Postgres может ругаться на dependency.

Использование

Теги и categories

«Пользователи с тэгом sql»:

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

Быстрее чем unnest + filter.

Event arrays

В event-based storage часто массив actions в одной строке → unnest для анализа.

JSON path

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

Но для аналитики всех элементов — unnest.

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

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;

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

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

FAQ

UNNEST в MySQL?

Нет нативного. Через JSON_TABLE в 8.0+.

Быстрее ли WHERE ... ANY(arr)?

Да, индексируется через GIN. UNNEST — для анализа всех элементов.

NULL в массиве?

unnest пропускает NULLs? Нет, вернёт как обычный row с NULL.

---

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