PostgreSQL 18 reference · Aggregate Functions
PostgreSQL ARRAY_AGG() Function
Collect a group's values, including NULLs, into an array, optionally in a given order.
PostgreSQL 18
Syntax
array_agg(expression ORDER BY ...)
Parameters
| Parameter | Description |
|---|---|
expression |
A column or expression. |
ORDER BY optional |
The order of the elements. |
Return value
An array, or NULL for no rows.
Examples
SELECT country, array_agg(split_part(name, ' ', 1) ORDER BY name) AS first_names
FROM customers
GROUP BY country
HAVING count(*) > 1
ORDER BY country;
Output
country | first_names
---------+------------------------------------------
UK | {Ada,Alan}
USA | {Barbara,Grace,Katherine,Linus,Margaret}
(2 rows)Run this on your computer — see “Get Started” for setup.
SELECT (array_agg(name ORDER BY salary DESC))[1] AS top_earner FROM employees;
Output
top_earner ------------- Sofia Reyes (1 row)
Run this on your computer — see “Get Started” for setup.
In other languages
JSON_GROUP_ARRAY()· SQLite
Related
STRING_AGG()· Join a group's non-NULL values into one string with a separator, optionally in a given ORDER BY order.UNNEST()· Expand an array into a set of rows, one per element. With several arrays it zips them into columns.JSONB_AGG()· Aggregate a group's values or rows into a JSON array. json_agg() does the same but returns json.- SQL Aggregate Functions · tutorial
- SQL GROUP BY · tutorial