jsonb_array_elements в PostgreSQL: как превратить JSONB-массив в строки

jsonb_array_elements — это функция PostgreSQL, которая берёт массив внутри jsonb и раскладывает его на строки: один элемент массива становится одной строкой результата.

Представьте, что в одной ячейке таблицы лежит не одно значение, а целый список:

["vip", "eu", "beta"]

Пока это один JSON-массив, с ним неудобно работать как с обычной таблицей. Нельзя просто сгруппировать пользователей по каждому тегу или присоединить каждый тег к справочнику. Сначала массив нужно «развернуть».

Вот этим и занимается jsonb_array_elements.

Если коротко: jsonb_array_elements для JSONB-массивов — почти как UNNEST для обычных SQL-массивов. Она превращает вложенный список в нормальные строки, с которыми уже можно работать через WHERE, GROUP BY, JOIN, COUNT, SUM и другие привычные инструменты SQL.

Зачем вообще разворачивать JSONB-массив

Допустим, есть таблица users, а в колонке prefs лежат настройки пользователя:

{
  "tags": ["vip", "eu", "beta"]
}

На первый взгляд всё удобно: один пользователь — одна строка, все теги лежат рядом.

Но как только появляется аналитический вопрос, начинаются сложности:

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

Пока массив лежит внутри одной JSON-ячейки, он похож на закрытую коробку. SQL видит коробку целиком, но для нормальной аналитики нам нужны отдельные элементы.

jsonb_array_elements открывает коробку и выкладывает содержимое на стол: каждый элемент массива становится отдельной строкой.

Базовый пример

Пусть в таблице users есть колонка prefs типа jsonb.

Внутри неё лежит объект с массивом тегов:

{
  "tags": ["vip", "eu", "beta"]
}

Развернём массив тегов:

SELECT
  u.id,
  e.value AS tag
FROM users u,
     jsonb_array_elements(u.prefs -> 'tags') AS e;

Если у пользователя три тега, на выходе получится три строки:

id | tag
---+--------
1  | "vip"
1  | "eu"
1  | "beta"

Здесь происходит важная вещь: одна строка таблицы users превращается в несколько строк результата — по одной на каждый элемент массива.

Почему функция стоит в FROM

jsonb_array_elements возвращает не одно значение, а набор строк. Такие функции в PostgreSQL удобно использовать в секции FROM.

Посмотрите на запрос ещё раз:

SELECT
  u.id,
  e.value AS tag
FROM users u,
     jsonb_array_elements(u.prefs -> 'tags') AS e;

Часть u.prefs -> 'tags' достаёт из JSONB поле tags.

Функция jsonb_array_elements берёт этот массив и возвращает элементы по одному.

Запятая в FROM здесь работает как соединение каждой строки пользователя с результатом функции. В PostgreSQL такая функция может видеть колонки таблицы, которая стоит слева. То есть для каждого пользователя берётся именно его массив тегов.

Более явно это можно записать через CROSS JOIN LATERAL:

SELECT
  u.id,
  e.value AS tag
FROM users u
CROSS JOIN LATERAL jsonb_array_elements(u.prefs -> 'tags') AS e;

Для новичка можно запомнить так:

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

Что лежит в колонке value

По умолчанию результат функции называется value.

SELECT
  e.value
FROM users u,
     jsonb_array_elements(u.prefs -> 'tags') AS e;

Но есть важная тонкость: jsonb_array_elements возвращает элементы типа jsonb.

Поэтому строка из JSON выглядит не как простой текст, а как JSON-строка:

"value"

Например тег vip будет выглядеть так:

"vip"

Это не просто красивое отображение. Для PostgreSQL это именно значение типа jsonb, а не обычный text.

Из-за этого сравнение тоже нужно писать как сравнение с JSONB-строкой:

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

Сначала это выглядит странно: почему внутри одинарных кавычек ещё и двойные?

Потому что внешние кавычки — это синтаксис SQL-строки, а внутренние двойные кавычки — часть JSON-строки.

То есть:

'"vip"'

означает JSON-значение:

"vip"

Если написать просто 'vip', сравнение будет не тем, что вы ожидаете.

jsonb_array_elements_text: вариант без JSON-кавычек

Чтобы не мучиться с кавычками, для массивов строк часто используют соседнюю функцию — jsonb_array_elements_text.

Она делает тот же разворот, но возвращает элементы как обычный text.

SELECT DISTINCT
  u.id,
  u.email
FROM users u,
     jsonb_array_elements_text(u.prefs -> 'tags') AS tag
WHERE tag = 'vip';

Вот здесь уже всё выглядит привычно:

WHERE tag = 'vip'

Без JSON-кавычек внутри строки.

Правило выбора простое:

Что лежит в массиве	Что использовать
строки, числа, простые значения	jsonb_array_elements_text
объекты, из которых нужно доставать поля	jsonb_array_elements

Если массив такой:

["vip", "eu", "beta"]

обычно удобнее использовать jsonb_array_elements_text.

Если массив такой:

[
  {"sku": "A1", "qty": 2, "price": 10},
  {"sku": "B2", "qty": 1, "price": 20}
]

нужен обычный jsonb_array_elements, потому что дальше мы будем обращаться к полям объектов через -> и ->>.

Пример: найти пользователей с тегом vip

Допустим, нужно найти всех пользователей, у которых в массиве тегов есть vip.

Через текстовый вариант это читается очень легко:

SELECT DISTINCT
  u.id,
  u.email
FROM users u,
     jsonb_array_elements_text(u.prefs -> 'tags') AS tag
WHERE tag = 'vip';

Почему здесь нужен DISTINCT?

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

Можно думать об этом запросе так:

1. Берём каждого пользователя.
2. Разворачиваем его массив тегов в строки.
3. Оставляем только строки, где тег равен vip.
4. Показываем уникальных пользователей.

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

Теперь посчитаем, какие теги встречаются чаще всего.

SELECT
  tag,
  COUNT(*) AS users_count
FROM users u,
     jsonb_array_elements_text(u.prefs -> 'tags') AS tag
GROUP BY tag
ORDER BY users_count DESC;

Результат может быть таким:

tag  | users_count
-----+------------
vip  | 154
eu   | 98
beta | 41

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

Пример: позиции заказа внутри JSONB

JSONB-массивы часто встречаются не только в тегах, но и в заказах.

Например, в таблице orders есть колонка payload:

{
  "items": [
    {"sku": "A1", "qty": 2, "price": 10},
    {"sku": "B2", "qty": 1, "price": 20}
  ]
}

Нужно посчитать выручку по каждому товару.

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;

Что здесь происходит:

1. o.payload -> 'items' достаёт массив позиций заказа.
2. jsonb_array_elements превращает каждую позицию в отдельную строку.
3. item ->> 'sku' достаёт артикул товара текстом.
4. item ->> 'qty' достаёт количество.
5. item ->> 'price' достаёт цену.
6. Количество и цена приводятся к числам.
7. SUM считает выручку по каждому sku.

Без разворота массива такой отчёт был бы гораздо сложнее. А после jsonb_array_elements вложенные позиции становятся почти обычной таблицей.

Почему для объектов нужен обычный jsonb_array_elements

В предыдущем примере мы использовали именно jsonb_array_elements, а не jsonb_array_elements_text.

Причина простая: каждый элемент массива — это объект.

{"sku": "A1", "qty": 2, "price": 10}

Чтобы достать из него поля, нужны JSON-операторы:

item ->> 'sku'

Если бы мы использовали jsonb_array_elements_text, элемент стал бы обычным текстом. А по обычному тексту уже нельзя пройти оператором ->> как по JSON-объекту.

Поэтому запомните:

• массив строк — чаще jsonb_array_elements_text;
• массив объектов — чаще jsonb_array_elements.

WITH ORDINALITY: как получить номер элемента

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

Например:

• первый тег пользователя;
• второй шаг воронки;
• порядок товаров в корзине;
• номер действия в сценарии.

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

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;

Результат может быть таким:

id | tag  | pos
---+------+----
1  | vip  | 1
1  | eu   | 2

Конструкция:

WITH ORDINALITY AS t(tag, pos)

говорит PostgreSQL:

• первую колонку назови tag;
• вторую колонку с номером элемента назови pos.

Очень важная деталь: WITH ORDINALITY нумерует элементы с 1.

То есть первый элемент имеет номер 1, второй — 2, третий — 3.

А вот JSON-операторы доступа к массиву работают с нуля:

SELECT
  prefs -> 'tags' -> 0 AS first_tag
FROM users;

Здесь 0 означает первый элемент.

Получается две разные системы отсчёта:

Способ	Первый элемент
WITH ORDINALITY	1
-> 0	0

Если смешать их в одном запросе, легко получить ошибку на один элемент. Это классическая маленькая ловушка, из-за которой отчёт вроде работает, но показывает не те позиции.

Пустой массив: строк не будет

Если в JSONB лежит пустой массив:

[]

jsonb_array_elements не вернёт ни одной строки.

Например:

SELECT
  u.id,
  tag
FROM users u,
     jsonb_array_elements_text(u.prefs -> 'tags') AS tag;

Если у пользователя нет тегов, он просто исчезнет из результата. Это поведение похоже на CROSS JOIN: нет элементов справа — нет строки в итоговой выборке.

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

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

SELECT
  u.id,
  e.value AS tag
FROM users u
LEFT JOIN LATERAL jsonb_array_elements_text(
  COALESCE(u.prefs -> 'tags', '[]'::jsonb)
) AS e(value) ON true;

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

Зачем нужен ON true в LEFT JOIN LATERAL

Конструкция может выглядеть непривычно:

LEFT JOIN LATERAL ... ON true

Но смысл простой.

LATERAL-функция зависит от текущей строки слева. А LEFT JOIN нужен, чтобы сохранить строку пользователя, даже если функция не вернула элементов.

Условие ON true означает: не добавляем никакого дополнительного условия соединения, просто присоединяем результат функции. Если элементов нет, строка слева всё равно остаётся.

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

Если поля tags нет

Если в JSONB нет поля tags, выражение:

u.prefs -> 'tags'

вернёт SQL NULL.

Чтобы функция получила пустой массив вместо NULL, удобно использовать COALESCE:

COALESCE(u.prefs -> 'tags', '[]'::jsonb)

Полный пример:

SELECT
  u.id,
  e.value AS tag
FROM users u
LEFT JOIN LATERAL jsonb_array_elements_text(
  COALESCE(u.prefs -> 'tags', '[]'::jsonb)
) AS e(value) ON true;

Так запрос спокойно обработает пользователей, у которых поле tags отсутствует.

Если внутри не массив

jsonb_array_elements ожидает именно массив.

Вот это нормально:

["vip", "eu", "beta"]

И это тоже нормально:

[
  {"sku": "A1"},
  {"sku": "B2"}
]

А вот если вместо массива пришёл объект:

{"tag": "vip"}

или строка:

"vip"

или число:

123

функция упадёт с ошибкой, потому что из объекта или скаляра нельзя извлечь элементы массива.

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

SELECT
  u.id,
  e.value AS tag
FROM users u
LEFT JOIN LATERAL jsonb_array_elements_text(
  CASE
    WHEN jsonb_typeof(u.prefs -> 'tags') = 'array'
    THEN u.prefs -> 'tags'
    ELSE '[]'::jsonb
  END
) AS e(value) ON true;

Здесь логика такая:

• если tags действительно массив, разворачиваем его;
• если нет, подставляем пустой массив;
• пустой массив не даёт элементов, но и не ломает запрос.

value против обычного текста

Одна из самых частых ошибок новичка — перепутать jsonb и text.

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

SELECT DISTINCT
  u.id
FROM users u,
     jsonb_array_elements(u.prefs -> 'tags') AS e
WHERE e.value = 'vip';

Проблема в том, что e.value здесь имеет тип jsonb, а 'vip' — это не JSON-строка "vip".

Правильно для jsonb_array_elements так:

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

Но чаще для массива строк лучше просто взять текстовую функцию:

SELECT DISTINCT
  u.id
FROM users u,
     jsonb_array_elements_text(u.prefs -> 'tags') AS tag
WHERE tag = 'vip';

Так запрос читается проще и меньше шансов ошибиться.

Как дать колонкам нормальные имена

По умолчанию колонка называется value. Но в реальных запросах лучше давать понятные имена.

Например:

SELECT
  u.id,
  tags.tag
FROM users u,
     jsonb_array_elements_text(u.prefs -> 'tags') AS tags(tag);

Здесь:

AS tags(tag)

означает:

• результат функции называем tags;
• колонку внутри результата называем tag.

Это делает запрос аккуратнее, особенно если в нём несколько JSON-массивов или несколько соединений.

Пример: соединить теги со справочником

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

tag  | title
-----+----------------
vip  | VIP customer
eu   | Europe
beta | Beta tester

А у пользователей теги лежат в JSONB-массиве.

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

SELECT
  u.id,
  u.email,
  d.title
FROM users u
CROSS JOIN LATERAL jsonb_array_elements_text(u.prefs -> 'tags') AS tags(tag)
JOIN tag_dictionary d ON d.tag = tags.tag;

После разворота tags.tag становится обычным текстовым значением. Поэтому его можно использовать в JOIN, как любую нормальную колонку.

Это и есть главный выигрыш: JSONB-массив превращается в табличные строки.

Пример: пользователи без нужного тега

Иногда нужно найти пользователей, у которых нет определённого тега.

Для такой задачи часто лучше использовать не разворот, а JSONB-оператор проверки. Но через разворот тоже можно, например так:

SELECT
  u.id,
  u.email
FROM users u
WHERE NOT EXISTS (
  SELECT 1
  FROM jsonb_array_elements_text(
    COALESCE(u.prefs -> 'tags', '[]'::jsonb)
  ) AS tags(tag)
  WHERE tags.tag = 'vip'
);

Логика:

1. Для каждого пользователя разворачиваем его теги во внутреннем запросе.
2. Ищем среди них vip.
3. NOT EXISTS оставляет только тех, у кого такого тега нет.

Это хороший пример того, как jsonb_array_elements_text помогает работать с JSON-массивом как с маленькой вложенной таблицей.

Когда jsonb_array_elements не лучший выбор

jsonb_array_elements отлично подходит для аналитики и преобразования массива в строки.

Но не всегда нужно разворачивать массив.

Если задача простая — проверить, содержит ли JSONB определённое значение, иногда короче и быстрее использовать JSONB-операторы.

Например, для проверки, что массив тегов содержит vip, можно написать так:

SELECT
  id,
  email
FROM users
WHERE prefs -> 'tags' @> '["vip"]'::jsonb;

Такой запрос не разворачивает массив в строки. Он просто проверяет включение одного JSONB в другой.

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

MySQL: аналог через JSON_TABLE

В MySQL прямого аналога jsonb_array_elements в старых версиях нет.

В MySQL 8.0 для разворота JSON-массивов используют JSON_TABLE.

Например, если в документе есть массив tags, его можно превратить в строки так:

SELECT
  u.id,
  jt.tag
FROM users u
JOIN JSON_TABLE(
  u.prefs,
  '$.tags[*]'
  COLUMNS (
    tag VARCHAR(50) PATH '$'
  )
) AS jt;

Идея та же самая: массив внутри JSON превращается в табличный набор строк.

Но синтаксис заметно другой. PostgreSQL делает это короче через jsonb_array_elements_text, а MySQL использует более подробную конструкцию JSON_TABLE.

ClickHouse: аналог через arrayJoin

В ClickHouse похожая идея обычно выражается через arrayJoin.

Если данные уже лежат в колонке типа Array, всё просто:

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

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

SELECT
  id,
  arrayJoin(JSONExtractArrayRaw(prefs, 'tags')) AS tag
FROM users;

В ClickHouse тоже действует та же общая идея: массив превращается в строки, и после этого по элементам можно фильтровать, группировать и считать.

PostgreSQL, MySQL и ClickHouse: одна идея, разный синтаксис

Задача	PostgreSQL	MySQL	ClickHouse
Развернуть JSON-массив строк	jsonb_array_elements_text	JSON_TABLE	arrayJoin
Развернуть JSON-массив объектов	jsonb_array_elements	JSON_TABLE	arrayJoin вместе с JSON-функциями
Получить позицию элемента	WITH ORDINALITY	колонка FOR ORDINALITY	часто используют функции массивов

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

Просто в PostgreSQL для JSONB это делается очень компактно.

Частые ошибки с jsonb_array_elements

Сравнить jsonb-строку как обычный text

Проблемный вариант:

SELECT
  e.value
FROM users u,
     jsonb_array_elements(u.prefs -> 'tags') AS e
WHERE e.value = 'vip';

Лучше так:

SELECT
  tag
FROM users u,
     jsonb_array_elements_text(u.prefs -> 'tags') AS tag
WHERE tag = 'vip';

Для массива строк почти всегда проще использовать _text.

Потерять строки с пустым массивом

Такой запрос уберёт пользователей без тегов:

SELECT
  u.id,
  tag
FROM users u,
     jsonb_array_elements_text(u.prefs -> 'tags') AS tag;

Если пользователей нужно сохранить, используйте LEFT JOIN LATERAL:

SELECT
  u.id,
  tags.tag
FROM users u
LEFT JOIN LATERAL jsonb_array_elements_text(
  COALESCE(u.prefs -> 'tags', '[]'::jsonb)
) AS tags(tag) ON true;

Забыть, что WITH ORDINALITY считает с единицы

SELECT
  t.tag,
  t.pos
FROM users u,
     jsonb_array_elements_text(u.prefs -> 'tags') WITH ORDINALITY AS t(tag, pos);

Здесь первый элемент будет иметь pos = 1.

А при доступе через стрелку первый элемент имеет индекс 0:

SELECT
  prefs -> 'tags' -> 0 AS first_tag
FROM users;

Не смешивайте эти два способа отсчёта без проверки.

Разворачивать не массив

Если данные могут быть грязными, лучше проверить тип:

SELECT
  u.id,
  tags.tag
FROM users u
LEFT JOIN LATERAL jsonb_array_elements_text(
  CASE
    WHEN jsonb_typeof(u.prefs -> 'tags') = 'array'
    THEN u.prefs -> 'tags'
    ELSE '[]'::jsonb
  END
) AS tags(tag) ON true;

Так запрос не упадёт, если вместо массива в JSON внезапно окажется объект или строка.

Главное из статьи

jsonb_array_elements превращает JSONB-массив в строки: один элемент массива становится одной строкой результата.

Базовый пример:

SELECT
  u.id,
  e.value
FROM users u,
     jsonb_array_elements(u.prefs -> 'tags') AS e;

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

SELECT
  u.id,
  tag
FROM users u,
     jsonb_array_elements_text(u.prefs -> 'tags') AS tag;

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

• jsonb_array_elements возвращает элементы типа jsonb;
• jsonb_array_elements_text возвращает элементы как обычный text;
• для массива строк обычно удобнее _text;
• для массива объектов нужен обычный jsonb_array_elements, чтобы работать с полями через -> и ->>;
• пустой массив не создаёт строк;
• чтобы не потерять исходную строку, используйте LEFT JOIN LATERAL ... ON true;
• WITH ORDINALITY добавляет номер элемента, но считает с 1;
• JSON-операторы доступа к массиву считают с 0;
• если вместо массива может прийти объект, строка или число, проверяйте тип через jsonb_typeof.

Если совсем коротко: jsonb_array_elements нужен тогда, когда JSONB-массив пора перестать воспринимать как одну большую ячейку и начать работать с ним как с маленькой таблицей внутри строки.