JSONB_OBJECT_AGG in PostgreSQL: Fold Key/Value Rows into a Single JSON Object

Рано или поздно у каждого появляется таблица настроек: пользователь, ключ, значение — и приложение, которому всё это нужно отдать одним аккуратным JSON-объектом. Тащить пары строк наружу и склеивать их в коде скучно и хрупко. JSONB_OBJECT_AGG(key, value) решает задачу прямо в базе: агрегатная функция берёт две колонки из множества строк и сворачивает их в один объект вида { "key": value }. Ни второго запроса, ни ручной сборки JSON на стороне приложения — всё считает Postgres.

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

Функция принимает ровно два аргумента: первый становится ключом объекта, второй — значением. Ключ приводится к тексту, значение сохраняет свой JSON-тип. Чаще всего вызов стоит рядом с GROUP BY, но без группировки он честно свернёт всю таблицу в один-единственный объект.

SELECT JSONB_OBJECT_AGG(key, value) AS settings
FROM settings;

Если в таблице settings лежат строки ('theme', 'dark') и ('lang', 'en'), на выходе получится {"theme": "dark", "lang": "en"}. Несколько деталей, которые стоит держать в голове с самого начала:

• Ключ не может быть NULL — такая строка уронит запрос с ошибкой. Отсейте их заранее через WHERE key IS NOT NULL.
• Значение NULL допустимо и спокойно попадёт в объект как JSON null.
• Порядок ключей в jsonb не сохраняется и не имеет значения: перед вами map, а не список.
• Для пустой группы результат — NULL, а не пустой объект {}. Мелочь, на которой легко споткнуться, если код ниже ждёт хотя бы {}.

Документ-справочник на каждую группу

Самое вкусное применение — собрать по одному JSON-объекту на сущность. Допустим, у каждого пользователя есть набор пар «ключ-значение» в таблице user_settings(user_id, key, value). Профиль настроек собирается одним запросом:

SELECT
    user_id,
    JSONB_OBJECT_AGG(key, value) AS prefs
FROM user_settings
GROUP BY user_id;

При этом источником ключей и значений вовсе не обязана быть готовая таблица настроек. Агрегировать можно любые две колонки — например, построить для каждого пользователя карту «статус заказа → сумма по этому статусу»:

SELECT
    user_id,
    JSONB_OBJECT_AGG(status, total) AS totals_by_status
FROM (
    SELECT user_id, status, SUM(amount) AS total
    FROM orders
    GROUP BY user_id, status
) s
GROUP BY user_id;

Подзапрос тут не для красоты: он гарантирует, что на пару (user_id, status) придётся ровно одна строка, а значит — ровно один ключ в итоговом объекте. И это плавно выводит нас к главной засаде.

Поведение при дубликатах ключей

Вот она, главная ловушка. Если внутри одной группы оказались два значения с одинаковым ключом, JSONB_OBJECT_AGG не падает и ничего не складывает — он молча оставляет одно из них. А какое именно, зависит от порядка обработки строк, то есть на практике непредсказуемо.

-- two rows share the key 'color' -> only one survives, order is undefined
SELECT JSONB_OBJECT_AGG(key, value)
FROM (VALUES ('color', 'red'), ('color', 'blue')) AS t(key, value);

Грабли: на негласное «побеждает последний» полагаться нельзя — порядок строк вам никто не обещал. Если по логике должна выигрывать, скажем, самая свежая запись, делайте дедупликацию явной — через DISTINCT ON или оконную функцию — до агрегации:

SELECT user_id, JSONB_OBJECT_AGG(key, value) AS prefs
FROM (
    SELECT DISTINCT ON (user_id, key)
        user_id, key, value
    FROM user_settings
    ORDER BY user_id, key, updated_at DESC
) latest
GROUP BY user_id;

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

JSONB_OBJECT_AGG против JSONB_AGG из объектов

Лёгкая путаница, на которой спотыкаются многие: JSONB_AGG тоже строит JSON, но массив, а не объект. Разница и в форме результата, и в том, как потом по нему ходить.

-- object (map): key lookup by name
SELECT JSONB_OBJECT_AGG(key, value)
FROM user_settings WHERE user_id = 1;
-- => {"theme": "dark", "lang": "en"}

-- array of objects: ordered list, no direct key lookup
SELECT JSONB_AGG(JSONB_BUILD_OBJECT('key', key, 'value', value))
FROM user_settings WHERE user_id = 1;
-- => [{"key": "theme", "value": "dark"}, {"key": "lang", "value": "en"}]

Как выбирать между ними:

• Нужен доступ по имени (prefs->>'theme') и ключи уникальны — берите JSONB_OBJECT_AGG. Это словарь.
• Нужен упорядоченный список, повторяющиеся элементы или несколько полей в каждой записи — берите JSONB_AGG. Это список.
• В JSONB_AGG порядок задаётся через ORDER BY прямо внутри агрегата; для объекта же порядок ключей всё равно ни на что не влияет.

Заметки по другим СУБД

В MySQL аналог зовётся JSON_OBJECTAGG(key, value) и работает похоже, но поведение при дубликатах ключей зависит от версии и режима, так что дедуплицируйте заранее ровно так же, как в PostgreSQL. В ClickHouse прямого агрегата нет вовсе: карту собирают через map() или groupArray пар с последующим преобразованием, а роль JSON-объекта берёт на себя тип Map. Но когда нужен именно компактный документ-справочник на группу, JSONB_OBJECT_AGG в PostgreSQL остаётся самым коротким путём от строк до готового объекта.