PostgreSQL 18 reference · JSON Functions
PostgreSQL JSONB_AGG() Function
Aggregate a group's values or rows into a JSON array. json_agg() does the same but returns json.
PostgreSQL 18 Since 9.5
Syntax
jsonb_agg(expression ORDER BY ...)
Parameters
| Parameter | Description |
|---|---|
expression |
A value or a whole row. |
Return value
jsonb array, or NULL for no rows.
Examples
SELECT customer_id, jsonb_agg(order_id ORDER BY order_id) AS orders
FROM orders
GROUP BY customer_id
ORDER BY customer_id
LIMIT 3;
Output
customer_id | orders
-------------+-----------
1 | [1, 3, 9]
2 | [2, 11]
3 | [4, 7]
(3 rows)Run this on your computer — see “Get Started” for setup.
SELECT jsonb_agg(jsonb_build_object('id', order_id, 'status', status) ORDER BY order_id) AS orders
FROM orders
WHERE customer_id = 1;
Output
orders
------------------------------------------------------------------------------------------------------
[{"id": 1, "status": "delivered"}, {"id": 3, "status": "delivered"}, {"id": 9, "status": "pending"}]
(1 row)Run this on your computer — see “Get Started” for setup.
Version history
| Version | Change |
|---|---|
| PostgreSQL 9.5 | Added |
In other languages
JSON_GROUP_ARRAY()· SQLite
Related
JSONB_OBJECT_AGG()· Aggregate key/value pairs from a group into one JSON object, such as a name-to-price lookup.ARRAY_AGG()· Collect a group's values, including NULLs, into an array, optionally in a given order.JSONB_BUILD_OBJECT()· Build a JSON object from alternating keys and values. json_build_object() returns json instead of jsonb.- SQL String Functions · tutorial