SQLearnAcademy
←Function reference
JSONPostgreSQL only

JSONB_AGG

Collects values from many rows into one JSON array.

Syntax

JSONB_AGG(<expression>)
JSONB_AGG(<expression> ORDER BY <sort key>)

Like ARRAY_AGG, but the result is a JSON array that's easy to send straight to the application. Together with JSONB_BUILD_OBJECT it builds whole lists of objects: a department and all its people in one field.

Data for the examples

employees

namedepartmentsalarybonusphone
Emmabackend1200300+44 7700 900123
Liambackend1000NULLNULL
Oliviafrontend1350150+44 7700 900456
Noahfrontend950NULL+44 7700 900789
Sophiadesign1200200NULL

These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.

All names into a JSON array
SELECT JSONB_AGG(name) AS names
FROM employees;
names
["Emma","Liam","Olivia","Noah","Sophia"]
A list of people in each department
SELECT department,
JSONB_AGG(name ORDER BY name) AS team
FROM employees
GROUP BY department
ORDER BY department;
departmentteam
backend["Emma","Liam"]
design["Sophia"]
frontend["Noah","Olivia"]
Departments with objects for their peopleAdvanced
SELECT department,
JSONB_AGG(JSONB_BUILD_OBJECT('name', name, 'salary', salary) ORDER BY salary DESC) AS team
FROM employees
GROUP BY department
ORDER BY department;
departmentteam
backend[{"name":"Emma","salary":1200},{"name":"Liam","salary":1000}]
design[{"name":"Sophia","salary":1200}]
frontend[{"name":"Olivia","salary":1350},{"name":"Noah","salary":950}]

This shape is handy for an API: one row per department, with the people as an array of objects inside.

Watch out

NULL values aren't skipped — they become null in the array. To remove them — JSONB_AGG(phone) FILTER (WHERE phone IS NOT NULL).

If there are no rows, the result is NULL, not an empty array. If you need [] — COALESCE(JSONB_AGG(…), '[]').

Without ORDER BY inside the brackets, the order of elements isn't guaranteed.