JSONB_OBJECT_KEYS in PostgreSQL: List a JSON Object's Top-Level Keys as Rows

jsonb_object_keys — это set-returning функция PostgreSQL: она возвращает по одной строке на каждый ключ верхнего уровня JSONB-объекта. Её зовут, когда колонка типа jsonb пришла из внешнего мира и схемы у неё, считай, нет: один объект несёт три поля, другой — десять, а какие именно, заранее не скажет никто. С jsonb_object_keys можно прощупать форму данных, убедиться, что нужное поле на месте, и собрать инвентарь ключей по всей таблице, не выгружая JSON в приложение и не угадывая структуру наугад.

Функция отдаёт именно набор строк, а не скаляр и не массив, поэтому её естественно ставить в FROM рядом с таблицей и джойнить с разворачиваемыми ключами. Дальше разберём четыре сценария: сигнатуру и базовый вызов, разведку формы незнакомой jsonb-колонки, проверку наличия конкретного поля и обратную сборку ключей в массив через array_agg.

Синтаксис и базовый пример

Сигнатура предельно короткая: jsonb_object_keys(jsonb). Это set-returning функция, то есть на выходе у неё набор строк, а не скаляр. Допустим, в таблице users завелась колонка prefs jsonb с настройками профиля, и мы хотим заглянуть в одну конкретную запись.

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

Если prefs равен {"theme": "dark", "lang": "en", "newsletter": true}, запрос отдаст три строки: theme, lang, newsletter. Несколько деталей, о которые легко споткнуться:

• Возвращаются только ключи верхнего уровня. Вложенные объекты функция не разворачивает.
• Порядок ключей не гарантирован — jsonb хранит их в своём внутреннем порядке, и полагаться на него нельзя.
• Скормите не объект, а массив, число или строку — получите либо ноль строк, либо ошибку. Подавайте на вход только объекты.
• Для типа json есть близнец json_object_keys: ведёт себя так же, только без нормализации значения.

Разведка формы данных

Первый вопрос к незнакомой jsonb-колонке всегда один: «какие ключи здесь вообще встречаются и насколько часто?». Разворачиваем ключи всех строк, группируем — и на руках карта полей с частотностью, по которой сразу видно, что встречается всегда, а что попадается как редкое необязательное.

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

Приём канонический: таблица и set-returning функция, перечисленные через запятую в FROM, образуют неявный LATERAL-джойн. Для каждого пользователя его ключи разворачиваются в отдельные строки, а GROUP BY сводит всю картину воедино.

Грабли: если prefs равен NULL, строка пользователя бесследно выпадет из результата — функция не отдаст по ней ни одной строки, и неявный джойн её отбросит. Чтобы такие записи не растворились, либо пишите явный LEFT JOIN LATERAL ... ON true, либо осознанно ставьте WHERE prefs IS NOT NULL. Иначе разведка молча обойдёт стороной кусок таблицы, а вы будете уверены, что видите всё.

Проверка необязательных полей

Бывает, нужно вытащить строки, где конкретное поле есть (или, наоборот, отсутствует). Для одиночной проверки удобнее оператор ?, но перебор ключей выручает, когда условие на ключ нетривиальное.

-- users who configured a newsletter preference at all
SELECT id, email
FROM users
WHERE prefs ? 'newsletter';

-- equivalent shape check via the keys set
SELECT id, email
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM jsonb_object_keys(u.prefs) AS key
    WHERE key = 'newsletter'
);

Оператор ? (проверяет наличие ключа верхнего уровня) почти всегда и быстрее, и читабельнее для одного поля — особенно если по prefs построен GIN-индекс, который оператор умеет задействовать. А вот перебор через jsonb_object_keys отрабатывает свой хлеб там, где условие сложнее точного равенства: например, «есть хотя бы один ключ с префиксом flag_» — такое одним оператором не выразишь.

Сбор ключей через array_agg

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

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;

ORDER BY key внутри array_agg задаёт стабильный, человекочитаемый порядок взамен внутреннего порядка jsonb. Такие массивы легко сопоставлять между строками: выловить пользователей с нестандартным набором настроек или собрать отчёт «у кого какие поля заполнены», ни разу не вытащив сам JSON в код приложения.

Другие СУБД

В MySQL прямого аналога-набора нет: JSON_KEYS(prefs) возвращает JSON-массив ключей одним значением, а не набором строк, — чтобы развернуть его в строки, придётся звать JSON_TABLE. В ClickHouse для разбора JSON берут JSONExtractKeys(prefs), который тоже отдаёт массив, а развернуть его помогает arrayJoin. Общее правило держите в голове во всех трёх системах: речь идёт только о ключах верхнего уровня — до вложенных объектов придётся добираться отдельным проходом.