JSONB_OBJECT_AGG в PostgreSQL: как собрать JSONB-объект из строк

Рано или поздно в проекте появляется таблица настроек.

Например, такая:

user_id	key	value
1	theme	dark
1	lang	en
1	newsletter	true

Для базы это три строки. А приложению часто нужен один аккуратный JSON-объект:

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

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

В PostgreSQL эту задачу удобно решает агрегатная функция JSONB_OBJECT_AGG.

Она берёт две колонки: одну для ключей, другую для значений — и собирает из них один объект типа jsonb.

Что делает JSONB_OBJECT_AGG

JSONB_OBJECT_AGG — это агрегатная функция PostgreSQL.

Она работает примерно как COUNT, SUM или STRING_AGG, только результатом будет не число и не строка, а JSONB-объект.

Синтаксис:

JSONB_OBJECT_AGG(key, value)

Первый аргумент становится ключом объекта, второй — значением.

Например:

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

Если в таблице settings лежат строки:

key	value
theme	dark
lang	en

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

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

То есть PostgreSQL берёт строки и сворачивает их в объект формата «ключ → значение».

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

Допустим, у нас есть таблица пользовательских настроек:

CREATE TABLE user_settings (
    user_id integer,
    key text,
    value jsonb,
    updated_at timestamp
);

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

user_id	key	value
1	theme	"dark"
1	lang	"en"
1	newsletter	true
2	theme	"light"
2	lang	"ru"

Обратите внимание: в примере колонка value имеет тип jsonb. Поэтому строковые значения выглядят как JSON-строки: "dark", "en", "light".

Теперь соберём настройки каждого пользователя в один объект:

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

Результат:

user_id	prefs
1	{"lang": "en", "theme": "dark", "newsletter": true}
2	{"lang": "ru", "theme": "light"}

Здесь GROUP BY user_id говорит: «собери строки отдельно для каждого пользователя».

А JSONB_OBJECT_AGG(key, value) внутри каждой группы превращает пары key и value в один JSONB-объект.

Если GROUP BY нет

JSONB_OBJECT_AGG можно использовать и без GROUP BY. Тогда она соберёт один объект по всей выборке.

Например, есть таблица системных параметров:

key	value
app_name	"SQL Arena"
default_lang	"ru"
maintenance	false

Запрос:

SELECT JSONB_OBJECT_AGG(key, value) AS config
FROM app_settings;

вернёт один объект:

{
  "app_name": "SQL Arena",
  "default_lang": "ru",
  "maintenance": false
}

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

Ключ не может быть NULL

У JSONB_OBJECT_AGG есть важное правило: ключ объекта не может быть NULL.

Вот такой набор данных опасен:

key	value
theme	"dark"
NULL	"some value"

Если попытаться собрать объект, PostgreSQL выдаст ошибку:

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

Почему так? Потому что JSON-объект состоит из именованных полей. У поля должно быть имя. А NULL — это не имя, а отсутствие значения.

Поэтому в реальных запросах часто добавляют фильтр:

SELECT JSONB_OBJECT_AGG(key, value) AS settings
FROM settings
WHERE key IS NOT NULL;

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

Значение может быть NULL

С ключом строго: он не может быть NULL.

А вот значение может быть NULL.

Например:

key	value
theme	"dark"
timezone	NULL

Запрос:

SELECT JSONB_OBJECT_AGG(key, value) AS settings
FROM settings
WHERE key IS NOT NULL;

вернёт объект, где значение будет JSON null:

{
  "theme": "dark",
  "timezone": null
}

Это нормальная ситуация.

Ключ есть, просто значение неизвестно или не заполнено.

Порядок ключей в jsonb не важен

Новички иногда ожидают, что ключи в JSONB-объекте будут идти в том же порядке, что и строки в таблице.

Например, строки были такими:

key	value
theme	"dark"
lang	"en"
newsletter	true

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

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

Это нормально.

Тип jsonb хранит объект в своём внутреннем формате. Для объекта порядок ключей не имеет смысла: это не список, а словарь.

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

Пустая группа вернёт NULL, а не пустой объект

Ещё одна маленькая, но неприятная деталь: если агрегировать нечего, результатом будет NULL, а не {}.

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

В отчётах это иногда неудобно: приложение ждёт пустой объект, а получает NULL.

Тогда используйте COALESCE:

SELECT
    u.id,
    COALESCE(
        JSONB_OBJECT_AGG(s.key, s.value) FILTER (WHERE s.key IS NOT NULL),
        '{}'::jsonb
    ) AS prefs
FROM users u
LEFT JOIN user_settings s ON s.user_id = u.id
GROUP BY u.id;

Здесь есть несколько важных деталей.

LEFT JOIN сохраняет пользователей даже без настроек.

FILTER (WHERE s.key IS NOT NULL) не даёт агрегату собрать строку с пустым ключом.

COALESCE заменяет итоговый NULL на пустой JSONB-объект.

В результате у пользователя без настроек будет не NULL, а аккуратный объект:

{}

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

Разберём задачу как в реальном приложении.

Есть пользователи:

CREATE TABLE users (
    id integer,
    email text
);

Есть настройки:

CREATE TABLE user_settings (
    user_id integer,
    key text,
    value jsonb
);

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

SELECT
    u.id,
    u.email,
    COALESCE(
        JSONB_OBJECT_AGG(s.key, s.value) FILTER (WHERE s.key IS NOT NULL),
        '{}'::jsonb
    ) AS prefs
FROM users u
LEFT JOIN user_settings s ON s.user_id = u.id
GROUP BY u.id, u.email
ORDER BY u.id;

Результат:

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

Такой результат удобно отдавать в API: одна строка — один пользователь, а настройки уже собраны в готовый JSONB-объект.

JSONB_OBJECT_AGG не только для таблицы настроек

Важно понимать: JSONB_OBJECT_AGG не привязана именно к настройкам.

Она собирает объект из любых двух выражений: одно станет ключом, другое — значением.

Например, нужно для каждого пользователя собрать карту «статус заказа → сумма заказов в этом статусе».

Есть таблица заказов:

user_id	status	amount
1	paid	100
1	paid	250
1	cancelled	80
2	paid	500
2	shipped	300

Сначала нужно посчитать сумму по каждой паре user_id и status, а потом собрать объект.

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	totals_by_status
1	{"paid": 350, "cancelled": 80}
2	{"paid": 500, "shipped": 300}

Подзапрос здесь не для красоты.

Он заранее приводит данные к правильной форме: одна строка на один статус пользователя.

А уже внешний запрос превращает эти строки в JSONB-объект.

Главная ловушка: дубликаты ключей

Самая важная опасность в JSONB_OBJECT_AGG — дубликаты ключей внутри одной группы.

Представим данные:

user_id	key	value
1	color	"red"
1	color	"blue"

Что делать PostgreSQL? В JSONB-объекте не может быть двух одинаковых ключей color.

Запрос:

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

не упадёт с ошибкой. Он оставит одно значение для ключа color.

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

Даже если в вашем тесте «победило» последнее значение, это плохая опора. План запроса может измениться, данные могут начать читаться иначе, и результат станет другим.

Правильный подход: заранее решить, какая строка должна победить.

Как правильно выбрать победителя среди дублей

Допустим, в таблице настроек есть колонка updated_at, и при дубликатах нужно брать самую свежую настройку.

Для этого можно использовать 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
    WHERE key IS NOT NULL
    ORDER BY user_id, key, updated_at DESC
) latest
GROUP BY user_id;

Что здесь происходит:

1. Внутренний запрос сортирует строки так, чтобы самая свежая настройка оказалась первой.
2. DISTINCT ON (user_id, key) оставляет по одной строке на пару user_id и key.
3. Внешний запрос спокойно собирает JSONB-объект уже без дублей.

Теперь выбор победителя понятен и управляем: берём самую свежую запись.

То же самое можно сделать через оконную функцию ROW_NUMBER.

SELECT
    user_id,
    JSONB_OBJECT_AGG(key, value) AS prefs
FROM (
    SELECT
        user_id,
        key,
        value,
        ROW_NUMBER() OVER (
            PARTITION BY user_id, key
            ORDER BY updated_at DESC
        ) AS rn
    FROM user_settings
    WHERE key IS NOT NULL
) ranked
WHERE rn = 1
GROUP BY user_id;

Этот вариант длиннее, зато хорошо читается: нумеруем строки внутри каждой пары user_id и key, потом берём только первую.

JSONB_OBJECT_AGG или JSONB_AGG: что выбрать

В PostgreSQL есть ещё одна похожая функция — JSONB_AGG.

Из-за похожих названий их легко перепутать.

JSONB_OBJECT_AGG собирает объект:

SELECT JSONB_OBJECT_AGG(key, value) AS prefs
FROM user_settings
WHERE user_id = 1;

Результат:

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

А JSONB_AGG собирает массив:

SELECT JSONB_AGG(JSONB_BUILD_OBJECT('key', key, 'value', value)) AS prefs
FROM user_settings
WHERE user_id = 1;

Результат:

[
  {
    "key": "theme",
    "value": "dark"
  },
  {
    "key": "lang",
    "value": "en"
  }
]

Разница не косметическая. Это разные структуры данных.

Объект удобен, когда нужен быстрый доступ по имени поля:

SELECT prefs ->> 'theme' AS theme
FROM user_profiles;

Массив удобен, когда важен порядок, возможны повторы или у каждого элемента несколько полей.

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

[
  {
    "key": "theme",
    "old_value": "light",
    "new_value": "dark",
    "changed_at": "2026-06-01"
  },
  {
    "key": "lang",
    "old_value": "ru",
    "new_value": "en",
    "changed_at": "2026-06-02"
  }
]

Такую структуру лучше собирать через JSONB_AGG, а не через JSONB_OBJECT_AGG.

Простое правило выбора

Используйте JSONB_OBJECT_AGG, если вам нужен словарь:

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

То есть:

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

Используйте JSONB_AGG, если вам нужен список:

[
  {
    "key": "theme",
    "value": "dark"
  },
  {
    "key": "lang",
    "value": "en"
  }
]

То есть:

• порядок элементов важен;
• элементы могут повторяться;
• у каждого элемента несколько полей;
• результат похож на историю, ленту, список товаров или список событий.

ORDER BY внутри JSONB_AGG и JSONB_OBJECT_AGG

Для JSONB_AGG порядок часто важен, потому что массив — это упорядоченная структура.

Например:

SELECT JSONB_AGG(
    JSONB_BUILD_OBJECT('id', id, 'amount', amount)
    ORDER BY created_at DESC
) AS orders
FROM orders
WHERE user_id = 1;

Так можно собрать массив заказов от новых к старым.

С JSONB_OBJECT_AGG ситуация другая. Объект — это не список, и порядок ключей в jsonb не стоит использовать как часть логики.

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

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

Так запрос будет понятнее и безопаснее.

Тип значений: почему value лучше делать jsonb

В примерах выше колонка value имела тип jsonb.

Это удобно, потому что разные настройки могут иметь разные типы:

key	value
theme	"dark"
newsletter	true
page_size	50
filters	["new", "paid"]

Тогда объект сохранит настоящие JSON-типы:

{
  "theme": "dark",
  "newsletter": true,
  "page_size": 50,
  "filters": ["new", "paid"]
}

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

Например:

{
  "theme": "dark",
  "newsletter": "true",
  "page_size": "50"
}

Иногда это нормально. Но если приложение ждёт boolean или number, лучше хранить и собирать значения как jsonb.

Если исходное значение текстовое, но его нужно положить в JSONB как JSON-строку, можно использовать TO_JSONB.

SELECT JSONB_OBJECT_AGG(key, TO_JSONB(value)) AS settings
FROM settings
WHERE key IS NOT NULL;

Пример: собрать справочник для API

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

code	name
USD	US Dollar
EUR	Euro
UYU	Uruguayan Peso

Нужно отдать API объект, где ключ — код валюты, а значение — название.

SELECT JSONB_OBJECT_AGG(code, TO_JSONB(name)) AS currencies
FROM currencies
WHERE code IS NOT NULL;

Результат:

{
  "USD": "US Dollar",
  "EUR": "Euro",
  "UYU": "Uruguayan Peso"
}

Такой объект удобен на фронтенде: можно быстро получить название по коду валюты.

Пример: собрать вложенные объекты

Значением в JSONB_OBJECT_AGG может быть не только простая строка или число, но и целый JSONB-объект.

Например, нужно собрать карту товаров по их id:

SELECT JSONB_OBJECT_AGG(
    id,
    JSONB_BUILD_OBJECT(
        'name', name,
        'price', price,
        'in_stock', in_stock
    )
) AS products_by_id
FROM products
WHERE id IS NOT NULL;

Результат:

{
  "101": {
    "name": "Mouse",
    "price": 25,
    "in_stock": true
  },
  "102": {
    "name": "Keyboard",
    "price": 70,
    "in_stock": false
  }
}

Обратите внимание: ключи JSON-объекта всегда строки. Поэтому id в результате станет ключом "101", а не числом 101.

Это нормальное поведение JSON-объектов.

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

Очень частая ситуация: нужно вывести всех пользователей и их настройки.

Если написать JOIN, пользователи без настроек пропадут:

SELECT
    u.id,
    JSONB_OBJECT_AGG(s.key, s.value) AS prefs
FROM users u
JOIN user_settings s ON s.user_id = u.id
GROUP BY u.id;

Чтобы сохранить всех пользователей, нужен LEFT JOIN:

SELECT
    u.id,
    COALESCE(
        JSONB_OBJECT_AGG(s.key, s.value) FILTER (WHERE s.key IS NOT NULL),
        '{}'::jsonb
    ) AS prefs
FROM users u
LEFT JOIN user_settings s ON s.user_id = u.id
GROUP BY u.id;

Этот шаблон стоит запомнить. Он решает сразу три задачи:

• не теряет строки из основной таблицы;
• не пытается собрать объект с NULL-ключом;
• возвращает {} вместо NULL, если настроек нет.

JSONB_OBJECT_AGG в MySQL

В MySQL похожая функция называется JSON_OBJECTAGG.

Пример:

SELECT JSON_OBJECTAGG(`key`, `value`) AS settings
FROM settings;

По смыслу она делает то же самое: собирает JSON-объект из пар «ключ → значение».

С группировкой:

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

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

Практическое правило такое же: не допускайте NULL в ключах и заранее убирайте дубликаты, если они возможны.

А что в ClickHouse

В ClickHouse обычно не собирают JSONB-объект так же, как в PostgreSQL, потому что там другая модель типов и нет прямого полного аналога JSONB_OBJECT_AGG.

Для похожих задач часто используют тип Map или собирают пары в массивы через агрегатные функции.

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

Но если вы работаете именно в PostgreSQL и вам нужен компактный JSONB-документ на группу, JSONB_OBJECT_AGG — самый прямой и выразительный путь.

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

Самая частая ошибка — забыть про NULL в ключах.

Плохо:

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

Лучше:

SELECT JSONB_OBJECT_AGG(key, value) AS settings
FROM settings
WHERE key IS NOT NULL;

Вторая ошибка — не подумать про дубликаты ключей.

Плохо:

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

Лучше сначала явно выбрать одну строку на каждый ключ:

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

Третья ошибка — ждать {} там, где агрегат вернёт NULL.

Если пустой объект важен, используйте COALESCE:

SELECT
    COALESCE(JSONB_OBJECT_AGG(key, value), '{}'::jsonb) AS settings
FROM settings
WHERE key IS NOT NULL;

Четвёртая ошибка — использовать объект там, где нужен массив.

Если важен порядок, история или повторяющиеся элементы, чаще подходит JSONB_AGG, а не JSONB_OBJECT_AGG.

Практический шаблон

Для пользовательских настроек удобно запомнить такой запрос:

SELECT
    u.id,
    u.email,
    COALESCE(
        JSONB_OBJECT_AGG(s.key, s.value) FILTER (WHERE s.key IS NOT NULL),
        '{}'::jsonb
    ) AS prefs
FROM users u
LEFT JOIN user_settings s ON s.user_id = u.id
GROUP BY u.id, u.email
ORDER BY u.id;

Это хороший базовый вариант для отчёта или API:

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

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

Главное

JSONB_OBJECT_AGG в PostgreSQL собирает JSONB-объект из строк.

Базовая форма:

SELECT JSONB_OBJECT_AGG(key, value) AS settings
FROM settings
WHERE key IS NOT NULL;

С группировкой:

SELECT
    user_id,
    JSONB_OBJECT_AGG(key, value) AS prefs
FROM user_settings
WHERE key IS NOT NULL
GROUP BY user_id;

Используйте эту функцию, когда нужно превратить строки вида «ключ → значение» в готовый JSONB-объект:

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

Главные правила:

• ключ не может быть NULL;
• значение может быть NULL;
• пустая группа вернёт NULL, а не {};
• порядок ключей в jsonb не имеет смысла;
• дубликаты ключей нужно разруливать заранее;
• для словаря подходит JSONB_OBJECT_AGG, для списка — JSONB_AGG.

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