JSONB_AGG in PostgreSQL: Collect Grouped Rows into a JSON Array for JSON APIs

jsonb_agg is an aggregate function that gathers the values of every row in a group into a single JSON array. Where array_agg produces an array of one type, jsonb_agg accepts anything, including whole table rows, which makes it the building block of JSON APIs: one query and you have a document with nested collections.

Syntax and a basic example

The signature matches any aggregate: jsonb_agg(item ORDER BY created_at), usually next to a GROUP BY. The item is most often a row_to_json(...) or a jsonb_build_object(...) so that each array element is an object rather than a bare scalar.

SELECT
    user_id,
    jsonb_agg(amount ORDER BY created_at) AS amounts
FROM orders
GROUP BY user_id;

A few behaviors worth knowing:

• Element order is not guaranteed without an ORDER BY inside the aggregate. Never rely on table row order.
• The result type is always jsonb, regardless of the inner expression's type.
• NULL rows land in the array as JSON null. To drop them, add FILTER (WHERE expr IS NOT NULL).
• An empty group yields NULL, not an empty array []. That is the main trap, covered below.

An array of objects with ORDER BY and FILTER

Scalars are the warm-up. In practice you pack objects into the array: for each user, a list of their orders, ordered and filtered right in the call.

SELECT
    u.id,
    u.email,
    jsonb_agg(
        jsonb_build_object('id', o.id, 'amount', o.amount, 'at', o.created_at)
        ORDER BY o.created_at DESC
    ) FILTER (WHERE o.status = 'paid') AS paid_orders
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email;

jsonb_build_object gives full control over key names, while to_jsonb(o) takes the whole row with every column. Pick one based on whether you need a strict response contract.

A parent document with a nested array

The most common JSON API pattern is a parent plus a nested collection of children. You assemble it with a correlated subquery: the outer query walks users, the inner one aggregates each user's orders with jsonb_agg.

SELECT jsonb_build_object(
    'id', u.id,
    'email', u.email,
    'orders', (
        SELECT jsonb_agg(
            jsonb_build_object('id', o.id, 'amount', o.amount)
            ORDER BY o.created_at
        )
        FROM orders o
        WHERE o.user_id = u.id
    )
) AS user_doc
FROM users u;

One pass, and each result row is ready to ship to the client. No N+1 in the application, no tree assembly in code.

The empty group: COALESCE to '[]'

Here is that trap. If a user has no orders, the subquery returns NULL, not [], and a frontend expecting an array breaks. Wrap it in COALESCE:

SELECT jsonb_build_object(
    'id', u.id,
    'orders', COALESCE(
        (
            SELECT jsonb_agg(jsonb_build_object('id', o.id) ORDER BY o.created_at)
            FROM orders o
            WHERE o.user_id = u.id
        ),
        '[]'::jsonb
    )
) AS user_doc
FROM users u;

Gotcha: FILTER can also turn a non-empty group into NULL. If no row passes the filter, jsonb_agg(...) FILTER (...) returns NULL, not []. So COALESCE(..., '[]'::jsonb) is needed whenever a FILTER is present, even when the group definitely has rows.

The same rule applies to a hierarchy: collect each manager's reports into an array and guarantee [] for the childless ones.

SELECT jsonb_build_object(
    'manager', m.name,
    'reports', COALESCE(
        (
            SELECT jsonb_agg(e.name ORDER BY e.salary DESC)
            FROM employees e
            WHERE e.manager_id = m.id
        ),
        '[]'::jsonb
    )
) AS team
FROM employees m;

MySQL and ClickHouse

In MySQL 8 the equivalent is JSON_ARRAYAGG, paired with JSON_OBJECT for objects:

-- MySQL 8: array of objects, one per order
SELECT user_id,
       JSON_ARRAYAGG(JSON_OBJECT('id', id, 'amount', amount)) AS orders
FROM orders
GROUP BY user_id;

Differences worth remembering:

• JSON_ARRAYAGG has no built-in ORDER BY; impose order with a pre-sorted subquery.
• There is no FILTER either; filter with a WHERE clause or a conditional expression inside.
• In ClickHouse a similar result comes from groupArray over a tuple, or pairing it with toJSONString.

When you need a predictable JSON document with nested collections and a guaranteed [] instead of NULL, PostgreSQL with jsonb_agg ... ORDER BY ... FILTER plus COALESCE stays the most direct tool.