How to Use jsonb_object_agg in PostgreSQL: Syntax and Examples

In PostgreSQL, jsonb_object_agg aggregates key-value pairs from rows into a single jsonb object. You provide two arguments: one for keys and one for values, and it returns a JSONB object combining them. This is useful to convert table data into JSON format grouped by keys.

Syntax

The jsonb_object_agg function takes two arguments: a key and a value. It aggregates these pairs from multiple rows into one jsonb object.

• key: The column or expression used as the JSON object key.
• value: The column or expression used as the JSON object value.

It returns a jsonb object where each key maps to its corresponding value.

sql

jsonb_object_agg(key_expression, value_expression)

Example

This example shows how to aggregate a table of user IDs and names into a single JSONB object where user IDs are keys and names are values.

sql

CREATE TABLE users (id INT, name TEXT);
INSERT INTO users VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol');

SELECT jsonb_object_agg(id, name) AS user_map FROM users;

Output

{"1": "Alice", "2": "Bob", "3": "Carol"}

Common Pitfalls

Common mistakes when using jsonb_object_agg include:

• Using duplicate keys, which causes the last value to overwrite previous ones.
• Passing NULL keys or values, which can result in NULL output or errors.
• Not casting values properly if you want nested JSON objects.

Always ensure keys are unique and non-null for predictable results.

sql

/* Wrong: duplicate keys cause overwriting */
SELECT jsonb_object_agg(id % 2, name) FROM users;

/* Right: use unique keys */
SELECT jsonb_object_agg(id, name) FROM users;

Quick Reference

Parameter	Description
key_expression	Column or expression used as JSON object keys
value_expression	Column or expression used as JSON object values
Return Type	Aggregated JSONB object
Behavior with duplicates	Last value for duplicate keys is kept
Null handling	NULL keys cause NULL result; NULL values included as null

Key Takeaways

Use jsonb_object_agg(key, value) to create a JSONB object from key-value pairs in rows.

Ensure keys are unique and not NULL to avoid unexpected overwrites or NULL results.

It returns a single JSONB object aggregating all input rows.

Casting values to JSONB allows nested JSON structures inside the result.

Common errors come from duplicate keys or NULL keys; handle these carefully.