jsonb_array_elements: Expand a JSON Array into Rows in PostgreSQL

jsonb_array_elements раскладывает массив, лежащий в значении JSONB, на набор строк — по строке на каждый элемент. Это JSON-двойник UNNEST, и именно с него начинается любая аналитика по вложенным массивам: теги пользователя, позиции заказа, шаги воронки. Нужна функция тогда, когда массив надо не достать целиком, а разобрать поэлементно — отфильтровать, сгруппировать или сджойнить по отдельным значениям внутри него.

Пока массив остаётся одним скалярным JSONB-значением, SQL не умеет «заглянуть» внутрь: сравнить один его элемент с чем-то или сгруппировать по нему не выйдет. jsonb_array_elements снимает это ограничение — после разворота каждый элемент становится обычной строкой результата, и к нему применим весь привычный арсенал WHERE, GROUP BY и джойнов. Без такого разворота вложенный массив так и останется непрозрачным комком JSON, по которому не построить ни отчёт, ни выборку.

Одна строка на элемент

jsonb_array_elements — функция, возвращающая множество (set-returning). Поставьте её в FROM рядом с таблицей через запятую — и каждый элемент массива превратится в самостоятельную строку. Запятая тут работает как неявный LATERAL: функция видит колонки таблицы, стоящей слева, и подставляет в аргумент значение из текущей строки.

-- users.prefs holds a jsonb array of tags: ["vip", "eu", "beta"]
SELECT u.id, e.value AS tag
FROM users u, jsonb_array_elements(u.prefs -> 'tags') AS e;

Если в prefs -> 'tags' лежит три элемента, на каждого пользователя придётся три строки. Колонка по умолчанию зовётся value, и тип в ней — jsonb. Три свойства, которые стоит держать в голове:

• Возвращает jsonb, поэтому e.value — это "vip" вместе с кавычками, а не голое vip.
• Бережёт порядок элементов массива — первый остаётся первым.
• Пустой массив [] не порождает ни одной строки, и пользователь попросту выпадает из выборки.

Фильтрация и джойн по элементам

Стоит развернуть массив — и его элементы становятся обычными строками: фильтруйте, группируйте, считайте по ним что угодно, как по любой нормальной колонке. Это основной приём всей JSON-аналитики, тот самый запрос в духе «найди всех, у кого в массиве встречается X». После разворота к нему подключаются WHERE, GROUP BY, агрегаты и джойны — словом, весь привычный арсенал.

-- find users whose tags array contains 'vip'
SELECT DISTINCT u.id, u.email
FROM users u, jsonb_array_elements(u.prefs -> 'tags') AS e
WHERE e.value = '"vip"';

Приглядитесь к сравнению с '"vip"': это jsonb-литерал строки, с кавычками внутри кавычек. Внешние кавычки — синтаксис SQL, внутренние — часть самого JSON-значения, и пропустить их легко. Чтобы писать привычное 'vip' без этой акробатики, берите текстовый вариант — о нём ниже. А вот пример посерьёзнее: разворачиваем позиции заказа и считаем выручку по каждому SKU.

-- orders.payload = {"items": [{"sku": "A1", "qty": 2, "price": 10}, ...]}
SELECT
  item ->> 'sku'                                   AS sku,
  sum((item ->> 'qty')::int * (item ->> 'price')::numeric) AS revenue
FROM orders o, jsonb_array_elements(o.payload -> 'items') AS item
WHERE o.status = 'paid'
GROUP BY item ->> 'sku'
ORDER BY revenue DESC;

Один вызов функции — и вложенный массив объектов превращается в полноценную таблицу позиций, по которой GROUP BY отрабатывает без единого костыля.

WITH ORDINALITY: достаём индекс

Порой важна не только сама величина, но и её место: первый тег, третий шаг воронки, порядковый номер строки. Сам по себе массив порядок хранит, но при разворачивании эта информация теряется — на выходе остаются голые значения без меток. WITH ORDINALITY возвращает её: дописывает к результату колонку с номером строки, нумерация — с единицы.

-- keep only the first two tags per user, with their position
SELECT u.id, t.tag, t.pos
FROM users u,
     jsonb_array_elements_text(u.prefs -> 'tags') WITH ORDINALITY AS t(tag, pos)
WHERE t.pos <= 2;

Конструкция t(tag, pos) раздаёт имена: tag — значение, pos — индекс. И вот тонкость, на которой легко споткнуться: эта нумерация начинается с 1, тогда как операторы-стрелки (->, ->>) адресуют массив с нуля. Смешаете два отсчёта в одном запросе — получите сдвиг на единицу и долгий вечер за отладкой.

Вариант _text: без кавычек

jsonb_array_elements_text делает ровно тот же разворот, но отдаёт text, а не jsonb. Это разом снимает мороку с кавычками: элементы сравниваются и выводятся как обычные строки, без двойного экранирования.

-- compare with plain 'vip', no JSON quoting needed
SELECT DISTINCT u.id
FROM users u, jsonb_array_elements_text(u.prefs -> 'tags') AS tag
WHERE tag = 'vip';

Правило выбора короткое. Массив строк или чисел — берите _text и не страдайте с кавычками: ни в сравнениях, ни в выводе они больше не всплывут. Массив объектов, в которые нужно нырять через ->/->>, — оставайтесь на обычном jsonb-варианте, иначе доступ к полям сломается, ведь по text стрелки уже не работают.

Подводные камни

• Пустой массив и NULL молча выбрасывают строку. Функция в FROM ведёт себя как CROSS JOIN: нет элементов — нет строк, и пользователь без тегов бесследно исчезает из результата. Хотите его сохранить — переходите на LEFT JOIN LATERAL ... ON true.
• Не массив — это ошибка, а не пустота. Если в значении вдруг оказался объект, скаляр или NULL, jsonb_array_elements не промолчит, а упадёт с cannot extract elements from a scalar/object. Подстелите соломку: jsonb_array_elements(COALESCE(col -> 'tags', '[]'::jsonb)).
• value против '"value"'. В обычном варианте элемент — это jsonb, поэтому строку сравнивают с '"vip"', а не с 'vip'. Именно эта путаница чаще всего и рождает «загадочно пустой» результат, где запрос вроде верный, а строк нет.

-- keep every user, even those with an empty or missing tags array
SELECT u.id, e.value AS tag
FROM users u
LEFT JOIN LATERAL jsonb_array_elements(COALESCE(u.prefs -> 'tags', '[]'::jsonb)) AS e ON true;

В MySQL прямого аналога до версии 8.0 нет: чтобы развернуть массив в строки, зовите JSON_TABLE(doc, '$.tags[*]' COLUMNS(tag VARCHAR(50) PATH '$')). В ClickHouse опираются на arrayJoin(JSONExtractArrayRaw(col)) либо сразу хранят данные в Array-колонке и натравливают на неё arrayJoin. Идея везде одна и та же — «массив в строки», — но в PostgreSQL jsonb_array_elements остаётся самым коротким путём к цели.