How to Use jsonb_agg in PostgreSQL: Syntax and Examples

In PostgreSQL, jsonb_agg is an aggregate function that collects multiple rows into a single JSONB array. You use it in a SELECT query with a column or expression to combine all values into one JSONB array result. It is useful for grouping related data as JSON objects in one row.

Syntax

The basic syntax of jsonb_agg is:

• jsonb_agg(expression): Aggregates the values of expression from multiple rows into a JSONB array.
• expression can be a column, a JSONB object, or any expression that returns JSONB.

This function is used with GROUP BY or over the entire result set.

sql

SELECT jsonb_agg(column_name) FROM table_name;

Example

This example shows how to aggregate multiple rows of user names into a single JSONB array.

sql

CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT);

INSERT INTO users (name) VALUES
('Alice'), ('Bob'), ('Charlie');

SELECT jsonb_agg(name) AS user_names FROM users;

Output

["Alice", "Bob", "Charlie"]

Common Pitfalls

Common mistakes when using jsonb_agg include:

• Not using GROUP BY when you want aggregation per group, resulting in aggregation over the entire table.
• Passing non-JSONB expressions without casting when needed.
• Expecting jsonb_agg to return a JSON object instead of an array.

Example of wrong and right usage:

sql

SELECT jsonb_agg(name) FROM users GROUP BY id; -- Wrong: grouping by unique id returns single-element arrays

SELECT id, jsonb_agg(name) FROM users GROUP BY id; -- Right: group by a common column to aggregate multiple rows

Quick Reference

Function	Description
jsonb_agg(expression)	Aggregates values into a JSONB array
Use with GROUP BY	Aggregate per group of rows
Works with JSONB or castable types	Input can be text, numbers, or JSONB
Returns JSONB array	Output is always a JSONB array

Key Takeaways

Use jsonb_agg to combine multiple row values into a single JSONB array.

Always use GROUP BY to aggregate per group, otherwise it aggregates the whole result set.

jsonb_agg returns a JSONB array, not a JSON object.

You can aggregate any expression that can be cast to JSONB.

Common errors include wrong grouping and misunderstanding output format.