aggregate

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

Loading playground environment...

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 UNNEST to 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 LIMIT or sampling first.

Related functions

STRING_AGG()

aggregate

Concatenates values from a group into a single string, separated by a delimiter.

Cite this resource

SQLSimplified. "ARRAY_AGG() SQL Function". Available at: https://sqlsimplified.online/reference/array-agg