How to Use json_agg in PostgreSQL: Syntax and Examples

In PostgreSQL, json_agg is an aggregate function that collects multiple rows into a single JSON array. You use it in a SELECT query with a column or expression to combine results as JSON. It is useful for returning grouped data as JSON arrays.

Syntax

The json_agg function aggregates input values as a JSON array. It is used as an aggregate function in a SELECT statement.

• json_agg(expression): Aggregates the values of expression into a JSON array.
• expression can be a column, a row, or any valid expression.

sql

SELECT json_agg(expression) FROM table_name WHERE condition;

Example

This example shows how to aggregate multiple rows from a users table into a JSON array of user names.

sql

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

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

SELECT json_agg(name) AS user_names FROM users;

Output

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

Common Pitfalls

Common mistakes when using json_agg include:

• Forgetting to group rows properly when combining with other columns, which can cause errors or unexpected results.
• Using json_agg without a GROUP BY when you want grouped JSON arrays per category.
• Not casting complex rows to JSON explicitly when needed, which can cause type errors.

Always ensure your query structure matches the aggregation you want.

sql

/* Wrong: Missing GROUP BY when aggregating per category */
SELECT category, json_agg(name) FROM products;

/* Right: Use GROUP BY to get JSON arrays per category */
SELECT category, json_agg(name) FROM products GROUP BY category;

Quick Reference

Feature	Description
Function	json_agg(expression)
Returns	JSON array of aggregated values
Input	Any valid SQL expression or row
Use case	Combine multiple rows into JSON array
Common clause	Often used with GROUP BY

Key Takeaways

Use json_agg to combine multiple rows into a single JSON array in PostgreSQL.

Always use GROUP BY when you want JSON arrays grouped by a column.

json_agg can aggregate simple columns or entire rows converted to JSON.

Check your query structure to avoid aggregation errors or unexpected results.

json_agg is useful for APIs or reports that need JSON formatted data.