jsonb_object_keys в PostgreSQL: как получить ключи из JSONB-объекта

jsonb_object_keys — это функция PostgreSQL, которая достаёт ключи верхнего уровня из значения типа jsonb.

Проще говоря, если в колонке лежит такой JSONB-объект:

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

то jsonb_object_keys вернёт три значения:

theme
lang
newsletter

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

В обычной таблице структура понятна по колонкам. А в jsonb всё гибче: внутри одной колонки могут жить объекты разной формы. Поэтому иногда сначала нужно не писать сложный отчёт, а спокойно разведать местность: какие ключи вообще встречаются, насколько часто, есть ли нужное поле и где структура отличается от ожидаемой.

Именно для такой разведки и подходит jsonb_object_keys.

Что делает jsonb_object_keys

Сигнатура у функции короткая:

jsonb_object_keys(jsonb)

Но важная деталь спрятана не в названии, а в поведении: jsonb_object_keys возвращает не одно значение, а набор строк.

Такие функции называют set-returning functions. Они работают как маленькая таблица: на вход подали один JSONB-объект, а на выходе получили несколько строк — по одной строке на каждый ключ.

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

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

В колонке prefs хранятся настройки профиля. Например:

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

Чтобы посмотреть ключи в настройках конкретного пользователя, можно написать так:

SELECT jsonb_object_keys(prefs) AS key
FROM users
WHERE id = 42;

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

key
theme
lang
newsletter

Функция не показывает значения. Она показывает только названия полей. То есть она отвечает не на вопрос «что внутри поля?», а на вопрос «какие поля здесь есть?».

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

У jsonb_object_keys есть несколько правил, которые лучше запомнить сразу.

Во-первых, функция возвращает только ключи верхнего уровня.

Если внутри JSONB есть вложенный объект, его внутренние ключи сами по себе не развернутся:

{
  "theme": "dark",
  "profile": {
    "timezone": "UTC",
    "currency": "USD"
  }
}

Для такого объекта jsonb_object_keys вернёт:

theme
profile

А ключи timezone и currency находятся уже глубже, внутри profile. До них нужно добираться отдельным выражением.

Во-вторых, порядок ключей не стоит считать стабильным.

jsonb хранит данные в своём внутреннем виде. Поэтому нельзя полагаться на то, что ключи вернутся ровно в том порядке, в котором они были записаны в исходном JSON. Если нужен красивый и предсказуемый порядок, добавляйте ORDER BY.

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

jsonb_object_keys предназначена именно для JSONB-объектов вида:

{"key": "value"}

Если передать массив, строку или число, это уже не объект с ключами. В таких случаях запрос может завершиться ошибкой. Поэтому в реальных данных полезно заранее проверять тип через jsonb_typeof.

Например:

SELECT jsonb_object_keys(prefs) AS key
FROM users
WHERE jsonb_typeof(prefs) = 'object';

Так запрос будет работать только со строками, где prefs действительно является JSONB-объектом.

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

Так как jsonb_object_keys возвращает набор строк, её часто используют в FROM.

Представим, что в таблице есть такие данные:

id	email	prefs
1	anna@example.com	{"theme": "dark", "lang": "en"}
2	boris@example.com	{"lang": "ru", "newsletter": true}
3	maria@example.com	{"theme": "light", "beta": true}

Развернём ключи для каждой строки:

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

Результат:

id	email	key
1	anna@example.com	theme
1	anna@example.com	lang
2	boris@example.com	lang
2	boris@example.com	newsletter
3	maria@example.com	theme
3	maria@example.com	beta

Что здесь произошло?

Каждая строка из users как будто раскрылась в несколько строк: по одной строке на каждый ключ из prefs.

LATERAL нужен потому, что функция справа использует значение из строки слева: u.prefs. То есть для каждого пользователя PostgreSQL отдельно берёт его JSONB-объект и отдельно разворачивает ключи.

Можно встретить и более короткую запись через запятую:

SELECT
    u.id,
    u.email,
    key
FROM users u,
     jsonb_object_keys(u.prefs) AS key;

В PostgreSQL такой вариант тоже работает: функция в FROM может ссылаться на таблицу, указанную раньше. Но для учебного и рабочего кода явный CROSS JOIN LATERAL обычно понятнее: сразу видно, что мы разворачиваем данные построчно.

Как узнать, какие ключи встречаются в таблице

Один из самых полезных сценариев — разведка структуры.

Допустим, колонка prefs давно живёт в проекте, но никто точно не помнит, какие настройки туда пишутся. Документация устарела, часть полей добавляли временно, часть приехала из старого API.

Нам нужно понять: какие ключи вообще встречаются и насколько часто.

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

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

key	rows_with_key
lang	980
theme	940
newsletter	620
beta	37
old_flag	4

Такой запрос сразу показывает картину.

lang и theme встречаются почти у всех — похоже, это обычные настройки. newsletter есть у части пользователей. beta встречается редко, возможно, это флаг эксперимента. А old_flag попался всего 4 раза — хороший кандидат на проверку: может быть, это старое поле, которое уже никто не использует.

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

Что будет с NULL

Есть важная ловушка: если prefs равен NULL, то jsonb_object_keys(prefs) не вернёт ни одной строки.

При CROSS JOIN LATERAL такая строка пользователя просто исчезнет из результата.

Например:

SELECT
    u.id,
    key
FROM users u
CROSS JOIN LATERAL jsonb_object_keys(u.prefs) AS key;

Если у пользователя prefs равен NULL, в итоговой выдаче его не будет вообще.

Иногда это нормально. Например, если вы осознанно изучаете только заполненные настройки. Тогда лучше написать явно:

SELECT
    u.id,
    key
FROM users u
CROSS JOIN LATERAL jsonb_object_keys(u.prefs) AS key
WHERE u.prefs IS NOT NULL;

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

SELECT
    u.id,
    u.email,
    key
FROM users u
LEFT JOIN LATERAL jsonb_object_keys(u.prefs) AS key ON true;

Теперь пользователь с пустым prefs останется в результате, просто в колонке key будет NULL.

Это особенно важно в отчётах. Иначе можно случайно «потерять» часть строк и сделать вывод по неполной картине.

Как проверить, есть ли конкретный ключ

Частая задача: найти строки, где в JSONB есть определённое поле.

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

Самый простой и правильный способ — оператор ?.

SELECT
    id,
    email
FROM users
WHERE prefs ? 'newsletter';

Оператор ? проверяет наличие ключа верхнего уровня в jsonb.

То есть он вернёт строку, если внутри prefs есть ключ newsletter, даже если значение там false или null.

Например, все эти объекты пройдут проверку:

{"newsletter": true}

{"newsletter": false}

{"newsletter": null}

Потому что вопрос не в значении. Вопрос в наличии ключа.

Ту же проверку можно сделать через jsonb_object_keys и EXISTS:

SELECT
    id,
    email
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM jsonb_object_keys(u.prefs) AS key
    WHERE key = 'newsletter'
);

Но для одного конкретного ключа оператор ? обычно лучше: он короче, понятнее и может эффективно работать с GIN-индексом по jsonb.

Например:

CREATE INDEX users_prefs_gin_idx
ON users
USING gin (prefs);

После этого PostgreSQL может использовать индекс для запросов с JSONB-операторами, включая проверку наличия ключа.

Когда jsonb_object_keys удобнее оператора ?

Возникает честный вопрос: если есть оператор ?, зачем тогда вообще перебирать ключи?

Потому что ? хорош для точной проверки:

prefs ? 'newsletter'

А jsonb_object_keys полезен, когда условие относится не к одному заранее известному ключу, а к набору ключей.

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

SELECT
    id,
    email
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM jsonb_object_keys(u.prefs) AS key
    WHERE key LIKE 'flag_%'
);

Так можно искать группы полей по шаблону.

Например:

• все экспериментальные флаги;
• все поля, начинающиеся с utm_;
• все настройки, заканчивающиеся на _enabled;
• все временные ключи, которые когда-то добавляли для миграции.

Вот пример для ключей с префиксом utm_:

SELECT
    u.id,
    u.email,
    key
FROM users u
CROSS JOIN LATERAL jsonb_object_keys(u.prefs) AS key
WHERE key LIKE 'utm_%'
ORDER BY u.id, key;

Такой запрос помогает быстро понять, какие маркетинговые метки записались в JSONB и у каких пользователей они есть.

Как собрать ключи обратно в массив

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

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

SELECT
    u.id,
    u.email,
    (
        SELECT array_agg(key ORDER BY key)
        FROM jsonb_object_keys(u.prefs) AS key
    ) AS configured_keys
FROM users u
WHERE u.prefs IS NOT NULL;

Результат:

id	email	configured_keys
1	anna@example.com	{lang,theme}
2	boris@example.com	{lang,newsletter}
3	maria@example.com	{beta,theme}

Здесь вложенный подзапрос делает две вещи:

1. jsonb_object_keys(u.prefs) разворачивает ключи конкретного пользователя.
2. array_agg(key ORDER BY key) собирает эти ключи обратно в массив.

ORDER BY key внутри array_agg очень важен. Без него порядок элементов в массиве не стоит считать надёжным. А когда порядок стабильный, массивы удобно сравнивать глазами и использовать в отчётах.

Например, можно найти самые частые наборы настроек:

SELECT
    configured_keys,
    COUNT(*) AS users_count
FROM (
    SELECT
        u.id,
        (
            SELECT array_agg(key ORDER BY key)
            FROM jsonb_object_keys(u.prefs) AS key
        ) AS configured_keys
    FROM users u
    WHERE u.prefs IS NOT NULL
) t
GROUP BY configured_keys
ORDER BY users_count DESC;

Такой отчёт отвечает уже не на вопрос «какие ключи бывают вообще?», а на вопрос «какие комбинации ключей встречаются чаще всего?».

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

Как добраться до вложенных ключей

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

Допустим, в prefs лежит такая структура:

{
  "theme": "dark",
  "notifications": {
    "email": true,
    "sms": false,
    "push": true
  }
}

Чтобы получить ключи внутри notifications, используем оператор ->:

SELECT jsonb_object_keys(prefs -> 'notifications') AS key
FROM users
WHERE id = 42;

Результат:

key
sms
push
email

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

SELECT key
FROM users u
CROSS JOIN LATERAL jsonb_object_keys(u.prefs -> 'notifications') AS key
WHERE u.id = 42
ORDER BY key;

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

SELECT
    u.id,
    key
FROM users u
CROSS JOIN LATERAL jsonb_object_keys(u.prefs -> 'notifications') AS key
WHERE jsonb_typeof(u.prefs -> 'notifications') = 'object';

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

jsonb_object_keys и json_object_keys

В PostgreSQL есть две похожие функции:

• jsonb_object_keys — для типа jsonb;
• json_object_keys — для типа json.

Работают они похоже: обе возвращают ключи верхнего уровня объекта.

Разница в типах данных. json хранит исходный JSON ближе к текстовому виду, а jsonb хранит его в разобранном бинарном формате. В современных PostgreSQL-проектах для запросов, индексов и активной работы с JSON-данными чаще используют именно jsonb.

Если колонка имеет тип jsonb, используйте jsonb_object_keys.

Если колонка имеет тип json, используйте json_object_keys.

Частый практический шаблон

Для начинающего полезно запомнить такой шаблон:

SELECT
    t.id,
    key
FROM table_name t
CROSS JOIN LATERAL jsonb_object_keys(t.jsonb_column) AS key
WHERE jsonb_typeof(t.jsonb_column) = 'object';

В нём есть всё главное:

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

А если нужно не потерять строки, где JSONB пустой или отсутствует, используйте LEFT JOIN LATERAL:

SELECT
    t.id,
    key
FROM table_name t
LEFT JOIN LATERAL jsonb_object_keys(t.jsonb_column) AS key ON true;

Первый вариант подходит для анализа только заполненных объектов. Второй — для отчётов, где важно сохранить все строки исходной таблицы.

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

В других базах похожие задачи решаются иначе.

В MySQL функция JSON_KEYS возвращает ключи объекта, но не набором строк, а одним JSON-массивом:

SELECT JSON_KEYS(prefs) AS keys
FROM users;

Чтобы превратить этот массив в строки, обычно используют JSON_TABLE.

В ClickHouse для похожей задачи можно использовать JSONExtractKeys. Она тоже возвращает массив ключей:

SELECT JSONExtractKeys(prefs) AS keys
FROM users;

А чтобы развернуть массив в строки, используют arrayJoin.

Общее правило остаётся тем же: такие функции обычно работают с ключами верхнего уровня. Если нужны вложенные ключи, до вложенного объекта нужно добраться отдельно.

Главное

jsonb_object_keys — удобный инструмент для разведки и анализа JSONB-структур в PostgreSQL.

Она возвращает по одной строке на каждый ключ верхнего уровня JSONB-объекта. Поэтому её удобно использовать в FROM вместе с CROSS JOIN LATERAL или LEFT JOIN LATERAL.

Для точной проверки одного ключа обычно лучше подходит оператор ?:

SELECT id
FROM users
WHERE prefs ? 'newsletter';

А jsonb_object_keys особенно полезна, когда нужно:

• узнать, какие ключи вообще встречаются в JSONB-колонке;
• посчитать частоту ключей по всей таблице;
• найти ключи по шаблону;
• собрать список ключей в массив через array_agg;
• исследовать данные, у которых нет строгой заранее известной схемы.

Главная мысль простая: когда JSONB-колонка похожа на тёмную кладовку с коробками без подписей, jsonb_object_keys включает свет и показывает, какие ярлыки на этих коробках вообще есть.