Aggregation

How do you return JSON from a PostgreSQL query?

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

Two functions do it: jsonb_build_object('id', id, 'name', name) makes one object per row, and jsonb_agg(...) collects those objects into an array.

Nest one inside the other and a single query returns the document an API sends, parent and children together, with no assembly in application code. There is no FOR JSON clause to reach for.

The worked examples draw on two tables: customers, one row per customer, and orders, one row per order with a customer_id pointing back. Both are below. Two examples further down build their documents from literals instead of reading a table.

You can edit and run two of the queries below in your browser, and there are two exercises at the end. Reading JSON that is already stored in a column is a different job.

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 orders WHERE customer_id = 1) AS orders_for_customer_1;

customer_rows	order_rows	orders_for_customer_1
70	200	5

the two tables in our sandbox, and how many orders the first customer has

What is the PostgreSQL equivalent of SELECT ... FOR JSON PATH?

There is no FOR JSON clause, and PostgreSQL rejects it while parsing, not while running. Microsoft documents SQL Server's FOR JSON as serialising a result set to JSON in one clause. Here is the clause against a Postgres table:

SELECT name FROM customers FOR JSON PATH;

PostgreSQL responds syntax error at or near "JSON"

PostgreSQL does the same work with functions instead of a clause, which is more typing and more control: you choose the keys, the nesting and the order rather than accepting a serialisation of whatever the SELECT list happened to be.

SELECT name FROM customers FOR JSON PATH;

How do you turn a whole row into a JSON object?

Pass the table alias to row_to_json. Every column becomes a key, named as the column is named:

SELECT row_to_json(c)::text AS document
FROM customers c
WHERE id = 1;

document
{"id":1,"name":"Alice Nguyen","email":"[email protected]","city":"New York","country":"US","created_at":"2022-01-15T09:00:00+00:00","is_active":true}

one customer row as JSON. The +00:00 offset on created_at is our sandbox running in UTC, not a fixed format

That is the quickest route and the least controlled one. Every column ships, including any the client has no use for, and the keys carry whatever names the table uses. For anything an API returns, name the keys yourself.

How do you pick and rename the keys?

Use jsonb_build_object with alternating keys and values. Three columns of the same customer row, under names chosen for the client:

SELECT jsonb_build_object(
         'id', id,
         'customer', name,
         'country', country
       )::text AS document
FROM customers
WHERE id = 1;

document
{"id": 1, "country": "US", "customer": "Alice Nguyen"}

three chosen keys from one customer row

The arguments alternate: key, value, key, value. Keys are text and values can be any expression, including another jsonb_build_object, which is what makes nesting work.

How do you nest a child list inside a parent object?

Put jsonb_agg in the value position of the parent object, joining the child table and grouping by the parent. One customer with their orders, one row, one document:

SELECT jsonb_build_object(
         'customer', c.name,
         'orders', jsonb_agg(
           jsonb_build_object('id', o.id, 'status', o.status, 'total', o.total_amount)
           ORDER BY o.id
         )
       )::text AS document
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.id = 1
GROUP BY c.id, c.name;

document
{"orders": [{"id": 1, "total": 129.98, "status": "delivered"}, {"id": 63, "total": 249.00, "status": "delivered"}, {"id": 64, "total": 1999.00, "status": "delivered"}, {"id": 101, "total": 799.00, "status": "delivered"}, {"id": 149, "total": 1099.00, "status": "delivered"}], "customer": "Alice Nguyen"}

one customer and five orders as a single document

The inner jsonb_build_object shapes each order, jsonb_agg collects them, and the outer jsonb_build_object puts that array under the orders key next to the customer's name. The ORDER BY o.id inside the aggregate fixes the order of the array, and without it the array order is not promised.

Try it: change WHERE c.id = 1 to WHERE c.id IN (1, 2, 3) and run it again. You get one document per customer, not one document overall.

One thing to expect before you point a client at this: the query joins with a plain JOIN, so a customer with no orders is absent from the result altogether rather than arriving with an empty list. Switch to a LEFT JOIN to keep them and the array comes back holding one order object whose every field is null, which passes a length check and reads as a real order at the client. That is its own problem with its own fix.

SELECT jsonb_build_object(
         'customer', c.name,
         'orders', jsonb_agg(
           jsonb_build_object('id', o.id, 'status', o.status, 'total', o.total_amount)
           ORDER BY o.id
         )
       )::text AS document
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.id = 1
GROUP BY c.id, c.name;

What is the difference between json and jsonb here?

json keeps the text you gave it and jsonb stores a parsed structure, which changes what survives. Build the same object both ways, with a duplicate key and keys out of alphabetical order:

SELECT json_build_object('b', 1, 'a', 2, 'a', 3)::text AS as_json,
       jsonb_build_object('b', 1, 'a', 2, 'a', 3)::text AS as_jsonb;

as_json	as_jsonb
{"b" : 1, "a" : 2, "a" : 3}	{"a": 3, "b": 1}

the same three arguments built as json and as jsonb

json preserved both a keys and the order the arguments were given in. jsonb kept the last a and reordered the keys. That reordering is not alphabetical either:

SELECT jsonb_build_object('bb', 1, 'a', 2, 'ccc', 3, 'dd', 4)::text AS keys;

keys
{"a": 2, "bb": 1, "dd": 4, "ccc": 3}

jsonb returned these four keys shortest first, not in the order they were written

The practical rule is to depend on neither: read a jsonb document by key, never by position. Prefer jsonb when the value is stored, compared or indexed, and either when it is built and sent straight out. The return types differ, which matters when a column or a function signature is involved:

SELECT pg_typeof(json_agg(city))::text AS json_agg_type,
       pg_typeof(jsonb_agg(city))::text AS jsonb_agg_type,
       pg_typeof(array_agg(city))::text AS array_agg_type
FROM customers
WHERE country = 'DE';

json_agg_type	jsonb_agg_type	array_agg_type
json	jsonb	text[]

PostgreSQL also ships the SQL standard spellings JSON_ARRAYAGG and JSON_OBJECTAGG, which the PostgreSQL 16 release notes list among the SQL/JSON constructors that release added. JSON_OBJECTAGG takes its pair as key VALUE value, so the standard form runs here while the comma form is a syntax error. MySQL documents its own JSON_OBJECTAGG as taking a comma-separated key and value, which is where the habit comes from.

How is a JSON array different from a Postgres array?

A JSON array prints in square brackets and a PostgreSQL array prints in curly braces, and underneath they are different types:

SELECT jsonb_agg(city ORDER BY city)::text AS json_array,
       array_agg(city ORDER BY city)::text AS postgres_array
FROM customers
WHERE country = 'DE';

json_array	postgres_array
["Berlin", "Berlin", "Munich"]	{Berlin,Berlin,Munich}

the same three cities collected as a JSON array and as a Postgres array

A PostgreSQL array holds one type and works with array operators and unnest. A JSON array can hold mixed types and nested objects, which is what a document needs. Send JSON to a client; keep an array or a string for work the query itself is doing.

Practice: building a document from rows

JSON aggregation is node N054 of our free SQL course, and querying JSONB is N053, for reading documents you already have. No account, nothing to install, nothing to pay.