JSON aggregation with JSON_AGG in PostgreSQL

Introduction

JSON_AGG helps you combine multiple rows into a single JSON array. This makes it easy to group related data together in one place.

You want to collect all comments for a blog post into one JSON list.

You need to show all products in a category as a JSON array.

You want to return a list of orders for each customer in JSON format.

You are building an API that sends grouped data as JSON arrays.

You want to simplify complex data by grouping related rows into JSON.

Syntax

PostgreSQL

JSON_AGG(expression)

The expression is usually a column or a JSON object you want to collect.

It returns a JSON array containing all the values from the grouped rows.

Examples

This collects all employee names into one JSON array.

PostgreSQL

SELECT JSON_AGG(name) FROM employees;

This groups employee names by their department, returning a JSON array per department.

PostgreSQL

SELECT department, JSON_AGG(name) FROM employees GROUP BY department;

This creates a JSON array of objects, each with employee id and name.

PostgreSQL

SELECT JSON_AGG(JSON_BUILD_OBJECT('id', id, 'name', name)) FROM employees;

Sample Program

This example creates a table of employees, inserts some data, and then groups employee names by their department into JSON arrays.

PostgreSQL

CREATE TABLE employees (id SERIAL PRIMARY KEY, name TEXT, department TEXT);
INSERT INTO employees (name, department) VALUES
('Alice', 'Sales'),
('Bob', 'Sales'),
('Charlie', 'HR');

SELECT department, JSON_AGG(name) AS employee_names
FROM employees
GROUP BY department
ORDER BY department;

Important Notes

JSON_AGG works well with GROUP BY to group rows into JSON arrays.

You can use JSON_BUILD_OBJECT inside JSON_AGG to create arrays of JSON objects.

Ordering inside JSON_AGG requires using ORDER BY inside the aggregate function if needed.

Summary

JSON_AGG collects multiple rows into a JSON array.

It is useful for grouping related data in JSON format.

Works great with GROUP BY to organize data by categories.