ARRAY_AGG()
Collects values from a group into a single array (list).
Description
ARRAY_AGG is an aggregate function that gathers all values from a group into a
single array (also called a list). Unlike STRING_AGG, it preserves the values
as structured data you can later unnest, filter, or pass to array functions.
Syntax
ARRAY_AGG(expression)
ARRAY_AGG(DISTINCT expression)Parameters
| Name | Description | Optional |
|---|---|---|
| expression | The value to collect (usually a column). | No |
Return Type
Returns an ARRAY of the input type. An empty group yields an empty array
rather than NULL in most engines.
Examples
Employees & Departments
Loading database engine...
Employees & Departments
Loading database engine...
Flip it back with UNNEST
An array can be turned back into rows with UNNEST: SELECT UNNEST(ARRAY_AGG(first_name)) FROM employees.`
Common Mistakes
- Treating the array as a string. It's a list, not text — use array
functions or
UNNESTto work with the elements. - Forgetting GROUP BY. Without grouping, every row collapses into one array.
- Large arrays. Aggregating huge groups can produce very large arrays;
consider
LIMITor sampling first.
Related Functions
See also: STRING_AGG, LIST.