JSON_AGG and JSONB_AGG are aggregate functions that fold a group's rows into a single JSON array. They turn "many rows per key" into "one row holding JSON," and they are the most direct way to return a nested API response straight from the database, without assembling JSON in application code.
JSON_AGG versus JSONB_AGG
Both gather values into a JSON array, but they store it differently:
JSON_AGG returns the json type: text kept "as is," preserving key order and duplicates.JSONB_AGG returns jsonb: a binary form where duplicate keys collapse (the last one wins), whitespace is normalized, and field access is faster.- For most API responses, reach for
JSONB_AGG: it is more compact and easier to filter or index further down the pipeline.
SELECT JSONB_AGG(t ORDER BY t.id) AS tasks
FROM tasks t;
By passing the whole row t into the aggregate, you get an array of objects whose keys are the column names. This is the quickest way to "ship a table as JSON."
ORDER BY inside the aggregate
Element order in the array is not guaranteed without an ORDER BY right inside the call. Never rely on table row order; set the sort explicitly:
SELECT
u.id,
u.email,
JSONB_AGG(o.amount ORDER BY o.created_at DESC) AS amounts
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email;
The ORDER BY lives inside the aggregate's parentheses, not in the outer query. An outer ORDER BY sorts the result rows, but not the elements inside each JSON array, which is a common source of confusion.
One object per row with jsonb_build_object
A bare array of scalars is rarely what you want; usually you build an array of objects. jsonb_build_object constructs a JSON object from key-value pairs, and JSONB_AGG folds those objects into an array:
SELECT
u.id,
u.email,
JSONB_AGG(
jsonb_build_object(
'order_id', o.id,
'amount', o.amount,
'status', o.status
)
ORDER BY o.created_at DESC
) AS orders
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email;
This gives you full control over the field names in the response: they need not match the column names. That is handy when the database schema and the API contract diverge.
A nested API response in one query
These building blocks compose into a full "user with a list of orders" document in a single pass, with no N+1 queries and no application-side assembly:
SELECT jsonb_build_object(
'user_id', u.id,
'email', u.email,
'country', u.country,
'orders', JSONB_AGG(
jsonb_build_object('id', o.id, 'amount', o.amount)
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;
For an employee hierarchy the same trick collects each manager together with their reports:
SELECT
m.name AS manager,
JSONB_AGG(
jsonb_build_object('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;
FILTER to skip NULLs
JSONB_AGG includes NULL values in the array: a row with no match from a LEFT JOIN produces a null element. To avoid that, add a FILTER:
SELECT
u.id,
JSONB_AGG(o.id) FILTER (WHERE o.id IS NOT NULL) AS order_ids
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;
Gotcha: if a user has no orders, JSONB_AGG(...) FILTER (...) returns NULL, not an empty array []. Many API clients dislike null where they expect an array. Wrap it in COALESCE(JSONB_AGG(...) FILTER (...), '[]'::jsonb) so you always emit an array.
MySQL and ClickHouse
In MySQL 8 the equivalent is JSON_ARRAYAGG, and JSON_OBJECT builds the per-row object:
SELECT
user_id,
JSON_ARRAYAGG(JSON_OBJECT('id', id, 'amount', amount)) AS orders
FROM orders
GROUP BY user_id;
What to keep in mind about MySQL:
JSON_ARRAYAGG has no built-in ORDER BY; impose an order through a pre-sorted subquery.- There is no
json versus jsonb split: the type is always JSON, already stored in a binary normalized form.
ClickHouse has no direct equivalent; you assemble with groupArray and serialize, or use its JSON-string functions. When you need a predictable nested JSON with sorting and filtering, PostgreSQL with JSONB_AGG ... ORDER BY ... FILTER stays the most direct tool.
JSON_AGGandJSONB_AGGare aggregate functions that fold a group's rows into a single JSON array. They turn "many rows per key" into "one row holding JSON," and they are the most direct way to return a nested API response straight from the database, without assembling JSON in application code.JSON_AGG versus JSONB_AGG
Both gather values into a JSON array, but they store it differently:
JSON_AGGreturns thejsontype: text kept "as is," preserving key order and duplicates.JSONB_AGGreturnsjsonb: a binary form where duplicate keys collapse (the last one wins), whitespace is normalized, and field access is faster.JSONB_AGG: it is more compact and easier to filter or index further down the pipeline.SELECT JSONB_AGG(t ORDER BY t.id) AS tasks FROM tasks t;By passing the whole row
tinto the aggregate, you get an array of objects whose keys are the column names. This is the quickest way to "ship a table as JSON."ORDER BY inside the aggregate
Element order in the array is not guaranteed without an
ORDER BYright inside the call. Never rely on table row order; set the sort explicitly:SELECT u.id, u.email, JSONB_AGG(o.amount ORDER BY o.created_at DESC) AS amounts FROM users u JOIN orders o ON o.user_id = u.id GROUP BY u.id, u.email;The
ORDER BYlives inside the aggregate's parentheses, not in the outer query. An outerORDER BYsorts the result rows, but not the elements inside each JSON array, which is a common source of confusion.One object per row with jsonb_build_object
A bare array of scalars is rarely what you want; usually you build an array of objects.
jsonb_build_objectconstructs a JSON object from key-value pairs, andJSONB_AGGfolds those objects into an array:SELECT u.id, u.email, JSONB_AGG( jsonb_build_object( 'order_id', o.id, 'amount', o.amount, 'status', o.status ) ORDER BY o.created_at DESC ) AS orders FROM users u JOIN orders o ON o.user_id = u.id GROUP BY u.id, u.email;This gives you full control over the field names in the response: they need not match the column names. That is handy when the database schema and the API contract diverge.
A nested API response in one query
These building blocks compose into a full "user with a list of orders" document in a single pass, with no N+1 queries and no application-side assembly:
SELECT jsonb_build_object( 'user_id', u.id, 'email', u.email, 'country', u.country, 'orders', JSONB_AGG( jsonb_build_object('id', o.id, 'amount', o.amount) 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;For an employee hierarchy the same trick collects each manager together with their reports:
SELECT m.name AS manager, JSONB_AGG( jsonb_build_object('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;FILTER to skip NULLs
JSONB_AGGincludesNULLvalues in the array: a row with no match from aLEFT JOINproduces anullelement. To avoid that, add aFILTER:SELECT u.id, JSONB_AGG(o.id) FILTER (WHERE o.id IS NOT NULL) AS order_ids FROM users u LEFT JOIN orders o ON o.user_id = u.id GROUP BY u.id;MySQL and ClickHouse
In MySQL 8 the equivalent is
JSON_ARRAYAGG, andJSON_OBJECTbuilds the per-row object:-- 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;What to keep in mind about MySQL:
JSON_ARRAYAGGhas no built-inORDER BY; impose an order through a pre-sorted subquery.jsonversusjsonbsplit: the type is alwaysJSON, already stored in a binary normalized form.ClickHouse has no direct equivalent; you assemble with
groupArrayand serialize, or use its JSON-string functions. When you need a predictable nested JSON with sorting and filtering, PostgreSQL withJSONB_AGG ... ORDER BY ... FILTERstays the most direct tool.