SQLearnAcademy
←Function reference
AggregateSQL standard

ARRAY_AGG

Collects values from many rows into one array.

Syntax

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

An aggregate function like STRING_AGG, but the result is an array rather than text: you can keep working with it like a list. The order of elements is set with ORDER BY right inside the brackets, and a single element is taken by its number: [1] is the first.

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 an array
SELECT ARRAY_AGG(name) AS names
FROM employees;
names
["Emma","Liam","Olivia","Noah","Sophia"]

The result table shows the array in square brackets, but PostgreSQL itself, in psql for example, writes it like this: {Emma,Liam,…}.

An array for each department
SELECT department,
ARRAY_AGG(name ORDER BY name) AS team
FROM employees
GROUP BY department
ORDER BY department;
departmentteam
backend["Emma","Liam"]
design["Sophia"]
frontend["Noah","Olivia"]
Who earns the most in each department — without a subqueryAdvanced
SELECT department,
(ARRAY_AGG(name ORDER BY salary DESC))[1] AS top_earner
FROM employees
GROUP BY department
ORDER BY department;
departmenttop_earner
backendEmma
designSophia
frontendOlivia

We collect the names from the highest salary to the lowest and take the first element.

Watch out

NULL values aren't skipped — they end up in the array. To remove them — ARRAY_AGG(phone) FILTER (WHERE phone IS NOT NULL).

Element numbering starts at 1, not 0: (ARRAY_AGG(name))[0] gives NULL.

If you just need comma-separated text, use STRING_AGG. An array is handy for further processing, not for display.