Skip to main content
Version: 24.3.x


Categories: Aggregate, Semi-Structured Data


Aggregates the provided expression into an array.


ARRAY_AGG ( [ DISTINCT ] expression ) → array

  • expression: An expression of any primitive type to aggregate into an array.


ARRAY_AGG example
CREATE TABLE $scratch.people (name) AS VALUES ('Bob'), ('Charlie'), ('Alice');
SELECT ARRAY_AGG(name) FROM $scratch.people;
-- ['Bob', 'Charlie', 'Alice']

Usage Notes

ARRAY_AGG is similar to PIVOT in the SELECT command but for a single column.

The DISTINCT keyword removes duplicate values from expression.

If the input is empty, then an empty array is returned.

Dremio field size limits apply for ARRAY_AGG output. Queries will fail if the size of accumulated data exceeds a system field size limit.

ARRAY_AGG is not supported for complex types like STRUCT, MAP, and LIST.

The elements in the returned array are not listed in any particular order.