Aggregation

Why does json_agg return null instead of an empty array?

By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17

Because an aggregate over zero rows has nothing to build from, so it returns null. Wrap it: coalesce(jsonb_agg(x), '[]'::jsonb).

That fixes one of the two ways this goes wrong, and the second one is worse because the first fix looks like it is working. After a LEFT JOIN with no matching child rows, the aggregate does not see zero rows. It sees one row of nulls, returns [null], and coalesce never fires because the result was never null. Both are below.

Those tables are customers, one row per customer, and orders, one row per order with a customer_id pointing back. Eight customers in this sandbox have never ordered anything, so the second bug is reproducible here.

You can edit and run two of the queries below in your browser, and there are two exercises at the end. See our other guides to aggregating query output if you came here with a different aggregation problem.

Schema · ecommerce2 tables? = nullable

customers

idinteger

nametext

emailtext

city?text

countrytext

created_attimestamptz

is_activeboolean

orders

idinteger

customer_id?integer

ordered_attimestamptz

statustext

total_amountnumeric

SELECT (SELECT count(*) FROM customers) AS customer_rows,
       (SELECT count(*) FROM orders) AS order_rows,
       (SELECT count(*) FROM customers c
         WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)
       ) AS customers_with_no_orders;

customer_rows	order_rows	customers_with_no_orders
70	200	8

the two tables in our sandbox, and how many customers have never ordered

Why is json_agg null when there are no rows?

Because that is what the general-purpose aggregates do with an empty input. There is no partial result to return, so PostgreSQL returns null rather than inventing an identity value, and count is the exception the manual calls out. It is one rule, not a JSON quirk:

SELECT jsonb_agg(id)::text AS jsonb_agg,
       array_agg(id)::text AS array_agg,
       string_agg(id::text, ',') AS string_agg,
       sum(id)::text AS sum,
       count(*) AS count_star
FROM orders
WHERE false;

jsonb_agg	array_agg	string_agg	sum	count_star
NULL	NULL	NULL	NULL	0

five aggregates over zero rows. Only count returns a value

sum of no rows is null, not zero, and PostgreSQL's documentation names this case directly, including that array_agg returns null, not an empty array, when there are no input rows. Once you know the rule covers that whole table of functions, the JSON version stops being surprising and the fix is the same fix.

How do you get an empty array instead?

Wrap the aggregate in coalesce with an empty JSON array as the fallback. Keep the cast for the reader. The parser does not need it: on its own '[]' is an untyped literal, and here it is only coalesce's other argument that settles it as jsonb.

SELECT coalesce(jsonb_agg(id), '[]'::jsonb)::text AS fixed
FROM orders
WHERE false;

fixed
[]

the same empty input, with a fallback

SELECT coalesce(jsonb_agg(id), '[]'::jsonb)::text AS fixed
FROM orders
WHERE false;

Why is the array [null] after a LEFT JOIN?

Because a LEFT JOIN that finds no match still produces a row. The child columns in it are null, the aggregate receives one value, and that value is null. Customer 1 has five orders and customer 63 has none:

SELECT c.id, c.name, jsonb_agg(o.id)::text AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.id IN (1, 63)
GROUP BY c.id, c.name
ORDER BY c.id;

id	name	orders
1	Alice Nguyen	[1, 63, 64, 101, 149]
63	Luna Grant	[null]

a customer with orders, and a customer whose empty list arrived as a one-element array containing null

[null] is a JSON array of length one. A client that checks the length before rendering sees one item. A client that maps over it gets a null where an object was expected. Both failures happen at the client, well away from the query that caused them.

How do you fix [null] when coalesce does not?

Stop the null reaching the aggregate, rather than replacing the aggregate's result. Wrapping a grouped LEFT JOIN aggregate in coalesce changes nothing, because every group holds at least one row, so the null it guards against never appears as the aggregate's result:

SELECT c.id, c.name, coalesce(jsonb_agg(o.id), '[]'::jsonb)::text AS still_wrong
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.id IN (1, 63)
GROUP BY c.id, c.name
ORDER BY c.id;

id	name	still_wrong
1	Alice Nguyen	[1, 63, 64, 101, 149]
63	Luna Grant	[null]

the same query wrapped in coalesce, still returning a one-element array for customer 63

A FILTER clause keeps the null row out of the aggregate. With nothing left to aggregate the result really is null, and now coalesce has something to do:

SELECT c.id, c.name,
       coalesce(jsonb_agg(o.id) FILTER (WHERE o.id IS NOT NULL), '[]'::jsonb)::text AS fixed
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.id IN (1, 63)
GROUP BY c.id, c.name
ORDER BY c.id;

id	name	fixed
1	Alice Nguyen	[1, 63, 64, 101, 149]
63	Luna Grant	[]

FILTER removes the null row, coalesce supplies the empty array

PostgreSQL also ships a shorter route. jsonb_agg_strict skips null inputs on its own, so the FILTER clause becomes unnecessary:

SELECT c.id, c.name, jsonb_agg_strict(o.id)::text AS strict
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.id IN (1, 63)
GROUP BY c.id, c.name
ORDER BY c.id;

id	name	strict
1	Alice Nguyen	[1, 63, 64, 101, 149]
63	Luna Grant	[]

jsonb_agg_strict on the same LEFT JOIN, with no FILTER and no coalesce

jsonb_agg_strict and json_agg_strict are documented from PostgreSQL 16 and are absent from the 15 documentation, so check your server version before relying on them.

One trap remains, and it is the reason to keep coalesce anyway. The strict form is not symmetric: a group holding one null row aggregates to an empty array, but a query matching no rows at all still returns null.

SELECT (SELECT jsonb_agg_strict(x)::text FROM (VALUES (1)) s(x) WHERE false) AS over_zero_rows,
       (SELECT jsonb_agg_strict(x)::text FROM (VALUES (NULL::int)) s(x)) AS over_one_null_row;

over_zero_rows	over_one_null_row
NULL	[]

jsonb_agg_strict over no rows at all, and over one row whose value is null

So the form that covers both is coalesce(jsonb_agg_strict(x), '[]'::jsonb), and on a grouped LEFT JOIN either fix alone is enough.

Try it: change jsonb_agg_strict back to jsonb_agg in the editor below and run it again. Customer 63's empty list turns back into [null].

SELECT c.id, c.name, jsonb_agg_strict(o.id)::text AS strict
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.id IN (1, 63)
GROUP BY c.id, c.name
ORDER BY c.id;

Practice: empty, not null

JSON aggregation is node N054 of our free SQL course, COALESCE and NULLIF are N028, and the nulls an outer join creates are N029. No account, nothing to install, nothing to pay.