array_agg ​

Creates an array of the argument values. The array retains NULL elements; use FILTER to exclude them.

Specify an aggregate ORDER BY when the order of elements matters. Without it, the element order follows the rows admitted by the query and should not be relied on.

Syntax

array_agg(value [ORDER BY expression])

Arguments

• value — A column or expression. The element type follows native collection type inference; nullable members are retained.

Returned value

A native Array containing one element per admitted row. No input rows returns SQL NULL, rather than an empty array.

Examples

Collecting names by department

CREATE TABLE employees (department TEXT, name TEXT);
INSERT INTO employees VALUES ('Engineering', 'Ada'), ('Engineering', 'Grace'), ('Sales', 'Sam');

SELECT department, array_agg(name ORDER BY name) AS names
FROM employees
GROUP BY department
ORDER BY department
FORMAT csv;

"Engineering","[""Ada"",""Grace""]"
"Sales","[""Sam""]"

Keeping or removing NULL elements

CREATE TABLE readings (value INTEGER);
INSERT INTO readings VALUES (10), (NULL), (20);

SELECT array_agg(value ORDER BY value NULLS LAST),
       array_agg(value ORDER BY value) FILTER (WHERE value IS NOT NULL)
FROM readings
FORMAT csv;

"[10,20,null]","[10,20]"

Collecting distinct values

CREATE TABLE tags (value TEXT);
INSERT INTO tags VALUES ('sql'), ('rust'), ('sql');

SELECT array_agg(DISTINCT value ORDER BY value)
FROM tags
FORMAT csv;

"[""rust"",""sql""]"

See array functions for reading and transforming the result.