JSON_AGG и JSONB_AGG в PostgreSQL: как собрать строки в JSON-массив

Представьте типичную задачу для API.

В базе есть пользователи и их заказы. В SQL это две таблицы: users и orders. Один пользователь может иметь много заказов.

Обычный JOIN возвращает плоскую таблицу:

SELECT
    u.id,
    u.email,
    o.id AS order_id,
    o.amount
FROM users u
JOIN orders o ON o.user_id = u.id;

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

id | email           | order_id | amount
---+-----------------+----------+--------
42 | ann@example.com | 1001     | 199.00
42 | ann@example.com | 1002     | 349.00
42 | ann@example.com | 1003     |  89.00

Для базы это нормальная форма: одна строка — один заказ.

Но фронтенду или API часто нужен другой формат:

{
  "id": 42,
  "email": "ann@example.com",
  "orders": [
    { "id": 1001, "amount": 199.00 },
    { "id": 1002, "amount": 349.00 },
    { "id": 1003, "amount": 89.00 }
  ]
}

То есть не «три строки с повторяющимся пользователем», а один пользователь со списком заказов.

Можно, конечно, получить плоскую выборку и собрать JSON в коде приложения: завести словари, циклы, группировки, аккуратно не сломать порядок, не получить лишний запрос на каждый заказ. Но PostgreSQL умеет сделать это прямо в SQL.

Для этого есть агрегатные функции JSON_AGG и JSONB_AGG.

Они берут несколько строк внутри группы и сворачивают их в один JSON-массив.

Зачем нужны JSON_AGG и JSONB_AGG

Обычные агрегатные функции вы уже могли видеть:

SELECT
    user_id,
    COUNT(*) AS orders_count
FROM orders
GROUP BY user_id;

COUNT берёт много строк и возвращает одно число.

JSONB_AGG работает похожим образом, только возвращает не число, а массив:

SELECT
    user_id,
    JSONB_AGG(id) AS order_ids
FROM orders
GROUP BY user_id;

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

user_id | order_ids
--------+--------------------
42      | [1001, 1002, 1003]
77      | [1004, 1005]

То есть вместо нескольких строк заказов мы получили одну строку на пользователя и JSON-массив его заказов.

Это особенно полезно, когда SQL-запрос сразу готовит ответ для API.

JSON_AGG против JSONB_AGG

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

• JSON_AGG;
• JSONB_AGG.

Обе собирают значения в JSON-массив.

Разница в типе результата.

JSON_AGG возвращает значение типа json.

JSONB_AGG возвращает значение типа jsonb.

Тип json ближе к текстовому JSON: PostgreSQL хранит его в виде JSON-документа и сохраняет некоторые особенности исходного представления.

Тип jsonb — это разобранное бинарное представление. Оно удобнее для дальнейшей работы: по нему можно эффективнее искать, фильтровать, сравнивать значения, строить индексы.

В реальных проектах чаще выбирают JSONB_AGG, особенно если результат потом ещё будет обрабатываться в PostgreSQL.

Простой пример:

SELECT
    JSONB_AGG(o.id) AS order_ids
FROM orders o;

Результат:

[1001, 1002, 1003]

Если нужно просто отдать JSON из базы в API, оба варианта могут подойти. Но для PostgreSQL-проектов jsonb обычно практичнее.

Самый простой пример: массив значений

Допустим, нам нужен список сумм заказов по каждому пользователю.

SELECT
    user_id,
    JSONB_AGG(amount) AS amounts
FROM orders
GROUP BY user_id;

Результат:

user_id | amounts
--------+------------------------
42      | [199.00, 349.00, 89.00]

Это уже похоже на вложенную структуру.

Но массив чисел в API нужен не так часто. Обычно хочется массив объектов: у каждого заказа есть идентификатор, сумма, статус, дата.

Для этого нужен следующий инструмент.

jsonb_build_object: собрать объект из полей

Функция jsonb_build_object собирает JSON-объект из пар «ключ — значение».

Пример:

SELECT jsonb_build_object(
    'id', 1001,
    'amount', 199.00,
    'status', 'paid'
) AS order_json;

Результат:

{
  "id": 1001,
  "amount": 199.00,
  "status": "paid"
}

Теперь соединим jsonb_build_object и JSONB_AGG.

SELECT
    user_id,
    JSONB_AGG(
        jsonb_build_object(
            'id', id,
            'amount', amount,
            'status', status
        )
    ) AS orders
FROM orders
GROUP BY user_id;

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

[
  { "id": 1001, "amount": 199.00, "status": "paid" },
  { "id": 1002, "amount": 349.00, "status": "shipped" }
]

Это уже почти готовый кусок ответа API.

Имена полей можно выбирать самому

Важное удобство jsonb_build_object: имена полей в JSON не обязаны совпадать с именами столбцов в таблице.

Например, в базе столбец называется created_at, а в API вы хотите поле createdAt.

SELECT
    user_id,
    JSONB_AGG(
        jsonb_build_object(
            'orderId', id,
            'totalAmount', amount,
            'currentStatus', status,
            'createdAt', created_at
        )
    ) AS orders
FROM orders
GROUP BY user_id;

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

В базе удобно использовать одни имена, а наружу отдавать другие.

ORDER BY внутри JSONB_AGG

У агрегатов есть важная особенность: порядок элементов внутри массива сам по себе не гарантирован.

Если вы написали так:

SELECT
    user_id,
    JSONB_AGG(id) AS order_ids
FROM orders
GROUP BY user_id;

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

Для API это плохая история. Клиент обычно ждёт предсказуемый порядок: например, новые заказы первыми.

Поэтому сортировку нужно указывать прямо внутри агрегата:

SELECT
    user_id,
    JSONB_AGG(id ORDER BY created_at DESC) AS order_ids
FROM orders
GROUP BY user_id;

Теперь элементы внутри массива будут отсортированы по created_at от новых к старым.

Для массива объектов это работает так же:

SELECT
    user_id,
    JSONB_AGG(
        jsonb_build_object(
            'id', id,
            'amount', amount,
            'status', status
        )
        ORDER BY created_at DESC
    ) AS orders
FROM orders
GROUP BY user_id;

Обратите внимание: ORDER BY находится внутри JSONB_AGG.

Это принципиально.

Внешний ORDER BY не сортирует элементы массива

Новички часто пишут так:

SELECT
    user_id,
    JSONB_AGG(id) AS order_ids
FROM orders
GROUP BY user_id
ORDER BY user_id;

Этот ORDER BY сортирует строки итогового результата.

Например:

user_id | order_ids
--------+--------------------
42      | [1003, 1001, 1002]
77      | [1005, 1004]

Пользователи будут идти по порядку, но элементы внутри order_ids не обязаны быть отсортированы.

Чтобы отсортировать именно массив, нужно писать так:

SELECT
    user_id,
    JSONB_AGG(id ORDER BY created_at DESC) AS order_ids
FROM orders
GROUP BY user_id
ORDER BY user_id;

Здесь два разных порядка:

• внешний ORDER BY user_id сортирует строки результата;
• внутренний ORDER BY created_at DESC сортирует элементы внутри каждого JSON-массива.

Собираем пользователя со списком заказов

Теперь соберём полноценный ответ: один пользователь и массив его заказов.

SELECT
    u.id,
    u.email,
    u.country,
    JSONB_AGG(
        jsonb_build_object(
            'id', o.id,
            'amount', o.amount,
            'status', o.status,
            'createdAt', o.created_at
        )
        ORDER BY o.created_at DESC
    ) AS orders
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.id = 42
GROUP BY u.id, u.email, u.country;

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

{
  "id": 42,
  "email": "ann@example.com",
  "country": "DE",
  "orders": [
    {
      "id": 1003,
      "amount": 89.00,
      "status": "paid",
      "createdAt": "2026-03-10T12:30:00Z"
    },
    {
      "id": 1002,
      "amount": 349.00,
      "status": "shipped",
      "createdAt": "2026-03-08T09:15:00Z"
    }
  ]
}

Пользователь повторялся в нескольких строках после JOIN, но GROUP BY собрал его обратно в одну строку, а JSONB_AGG сложил заказы в массив.

Один готовый JSON-объект через jsonb_build_object

Иногда удобно вернуть не отдельные столбцы, а один готовый JSON-документ.

SELECT jsonb_build_object(
    'id', u.id,
    'email', u.email,
    'country', u.country,
    'orders', JSONB_AGG(
        jsonb_build_object(
            'id', o.id,
            'amount', o.amount,
            'status', o.status
        )
        ORDER BY o.created_at DESC
    )
) AS payload
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.id = 42
GROUP BY u.id, u.email, u.country;

Результат будет в одном столбце payload.

Примерно так:

{
  "id": 42,
  "email": "ann@example.com",
  "country": "DE",
  "orders": [
    { "id": 1003, "amount": 89.00, "status": "paid" },
    { "id": 1002, "amount": 349.00, "status": "shipped" }
  ]
}

Такой подход часто используют, когда PostgreSQL напрямую готовит ответ для API или для внутреннего сервиса.

Передать в JSONB_AGG всю строку

Есть короткий способ: передать в JSONB_AGG всю строку таблицы.

SELECT
    JSONB_AGG(o ORDER BY o.id) AS orders
FROM orders o;

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

Это удобно для быстрых внутренних запросов, отладки, выгрузок и админских инструментов.

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

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

Например, не стоит случайно отдавать наружу служебные поля вроде internal_comment, deleted_at или admin_note.

LEFT JOIN и неприятный null в массиве

Теперь важная ловушка.

Допустим, мы хотим получить всех пользователей, даже тех, у кого нет заказов.

Для этого нужен LEFT JOIN:

SELECT
    u.id,
    u.email,
    JSONB_AGG(
        jsonb_build_object(
            'id', o.id,
            'amount', o.amount
        )
    ) AS orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email;

Проблема в том, что для пользователя без заказов LEFT JOIN всё равно создаёт одну строку, но поля заказа будут равны NULL.

В итоге можно получить массив с объектом, где все значения пустые:

[
  { "id": null, "amount": null }
]

А если агрегировать просто o.id, можно получить:

[null]

Для API это обычно мусор. Если заказов нет, мы хотим пустой массив:

[]

FILTER: не добавлять лишние значения в агрегат

Чтобы не тащить в массив пустые строки после LEFT JOIN, используйте FILTER.

SELECT
    u.id,
    u.email,
    JSONB_AGG(
        jsonb_build_object(
            'id', o.id,
            'amount', o.amount
        )
        ORDER BY o.created_at DESC
    ) FILTER (WHERE o.id IS NOT NULL) AS orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email;

Часть:

FILTER (WHERE o.id IS NOT NULL)

говорит агрегату:

Добавляй в массив только те строки, где заказ действительно есть.

Но есть ещё одна тонкость.

Если после фильтрации не осталось ни одной строки, JSONB_AGG вернёт не пустой массив, а NULL.

То есть получится:

{
  "id": 42,
  "email": "ann@example.com",
  "orders": null
}

А многие клиенты API ждут именно массив. Даже если он пустой.

COALESCE: вернуть пустой массив вместо NULL

Чтобы вместо NULL получить [], используйте COALESCE.

SELECT
    u.id,
    u.email,
    COALESCE(
        JSONB_AGG(
            jsonb_build_object(
                'id', o.id,
                'amount', o.amount
            )
            ORDER BY o.created_at DESC
        ) FILTER (WHERE o.id IS NOT NULL),
        '[]'::jsonb
    ) AS orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email;

Теперь если у пользователя нет заказов, в orders будет пустой JSON-массив:

[]

Для API это почти всегда лучше, чем null.

null обычно означает «значение неизвестно» или «поля нет».

А пустой массив означает понятную вещь: «список есть, но он пустой».

Пример с руководителями и подчинёнными

JSONB_AGG полезен не только для пользователей и заказов.

Допустим, есть таблица сотрудников. У каждого сотрудника может быть руководитель.

CREATE TABLE employees (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    manager_id bigint REFERENCES employees(id),
    name text NOT NULL,
    salary numeric(10, 2) NOT NULL
);

Можно собрать каждого руководителя и список его подчинённых:

SELECT
    m.id,
    m.name AS manager_name,
    JSONB_AGG(
        jsonb_build_object(
            'id', e.id,
            'name', e.name,
            'salary', e.salary
        )
        ORDER BY e.salary DESC
    ) AS reports
FROM employees m
JOIN employees e ON e.manager_id = m.id
GROUP BY m.id, m.name;

Получится одна строка на руководителя и массив сотрудников внутри.

Это тот же принцип: много строк подчинённых сворачиваются в один JSON-массив.

Когда JSONB_AGG особенно полезен

JSONB_AGG хорошо подходит, когда у вас есть связь «один ко многим».

Например:

• пользователь и его заказы;
• заказ и его позиции;
• курс и его уроки;
• пост и комментарии;
• руководитель и сотрудники;
• категория и товары;
• проект и задачи.

Вместо того чтобы собирать вложенную структуру в приложении, можно сразу получить её из SQL.

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

Чем JSONB_AGG отличается от ARRAY_AGG

В PostgreSQL есть ещё ARRAY_AGG.

Она собирает значения в обычный SQL-массив.

Например:

SELECT
    user_id,
    ARRAY_AGG(id ORDER BY created_at DESC) AS order_ids
FROM orders
GROUP BY user_id;

Результат будет массивом PostgreSQL, а не JSON.

Это удобно, если вы дальше работаете с данными внутри SQL.

Но если вы готовите ответ для API, JSON-формат обычно удобнее:

SELECT
    user_id,
    JSONB_AGG(id ORDER BY created_at DESC) AS order_ids
FROM orders
GROUP BY user_id;

Главное отличие простое:

• ARRAY_AGG — SQL-массив;
• JSONB_AGG — JSON-массив.

Не забывайте про GROUP BY

JSONB_AGG — агрегатная функция. Поэтому, если рядом с ней есть обычные столбцы, эти столбцы должны быть в GROUP BY.

Например, так нельзя:

SELECT
    u.id,
    u.email,
    JSONB_AGG(o.id) AS order_ids
FROM users u
JOIN orders o ON o.user_id = u.id;

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

Правильно:

SELECT
    u.id,
    u.email,
    JSONB_AGG(o.id ORDER BY o.created_at DESC) AS order_ids
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email;

Группировка говорит:

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

Аккуратнее с большими массивами

JSONB_AGG удобен, но не стоит забывать про размер результата.

Если у пользователя 10 заказов — отлично.

Если у пользователя 1000 заказов — всё ещё может быть нормально, зависит от задачи.

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

Для больших списков часто лучше использовать пагинацию:

SELECT
    JSONB_AGG(
        jsonb_build_object(
            'id', id,
            'amount', amount
        )
        ORDER BY created_at DESC
    ) AS orders
FROM (
    SELECT id, amount, created_at
    FROM orders
    WHERE user_id = 42
    ORDER BY created_at DESC
    LIMIT 50
) s;

Здесь мы сначала берём последние 50 заказов, а уже потом собираем их в JSON.

Так ответ API остаётся быстрым и предсказуемым.

MySQL: JSON_ARRAYAGG и JSON_OBJECT

В MySQL похожая задача решается через JSON_ARRAYAGG и JSON_OBJECT.

Пример:

SELECT
    user_id,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'id', id,
            'amount', amount
        )
    ) AS orders
FROM orders
GROUP BY user_id;

JSON_OBJECT собирает объект на одну строку, а JSON_ARRAYAGG собирает эти объекты в массив.

В MySQL нет разделения на json и jsonb, как в PostgreSQL. Там используется тип JSON.

Главная идея та же: много строк превращаются в один JSON-массив.

Но детали синтаксиса и поведение сортировки отличаются, поэтому запросы из PostgreSQL нельзя просто копировать в MySQL без адаптации.

ClickHouse: другой подход

В ClickHouse нет прямого аналога JSONB_AGG в стиле PostgreSQL.

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

Там массивы обычно собирают через функции вроде groupArray, а JSON часто формируют уже на следующем шаге или используют специальные функции работы с JSON-строками.

То есть для задачи «собрать удобный вложенный JSON для API» PostgreSQL обычно приятнее и прямолинейнее.

Связка jsonb_build_object, JSONB_AGG, ORDER BY, FILTER и COALESCE даёт очень выразительный инструмент прямо внутри SQL.

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

Вот хороший шаблон, который часто можно адаптировать под реальные задачи:

SELECT jsonb_build_object(
    'id', u.id,
    'email', u.email,
    'orders', COALESCE(
        JSONB_AGG(
            jsonb_build_object(
                'id', o.id,
                'amount', o.amount,
                'status', o.status,
                'createdAt', o.created_at
            )
            ORDER BY o.created_at DESC
        ) FILTER (WHERE o.id IS NOT NULL),
        '[]'::jsonb
    )
) AS payload
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.id = 42
GROUP BY u.id, u.email;

В этом запросе есть почти всё важное:

• jsonb_build_object собирает объект пользователя;
• JSONB_AGG собирает заказы в массив;
• внутренний ORDER BY задаёт порядок заказов;
• FILTER убирает пустые строки после LEFT JOIN;
• COALESCE возвращает [], если заказов нет.

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

Главное

JSON_AGG и JSONB_AGG собирают несколько строк в один JSON-массив.

JSON_AGG возвращает тип json, а JSONB_AGG возвращает jsonb. В PostgreSQL для большинства практических задач удобнее JSONB_AGG.

Если нужно собрать массив объектов, используйте jsonb_build_object внутри JSONB_AGG.

Порядок элементов в массиве не гарантирован сам по себе. Чтобы порядок был стабильным, пишите ORDER BY внутри агрегата.

Внешний ORDER BY сортирует строки результата, но не элементы внутри JSON-массива.

При LEFT JOIN используйте FILTER, чтобы не получить null или объект с пустыми значениями в массиве.

Если у группы нет элементов, JSONB_AGG вернёт NULL. Чтобы API получил пустой массив, оборачивайте агрегат в COALESCE(..., '[]'::jsonb).

JSONB_AGG особенно полезен для связей «один ко многим»: пользователь и заказы, заказ и позиции, пост и комментарии, руководитель и сотрудники.

Главная мысль простая: если приложению нужен вложенный JSON, не всегда нужно собирать его вручную в коде. PostgreSQL умеет сделать это сам — красиво, понятно и одним SQL-запросом.