PostgreSQL 18 reference · JSON Functions

PostgreSQL JSONB_OBJECT_AGG() Function

Aggregate key/value pairs from a group into one JSON object, such as a name-to-price lookup.

PostgreSQL 18 Since 9.5

Create a free account to save this page, take notes and track your progress. Sign in

Syntax

jsonb_object_agg(key, value)

Parameters

Parameter	Description
`key`	The key for each row.
`value`	The value for each row.

Return value

jsonb object.

Examples

A lookup object

SELECT jsonb_object_agg(name, price) AS prices FROM products WHERE category_id = 1;

Output

                                        prices
--------------------------------------------------------------------------------------
 {"Clean Code": 37.50, "SQL for Beginners": 29.99, "The Pragmatic Programmer": 42.00}
(1 row)

Run this on your computer — see “Get Started” for setup.

Counts as an object

SELECT jsonb_object_agg(status, n) AS counts
FROM (SELECT status, count(*) AS n FROM orders GROUP BY status) AS s;