`jsonb_each` в PostgreSQL: как развернуть JSON-объект в строки

jsonb_each — это функция PostgreSQL, которая берёт один JSON-объект и превращает его в набор строк.

Было так:

{
  "theme": "dark",
  "lang": "en"
}

А после jsonb_each становится примерно так:

theme | "dark"
lang  | "en"

То есть каждая пара из JSON-объекта превращается в отдельную строку: ключ отдельно, значение отдельно.

Это очень полезно, когда в колонке лежит не одно конкретное поле, а целый «словарь»: настройки пользователя, метаданные события, набор фичефлагов, параметры формы, технические свойства заказа.

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

jsonb_each решает эту проблему: он разворачивает объект в обычные строки, а дальше включается привычный SQL — WHERE, JOIN, GROUP BY, COUNT, ORDER BY.

Можно думать о jsonb_each как об обратной операции к jsonb_object_agg. Одна функция собирает строки в JSON-объект, другая разбирает JSON-объект обратно на строки.

Базовый синтаксис

Функция принимает одно значение типа jsonb и возвращает две колонки:

• key — ключ объекта, тип text;
• value — значение ключа, тип jsonb.

Простой пример:

SELECT key, value
FROM jsonb_each('{"theme": "dark", "lang": "en"}'::jsonb);

Результат будет таким:

key   | value
------+--------
theme | "dark"
lang  | "en"

Обратите внимание на кавычки вокруг "dark" и "en" в результате. Это не обычный текст, а JSON-значение. Поэтому строка внутри jsonb отображается с кавычками.

Это одна из главных деталей, которую новички часто пропускают: value у jsonb_each имеет тип jsonb, а не text.

Почему функцию обычно вызывают в FROM

jsonb_each возвращает не одно значение, а набор строк. Такие функции удобно вызывать в секции FROM.

Вот так правильно и понятно:

SELECT key, value
FROM jsonb_each('{"theme": "dark", "lang": "en"}'::jsonb);

По смыслу PostgreSQL временно делает из JSON-объекта маленькую таблицу:

key   | value
------+--------
theme | "dark"
lang  | "en"

А раз это уже похоже на таблицу, с результатом можно работать обычным SQL.

Например, отфильтровать только один ключ:

SELECT key, value
FROM jsonb_each('{"theme": "dark", "lang": "en"}'::jsonb)
WHERE key = 'theme';

Или выбрать только значения:

SELECT value
FROM jsonb_each('{"theme": "dark", "lang": "en"}'::jsonb);

Важные особенности jsonb_each

У функции есть несколько особенностей, о которых лучше узнать сразу.

Первая: порядок строк не гарантирован.

JSON-объект — это не список, а набор пар ключ-значение. Поэтому нельзя рассчитывать, что PostgreSQL вернёт ключи именно в том порядке, в котором они были записаны в JSON.

Если порядок важен, явно добавляйте ORDER BY:

SELECT key, value
FROM jsonb_each('{"theme": "dark", "lang": "en"}'::jsonb)
ORDER BY key;

Вторая: на вход нужно передавать именно JSON-объект.

Так работает:

SELECT key, value
FROM jsonb_each('{"a": 1, "b": 2}'::jsonb);

А так — нет, потому что это массив:

SELECT key, value
FROM jsonb_each('[1, 2, 3]'::jsonb);

Для массивов нужна другая функция — jsonb_array_elements.

Третья: если на вход передать SQL-NULL, функция вернёт ноль строк.

SELECT key, value
FROM jsonb_each(NULL::jsonb);

Это не одна строка с пустыми значениями. Это именно отсутствие строк.

Разворачиваем JSON-колонку из таблицы

В реальной базе JSON обычно лежит не прямо в запросе, а в колонке таблицы.

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

CREATE TABLE users (
    id bigint,
    email text,
    prefs jsonb
);

В колонке prefs лежат настройки пользователя:

{
  "theme": "dark",
  "lang": "en",
  "notifications": "on"
}

Чтобы развернуть настройки каждого пользователя в строки, используем jsonb_each вместе с таблицей users:

SELECT
    u.id,
    u.email,
    e.key,
    e.value
FROM users AS u,
     jsonb_each(u.prefs) AS e;

Если у одного пользователя в prefs три ключа, то из одной строки пользователя получится три строки результата.

Например:

id | email          | key           | value
---+----------------+---------------+--------
1  | a@example.com  | theme         | "dark"
1  | a@example.com  | lang          | "en"
1  | a@example.com  | notifications | "on"

Это главный смысл jsonb_each: он превращает «широкий» JSON-объект внутри одной ячейки в обычные строки, которые SQL уже умеет фильтровать, считать и соединять.

Что здесь делает LATERAL

Запись через запятую:

FROM users AS u,
     jsonb_each(u.prefs) AS e

в PostgreSQL работает как неявный LATERAL.

Это значит: функция jsonb_each(u.prefs) выполняется отдельно для каждой строки из users.

Можно написать то же самое явно:

SELECT
    u.id,
    u.email,
    e.key,
    e.value
FROM users AS u
CROSS JOIN LATERAL jsonb_each(u.prefs) AS e;

Для новичка слово LATERAL сначала выглядит страшно, но идея простая.

Обычный подзапрос в FROM живёт сам по себе. А LATERAL-часть может смотреть на строки, которые находятся слева от неё.

В нашем случае jsonb_each смотрит на u.prefs, то есть на JSON конкретного пользователя.

Как не потерять строки с NULL

Есть важная ловушка.

Если у пользователя prefs равен NULL, то jsonb_each(u.prefs) вернёт ноль строк. При обычном CROSS JOIN LATERAL такой пользователь исчезнет из результата.

Например, есть пользователь:

id | email         | prefs
---+---------------+-------
7  | b@example.com | NULL

В запросе ниже он не появится:

SELECT
    u.id,
    u.email,
    e.key,
    e.value
FROM users AS u
CROSS JOIN LATERAL jsonb_each(u.prefs) AS e;

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

SELECT
    u.id,
    u.email,
    e.key,
    e.value
FROM users AS u
LEFT JOIN LATERAL jsonb_each(COALESCE(u.prefs, '{}'::jsonb)) AS e
    ON true;

Здесь COALESCE заменяет NULL на пустой JSON-объект:

'{}'::jsonb

А LEFT JOIN LATERAL оставляет пользователя в результате, даже если справа не нашлось ни одной строки.

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

Считаем количество ключей в JSON

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

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

SELECT
    u.id,
    COUNT(e.key) AS pref_count
FROM users AS u
LEFT JOIN LATERAL jsonb_each(COALESCE(u.prefs, '{}'::jsonb)) AS e
    ON true
GROUP BY u.id
ORDER BY u.id;

Почему здесь лучше считать COUNT(e.key), а не COUNT(*)?

Потому что при LEFT JOIN пользователь без настроек всё равно останется в результате. COUNT(*) посчитает такую строку как 1, а COUNT(e.key) посчитает только реальные ключи.

Для пользователя без настроек получится 0, а не 1.

Ищем самые популярные ключи

Одна из самых полезных задач — понять, какие ключи вообще встречаются в «диком» JSON.

Например, в проекте несколько лет складывали в prefs всё подряд. Кто-то писал theme, кто-то language, кто-то lang, кто-то notifications, а документации давно нет.

jsonb_each помогает быстро провести разведку:

SELECT
    e.key,
    COUNT(*) AS used_by
FROM users AS u
CROSS JOIN LATERAL jsonb_each(u.prefs) AS e
GROUP BY e.key
ORDER BY used_by DESC, e.key;

Так можно увидеть самые частые ключи и понять, что реально лежит в данных.

Например:

key           | used_by
--------------+--------
theme         | 1842
lang          | 1710
notifications | 1635
timezone      | 812

Это отличный приём перед рефакторингом JSON-колонки. Сначала смотрим, какие ключи живут в данных, потом решаем, какие стоит вынести в обычные колонки.

Фильтрация по ключу

Можно выбрать только конкретные ключи.

Например, достать тему интерфейса у всех пользователей:

SELECT
    u.id,
    u.email,
    e.value AS theme
FROM users AS u
CROSS JOIN LATERAL jsonb_each(u.prefs) AS e
WHERE e.key = 'theme';

Но помним: e.value здесь всё ещё jsonb.

Если значение было строкой, в результате оно будет выглядеть как JSON-строка:

"dark"

А не просто:

dark

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

Фильтрация по значению

Можно искать строки не только по ключу, но и по значению.

Например, найдём пользователей, у которых какая-то настройка равна строке "on":

SELECT DISTINCT
    u.id,
    u.email
FROM users AS u
CROSS JOIN LATERAL jsonb_each(u.prefs) AS e
WHERE e.value = '"on"'::jsonb;

Сравнение выглядит непривычно:

e.value = '"on"'::jsonb

Почему не так?

e.value = 'on'

Потому что e.value имеет тип jsonb. Значит, справа тоже нужно дать валидное JSON-значение.

JSON-строка записывается с кавычками внутри:

'"on"'::jsonb

А число можно сравнивать проще:

SELECT DISTINCT
    u.id,
    u.email
FROM users AS u
CROSS JOIN LATERAL jsonb_each(u.prefs) AS e
WHERE e.value = '10'::jsonb;

Для JSON-числа 10 кавычки внутри не нужны, потому что это не строка.

jsonb_each_text: когда нужен обычный текст

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

Она делает почти то же самое, но возвращает value уже как text, а не как jsonb.

Пример:

SELECT key, value
FROM jsonb_each_text('{"theme": "dark", "lang": "en"}'::jsonb);

Результат:

key   | value
------+-------
theme | dark
lang  | en

Теперь значение dark приходит без JSON-кавычек.

Сравнение тоже становится приятнее:

SELECT DISTINCT
    u.id,
    u.email
FROM users AS u
CROSS JOIN LATERAL jsonb_each_text(u.prefs) AS e
WHERE e.value = 'on';

Здесь уже не нужно писать '"on"'::jsonb.

Приведение значений к числам

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

Допустим, в настройках есть ключ rate_limit:

{
  "rate_limit": 100
}

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

SELECT
    u.id,
    e.value::int AS rate_limit
FROM users AS u
CROSS JOIN LATERAL jsonb_each_text(u.prefs) AS e
WHERE e.key = 'rate_limit';

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

Например, найти пользователей с лимитом больше 50:

SELECT
    u.id,
    u.email,
    e.value::int AS rate_limit
FROM users AS u
CROSS JOIN LATERAL jsonb_each_text(u.prefs) AS e
WHERE e.key = 'rate_limit'
  AND e.value::int > 50;

Но здесь нужна осторожность. Если в каком-то JSON значение окажется не числом, а строкой вроде "many", приведение к int упадёт с ошибкой.

Для чистых данных это нормально. Для грязных данных лучше сначала проверять формат значения.

Что будет с вложенными объектами и массивами

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

Например:

SELECT key, value
FROM jsonb_each_text(
    '{"theme": "dark", "profile": {"city": "Tbilisi"}}'::jsonb
);

Ключ theme вернётся как обычный текст:

dark

А ключ profile вернётся как JSON, превращённый в строку:

{"city": "Tbilisi"}

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

Если вам нужно продолжать работать с вложенным объектом как с JSON, лучше использовать обычный jsonb_each, где value остаётся типом jsonb.

JSON-null и SQL-NULL

Отдельно стоит помнить про null.

В JSON можно хранить значение null:

{
  "theme": null
}

Для jsonb_each это будет JSON-значение null.

А для jsonb_each_text JSON-null превратится в SQL-NULL.

Пример:

SELECT key, value
FROM jsonb_each_text('{"theme": null}'::jsonb);

Результат будет примерно таким:

key   | value
------+------
theme | NULL

Это важно, если вы потом фильтруете значения.

Такой фильтр не найдёт NULL:

WHERE value = NULL

В SQL для этого нужно писать:

WHERE value IS NULL

Когда использовать jsonb_each, а когда jsonb_each_text

Выбирайте jsonb_each, если хотите сохранить JSON-тип значения.

Это полезно, когда:

• значения могут быть объектами;
• значения могут быть массивами;
• нужно сравнивать именно JSON;
• нужно дальше применять JSON-функции и операторы.

Пример:

SELECT
    e.key,
    e.value
FROM users AS u
CROSS JOIN LATERAL jsonb_each(u.prefs) AS e
WHERE jsonb_typeof(e.value) = 'object';

Выбирайте jsonb_each_text, если значения нужны как обычный текст.

Это удобно, когда:

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

Пример:

SELECT
    u.id,
    e.key,
    e.value
FROM users AS u
CROSS JOIN LATERAL jsonb_each_text(u.prefs) AS e
WHERE e.value = 'enabled';

Простое правило: если нужно продолжать жить в мире JSON — берите jsonb_each. Если нужно перейти в обычный SQL-текст — берите jsonb_each_text.

Пример из жизни: фичефлаги пользователей

Представим таблицу пользователей, где в prefs лежат фичефлаги:

{
  "new_dashboard": "on",
  "beta_checkout": "off",
  "dark_mode": "on"
}

Найдём всех пользователей, у которых включён хотя бы один флаг:

SELECT DISTINCT
    u.id,
    u.email
FROM users AS u
CROSS JOIN LATERAL jsonb_each_text(u.prefs) AS e
WHERE e.value = 'on';

Теперь посчитаем, какие флаги чаще всего включены:

SELECT
    e.key AS flag_name,
    COUNT(*) AS enabled_count
FROM users AS u
CROSS JOIN LATERAL jsonb_each_text(u.prefs) AS e
WHERE e.value = 'on'
GROUP BY e.key
ORDER BY enabled_count DESC, flag_name;

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

Пример из жизни: метаданные событий

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

CREATE TABLE events (
    id bigint,
    event_name text,
    properties jsonb
);

В properties лежат разные свойства события:

{
  "source": "email",
  "campaign": "spring",
  "device": "mobile"
}

Можно узнать, какие свойства вообще используются в событиях:

SELECT
    e.key,
    COUNT(*) AS usage_count
FROM events AS ev
CROSS JOIN LATERAL jsonb_each(ev.properties) AS e
GROUP BY e.key
ORDER BY usage_count DESC, e.key;

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

SELECT
    e.value AS source,
    COUNT(*) AS event_count
FROM events AS ev
CROSS JOIN LATERAL jsonb_each_text(ev.properties) AS e
WHERE e.key = 'source'
GROUP BY e.value
ORDER BY event_count DESC, source;

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

Что делать, если в колонке может быть не объект

jsonb_each ожидает объект. Если в колонке внезапно лежит массив, строка или число, запрос упадёт.

Например, такие значения для jsonb_each не подходят:

["a", "b", "c"]

"dark"

42

Перед разворотом можно проверить тип через jsonb_typeof:

SELECT
    u.id,
    e.key,
    e.value
FROM users AS u
CROSS JOIN LATERAL jsonb_each(u.prefs) AS e
WHERE jsonb_typeof(u.prefs) = 'object';

Но здесь есть тонкость: WHERE применяется уже после FROM, а функция в FROM может успеть выполниться раньше.

Надёжнее подать в функцию объект в любом случае:

SELECT
    u.id,
    e.key,
    e.value
FROM users AS u
CROSS JOIN LATERAL jsonb_each(
    CASE
        WHEN jsonb_typeof(u.prefs) = 'object' THEN u.prefs
        ELSE '{}'::jsonb
    END
) AS e;

Если prefs — объект, разворачиваем его. Если нет — подставляем пустой объект.

Так запрос не упадёт на неожиданных данных.

Чем это отличается от обращения к конкретному ключу

Если вы заранее знаете нужный ключ, можно использовать операторы -> и ->>.

Например:

SELECT
    id,
    prefs->>'theme' AS theme
FROM users;

Это хороший вариант, когда нужен конкретный ключ theme.

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

Вот здесь и нужен jsonb_each.

Он отвечает не на вопрос:

«Какое значение лежит по ключу theme?»

А на вопрос:

«Какие пары ключ-значение вообще лежат внутри этого объекта?»

Это разные задачи.

Аналоги в других СУБД

В PostgreSQL для такой задачи есть прямой и удобный инструмент: jsonb_each и jsonb_each_text.

В MySQL прямого аналога jsonb_each нет. Обычно приходится сначала получать список ключей через JSON_KEYS, а потом разворачивать его через JSON_TABLE. Такой подход работает, но выглядит более многословно.

Идея примерно такая: сначала достать массив ключей, затем превратить массив в строки, затем по каждому ключу достать значение из исходного JSON.

В ClickHouse похожие задачи часто решают иначе. Если данные хранятся как Map, можно получить ключи и значения через mapKeys и mapValues, а затем развернуть массивы через arrayJoin.

То есть сама идея одна и та же: превратить карту в строки. Но в PostgreSQL это делается особенно прямо и читаемо.

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

Первая ошибка — забыть, что value у jsonb_each имеет тип jsonb.

Вот так сравнивать строковое JSON-значение неправильно:

WHERE e.value = 'on'

Нужно либо сравнивать с JSON-строкой:

WHERE e.value = '"on"'::jsonb

либо использовать jsonb_each_text:

WHERE e.value = 'on'

Вторая ошибка — применять jsonb_each к массиву.

Для объектов:

jsonb_each('{"a": 1}'::jsonb)

Для массивов нужна другая функция:

jsonb_array_elements('[1, 2, 3]'::jsonb)

Третья ошибка — случайно потерять строки с NULL.

Если JSON-колонка равна SQL-NULL, jsonb_each вернёт ноль строк. При обычном CROSS JOIN LATERAL исходная строка исчезнет из результата.

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

Четвёртая ошибка — ждать стабильного порядка ключей.

JSON-объект не гарантирует порядок. Если порядок важен, добавляйте ORDER BY.

Главное

jsonb_each разворачивает JSON-объект в строки.

На вход она принимает jsonb, а на выходе даёт две колонки: key типа text и value типа jsonb.

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

Если значение нужно как обычный текст, используйте jsonb_each_text. Она убирает JSON-кавычки вокруг строк и упрощает сравнения.

Для колонок таблицы jsonb_each обычно используют через CROSS JOIN LATERAL или LEFT JOIN LATERAL.

Если JSON-колонка может быть NULL, обычный CROSS JOIN LATERAL может незаметно выкинуть строку из результата. Чтобы сохранить исходную строку, используйте LEFT JOIN LATERAL и при необходимости COALESCE.

jsonb_each — это мост между гибким JSON и обычным SQL. Пока данные лежат внутри объекта, они как будто спрятаны в коробке. А когда вы разворачиваете их через jsonb_each, они становятся нормальными строками таблицы: их можно фильтровать, считать, группировать и соединять с другими данными.