Aggregating Rows Into JSON With jsonb_agg in PostgreSQL

The last mile of many APIs is a query that returns rows, an application that loops over them, and a serializer that assembles JSON. PostgreSQL can do the assembling itself, which saves round trips and leaves the application with a single value to pass along. It is also an easy way to produce subtly wrong output, because aggregation and joins interact. This article covers the builders, the aggregates, the fan-out bug that burns almost everyone once, and a measurement of three ways to shape the same document.

The builders

Four functions create JSON values from SQL values:

• jsonb_build_object(k1, v1, k2, v2, ...) makes an object from alternating keys and values. An odd number of arguments is an error: jsonb_build_object('a') raised argument list must have even number of elements.

• jsonb_build_array(a, b, ...) makes an array: jsonb_build_array(1, 'two', null, true) gave [1, "two", null, true].

• to_jsonb(anything) converts a value, a composite or a whole row. For a row from c_customers it returned {"id": 1, "name": "Customer 1"}, using the column names as keys.

• jsonb_strip_nulls, applied afterward, removes keys whose value is null.

Types convert in the way JSON allows. I built one object to see what each type becomes:

select jsonb_build_object('d', date '2026-10-01', 'ts', timestamptz '2026-10-01 12:00:00+00',
  'n', 1.50::numeric, 'big', 12345678901234567890::numeric, 'arr', array[1,2], 'nulltxt', null::text);

{"d": "2026-10-01", "n": 1.50, "ts": "2026-10-01T12:00:00+00:00", "arr": [1, 2], "big": 12345678901234567890, "nulltxt": null}

Dates and timestamps become ISO 8601 strings, as JSON has no date type (the consequences are in JSON dates lack a date type). The numeric kept its trailing zero and the twenty digit integer survived exactly, which is a PostgreSQL strength and a hazard if the reader is a JavaScript client, as described in how JSON handles big numbers. SQL NULL becomes JSON null. Arrays become JSON arrays.

The aggregates

jsonb_agg(expr) gathers one value per row into an array. jsonb_object_agg(key, value) makes an object. Both accept ORDER BY inside the call and FILTER (WHERE ...) after it:

select jsonb_agg(id order by id desc) filter (where id > 1) from orders;   -- [3, 2]
select jsonb_object_agg(id, doc #>> '{customer,name}') from orders;       -- {"1": "Ada", "2": "Grace", "3": "Ada"}

Two properties matter every time:

1. Order. An array has an order, and jsonb_agg without ORDER BY takes whatever order the rows arrive in. That is usually insertion or scan order, which can change when the plan changes. If the order is part of the meaning, put it in the call: jsonb_agg(x order by x.position). The same is true of string_agg and array_agg.

2. Empty input. With no rows, the result is SQL NULL. select jsonb_agg(x) from (select 1 as x where false) x returned a null, and coalesce(jsonb_agg(x), '[]') returned []. A client that expects an array and receives null for "no items" is one of the common causes of a Cannot read properties of null error in a front end. For a grouped query, a group always has at least one row, so the NULL appears only for whole-table aggregates or in correlated subqueries.

distinct works inside the call: jsonb_agg(distinct e) on ["b","a","b","c"]'s elements returned ["a", "b", "c"], sorted as a side effect.

PostgreSQL 16 added the SQL-standard names. json_arrayagg(id order by id) returned [1, 2, 3] and json_objectagg('k' || id : id) returned { "k1" : 1, "k2" : 2, "k3" : 3 }, with an absent on null and null on null clause that the older functions lacked: json_object('a': 1, 'b': null absent on null) dropped the b. They return json, not jsonb, so the key order and spacing are those of the call.

A nested document from three tables

Here is the typical job: one JSON document per order, with its customer and its items.

select jsonb_build_object(
         'id', o.id,
         'placed', o.placed,
         'customer', jsonb_build_object('id', c.id, 'name', c.name),
         'items', (select coalesce(jsonb_agg(jsonb_build_object('sku', i.sku, 'qty', i.qty, 'price', i.price)
                                             order by i.id), '[]')
                   from c_items i where i.order_id = o.id))
from c_orders o join c_customers c on c.id = o.customer_id
where o.id = 1;

The result had id, placed, a customer object and an items array of four objects. The items are built in a subquery that aggregates only the child table, and the customer, which is one row per order, is built in the parent query. That separation is the whole technique: aggregate each one-to-many relationship on its own, then assemble.

The fan-out bug

The mistake is to join the child tables to the parent first and aggregate afterward. With one child table the result is fine:

 id |           skus           | count 
----+--------------------------+-------
  1 | ["S2", "S3", "S4", "S5"] |     4

With two one-to-many relationships joined at once, each child row is multiplied by the rows of the other. I joined c_items twice to stand in for items and, say, shipments, both four per order:

select o.id, jsonb_agg(i.sku) as skus
from c_orders o
join c_items i on i.order_id = o.id
join c_items j on j.order_id = o.id
where o.id = 1 group by o.id;

The skus array came back with sixteen elements, each sku four times, and a second aggregate over o.placed repeated the date sixteen times. No error, plausible output, wrong numbers. Totals computed this way (sum(i.price)) are inflated by the same factor. The remedy is the subquery or lateral pattern above, one aggregate per relationship, or jsonb_agg(distinct ...) as a patch for scalars (it hides the problem for duplicates that should exist, such as two items with the same sku).

Three shapes, measured

The same 20,000 documents, built three ways, with the same total size (3,798,810 characters of text):

join + group by                       196 ms
cross join lateral (subquery)         251 ms
correlated subquery in select list    306 ms

The join with GROUP BY was fastest here because the planner can read c_items once through a hash or merge join. The correlated subquery runs once per order, through the index on order_id. For a few thousand parents the difference is nothing. For millions it grows, and in that range the right question is whether the database should be producing this JSON at all. Use explain (analyze) on your own data; the order can flip when children are numerous and parents few.

When the child rows are already the shape you want, there is a shortcut: jsonb_agg(to_jsonb(i)) collects whole rows as objects, and to_jsonb(i) - 'order_id' removes a key you do not want to send. Grouping all 80,000 item rows by order with jsonb_agg(to_jsonb(i)) took 8 ms, and the json_agg(to_json(i)) version took 10 ms, because there is no per-field object building. Prefer an explicit jsonb_build_object for anything that leaves your system, since to_jsonb(row) exposes every column, and adding a column to the table adds a field to the API without anyone deciding to.

Grouping into objects

jsonb_object_agg turns rows into a lookup: select jsonb_object_agg(sku, total) from (select sku, sum(qty) total ...) produced {"S2": 3, "S3": 8, "S4": 15, ...} for five orders. Keys must be unique per call. A duplicate key does not raise an error, since jsonb keeps the last value silently, so group first. Keys are always strings, so a number key becomes "1" and the lookup doc -> '1' needs the quotes.

To get a keyed array of arrays, group by the key and aggregate: select doc #>> '{customer,name}' as customer, jsonb_agg(id order by id) from orders group by 1 gave Ada | [1, 3] and Grace | [2].

Reading the result in the application

A driver returns the aggregate as a parsed object or as text, depending on the library. In PostgreSQL's wire protocol, jsonb arrives as text, and the driver parses it. Node's pg and Python's psycopg parse it by default; PHP's PDO does not, so you call json_decode. If the application only forwards the result, a text cast (doc::text) skips the parse and re-serialize, and saves memory on big payloads. Remember that jsonb normalizes key order and spacing; if the client expects the keys in the order you wrote them, use json/json_build_object instead. A JSON formatter is handy to inspect a payload you captured.

When the database should not build the JSON

Assembling JSON in SQL is convenient and has costs.

Huge result sets. One jsonb_agg over a million rows builds the whole array in memory on the server, then ships it as a single value. Stream rows and let the application, or JSON Lines, carry the structure; JSON Lines for data pipelines and log files covers the format.

Logic that changes often. Document shapes tied to SQL strings are harder to test and review than a serializer in application code, with types and unit tests. A view that returns the document helps, and so does a function; both put the shape in version control.

Many consumers with different needs. If five clients want five shapes, five queries is not better than one data model and five serializers.

Ordering you cannot guarantee. If you forget the ORDER BY inside jsonb_agg, the output changes with the plan. That is a bug you will find in production.

For the aggregate reference, see the PostgreSQL manual: aggregate functions and JSON creation functions. The guide to the rest of the type is working with JSONB in PostgreSQL from first query to index.