Šis raksts pašlaik ir krievu valodā — angļu tulkojums tiek gatavots.
jsonb_each — это set-returning функция PostgreSQL: она берёт один JSON-объект и раскладывает его на строки, по одной на каждую пару (key, value). К ней тянутся, когда нужно перебрать содержимое jsonb-колонки целиком, не зная заранее имён ключей: вытащить все настройки разом, посчитать их, отфильтровать по значению или поджойнить к остальным данным.
В одной jsonb-колонке нередко прячется целый словарь: настройки пользователя, метаданные события, набор фичефлагов. Пока он лежит цельным объектом, обычный SQL до него не дотягивается — ни ключи сгруппировать, ни посчитать, ни поджойнить. jsonb_each ломает эту стену и превращает карту в обычный набор строк. По сути это зеркало JSONB_OBJECT_AGG: там строки сворачивались в карту, здесь карта снова разворачивается в строки, и с ними можно работать как с любой таблицей — фильтровать, джойнить, агрегировать.
Дальше разберём синтаксис и тип результата, разворот настоящей колонки через LATERAL, фильтрацию и агрегацию записей, вариант jsonb_each_text и то, чем на это отвечают MySQL и ClickHouse. По дороге отметим типичные грабли: value остаётся типом jsonb, вход не-объект даёт ошибку, а NULL молча выкидывает строку из выборки.
Синтаксис и базовый пример
Функция принимает один аргумент типа jsonb и возвращает две колонки: key типа text и value типа jsonb. Звать её логичнее всего в FROM, а не в списке SELECT, — тогда каждая запись объекта становится отдельной строкой результата, а не одной ячейкой.
SELECT key, value
FROM jsonb_each('{"theme": "dark", "lang": "en"}'::jsonb);
На выходе две строки: ('theme', "dark") и ('lang', "en"). Несколько деталей, о которые легко споткнуться:
value остаётся типом jsonb, поэтому строковые значения приходят в кавычках: "dark", а не dark.- Порядок строк не гарантирован: объект — это карта, а не список, и полагаться на него нельзя.
- Если на вход подать не объект, а массив или скаляр, будет ошибка. Массив разворачивают через
jsonb_array_elements.
- На вход
NULL функция отдаёт ноль строк, а не одну строку с NULL внутри.
Разворачиваем колонку JSON по строкам
В реальной жизни объект почти всегда лежит не в литерале, а в колонке. Пусть у таблицы users есть поле prefs jsonb с произвольным набором настроек. Чтобы развернуть его, ставим функцию в FROM через запятую — это неявный LATERAL-джойн, который прогоняет функцию для каждой строки слева:
SELECT u.id, u.email, e.key, e.value
FROM users AS u, jsonb_each(u.prefs) AS e
WHERE u.id = 1;
Каждая пара из prefs становится отдельной строкой, привязанной к пользователю. Главный козырь приёма в том, что ключи могут быть какими угодно: их имена не нужно знать заранее, в отличие от жёсткого prefs->>'theme'. Это спасает, когда схема плавающая и хочется просто перебрать всё, что в объекте есть, или выяснить, какие ключи там вообще встречаются.
Грабли: если prefs хоть у кого-то окажется NULL, неявный джойн молча выкинет такого пользователя из выборки — строка просто исчезнет, и вы об этом не узнаете. Чтобы пользователь остался, берите явный LEFT JOIN LATERAL ... ON true и подсовывайте COALESCE(prefs, '{}'::jsonb).
Фильтрация и агрегация записей
Как только записи объекта превратились в строки, к ним применим весь привычный арсенал SQL — WHERE, GROUP BY, count. Скажем, посчитать, сколько ключей в каждом объекте настроек:
SELECT u.id, count(*) AS pref_count
FROM users AS u, jsonb_each(u.prefs) AS e
GROUP BY u.id;
Или выяснить, какие ключи популярнее всего по всей таблице, — классический способ разведать «дикий» JSON, в который годами писали без схемы и без оглядки:
SELECT e.key, count(*) AS used_by
FROM users AS u, jsonb_each(u.prefs) AS e
GROUP BY e.key
ORDER BY used_by DESC;
Фильтровать можно и по ключу, и по значению. Например, найти всех, у кого хоть одна настройка равна строке "on":
SELECT DISTINCT u.id, u.email
FROM users AS u, jsonb_each(u.prefs) AS e
WHERE e.value = '"on"'::jsonb;
Присмотритесь к сравнению: раз value имеет тип jsonb, то и литерал справа обязан быть валидным JSON — отсюда кавычки внутри '"on"'. Забудете их — и сравнение либо упадёт, либо тихо не совпадёт ни с чем.
Вариант _text: когда JSON в значении лишний
Если значения по сути текстовые, кавычки вокруг них только мешают жить. На этот случай есть jsonb_each_text: он отдаёт ту же пару, но value приходит уже типом text, без обрамляющих кавычек.
SELECT key, value
FROM jsonb_each_text('{"theme": "dark", "lang": "en"}'::jsonb);
Разница особенно заметна, когда значение нужно сравнить или привести к числу:
SELECT u.id, (e.value)::int AS limit_value
FROM users AS u, jsonb_each_text(u.prefs) AS e
WHERE e.key = 'rate_limit';
С обычным jsonb_each тот же фокус потребовал бы двойного снятия типа — сначала из jsonb в text, потом в int. Но и тут поджидает подвох: jsonb_each_text приводит к тексту вообще всё, включая вложенные объекты и массивы — они вернутся сериализованной JSON-строкой, а JSON-null превратится в SQL-NULL. Для скалярных значений вариант идеален, а вот для вложенных структур держите в уме, что они склеятся обратно в текст.
Заметки по другим СУБД
Разворачивать JSON-объект в строки — профильная сила именно PostgreSQL. В MySQL прямого аналога jsonb_each нет: ключи обычно вытаскивают через JSON_KEYS, а потом разворачивают полученный массив таблицей JSON_TABLE — выходит заметно многословнее. В ClickHouse JSON-объекты чаще хранят типом Map, и перебор идёт через mapKeys и mapValues в связке с arrayJoin, а не через отдельную функцию. Но когда задача — вернуть карту обратно в строки и работать с ней обычным SQL, jsonb_each и jsonb_each_text в PostgreSQL остаются самым прямым и честным инструментом.
jsonb_each— это set-returning функция PostgreSQL: она берёт один JSON-объект и раскладывает его на строки, по одной на каждую пару(key, value). К ней тянутся, когда нужно перебрать содержимоеjsonb-колонки целиком, не зная заранее имён ключей: вытащить все настройки разом, посчитать их, отфильтровать по значению или поджойнить к остальным данным.В одной
jsonb-колонке нередко прячется целый словарь: настройки пользователя, метаданные события, набор фичефлагов. Пока он лежит цельным объектом, обычный SQL до него не дотягивается — ни ключи сгруппировать, ни посчитать, ни поджойнить.jsonb_eachломает эту стену и превращает карту в обычный набор строк. По сути это зеркалоJSONB_OBJECT_AGG: там строки сворачивались в карту, здесь карта снова разворачивается в строки, и с ними можно работать как с любой таблицей — фильтровать, джойнить, агрегировать.Дальше разберём синтаксис и тип результата, разворот настоящей колонки через
LATERAL, фильтрацию и агрегацию записей, вариантjsonb_each_textи то, чем на это отвечают MySQL и ClickHouse. По дороге отметим типичные грабли:valueостаётся типомjsonb, вход не-объект даёт ошибку, аNULLмолча выкидывает строку из выборки.Синтаксис и базовый пример
Функция принимает один аргумент типа
jsonbи возвращает две колонки:keyтипаtextиvalueтипаjsonb. Звать её логичнее всего вFROM, а не в спискеSELECT, — тогда каждая запись объекта становится отдельной строкой результата, а не одной ячейкой.SELECT key, value FROM jsonb_each('{"theme": "dark", "lang": "en"}'::jsonb);На выходе две строки:
('theme', "dark")и('lang', "en"). Несколько деталей, о которые легко споткнуться:valueостаётся типомjsonb, поэтому строковые значения приходят в кавычках:"dark", а неdark.jsonb_array_elements.NULLфункция отдаёт ноль строк, а не одну строку сNULLвнутри.Разворачиваем колонку JSON по строкам
В реальной жизни объект почти всегда лежит не в литерале, а в колонке. Пусть у таблицы
usersесть полеprefs jsonbс произвольным набором настроек. Чтобы развернуть его, ставим функцию вFROMчерез запятую — это неявныйLATERAL-джойн, который прогоняет функцию для каждой строки слева:SELECT u.id, u.email, e.key, e.value FROM users AS u, jsonb_each(u.prefs) AS e WHERE u.id = 1;Каждая пара из
prefsстановится отдельной строкой, привязанной к пользователю. Главный козырь приёма в том, что ключи могут быть какими угодно: их имена не нужно знать заранее, в отличие от жёсткогоprefs->>'theme'. Это спасает, когда схема плавающая и хочется просто перебрать всё, что в объекте есть, или выяснить, какие ключи там вообще встречаются.Фильтрация и агрегация записей
Как только записи объекта превратились в строки, к ним применим весь привычный арсенал SQL —
WHERE,GROUP BY,count. Скажем, посчитать, сколько ключей в каждом объекте настроек:SELECT u.id, count(*) AS pref_count FROM users AS u, jsonb_each(u.prefs) AS e GROUP BY u.id;Или выяснить, какие ключи популярнее всего по всей таблице, — классический способ разведать «дикий» JSON, в который годами писали без схемы и без оглядки:
SELECT e.key, count(*) AS used_by FROM users AS u, jsonb_each(u.prefs) AS e GROUP BY e.key ORDER BY used_by DESC;Фильтровать можно и по ключу, и по значению. Например, найти всех, у кого хоть одна настройка равна строке
"on":SELECT DISTINCT u.id, u.email FROM users AS u, jsonb_each(u.prefs) AS e WHERE e.value = '"on"'::jsonb;Присмотритесь к сравнению: раз
valueимеет типjsonb, то и литерал справа обязан быть валидным JSON — отсюда кавычки внутри'"on"'. Забудете их — и сравнение либо упадёт, либо тихо не совпадёт ни с чем.Вариант _text: когда JSON в значении лишний
Если значения по сути текстовые, кавычки вокруг них только мешают жить. На этот случай есть
jsonb_each_text: он отдаёт ту же пару, ноvalueприходит уже типомtext, без обрамляющих кавычек.-- value comes back as plain text: dark, not "dark" SELECT key, value FROM jsonb_each_text('{"theme": "dark", "lang": "en"}'::jsonb);Разница особенно заметна, когда значение нужно сравнить или привести к числу:
-- read a numeric flag straight from a dynamic key SELECT u.id, (e.value)::int AS limit_value FROM users AS u, jsonb_each_text(u.prefs) AS e WHERE e.key = 'rate_limit';С обычным
jsonb_eachтот же фокус потребовал бы двойного снятия типа — сначала изjsonbвtext, потом вint. Но и тут поджидает подвох:jsonb_each_textприводит к тексту вообще всё, включая вложенные объекты и массивы — они вернутся сериализованной JSON-строкой, а JSON-nullпревратится в SQL-NULL. Для скалярных значений вариант идеален, а вот для вложенных структур держите в уме, что они склеятся обратно в текст.Заметки по другим СУБД
Разворачивать JSON-объект в строки — профильная сила именно PostgreSQL. В MySQL прямого аналога
jsonb_eachнет: ключи обычно вытаскивают черезJSON_KEYS, а потом разворачивают полученный массив таблицейJSON_TABLE— выходит заметно многословнее. В ClickHouse JSON-объекты чаще хранят типомMap, и перебор идёт черезmapKeysиmapValuesв связке сarrayJoin, а не через отдельную функцию. Но когда задача — вернуть карту обратно в строки и работать с ней обычным SQL,jsonb_eachиjsonb_each_textв PostgreSQL остаются самым прямым и честным инструментом.