Skip to main content

Categories: Aggregate, Semi-Structured Data

ARRAY_AGG

Aggregates the provided expression into an array.

Syntax​

ARRAY_AGG ( [ DISTINCT ] expression ) → array​

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

Examples

ARRAY_AGG example 
CREATE TABLE <catalog-name>.people (name) AS VALUES ('Bob'), ('Charlie'), ('Alice');
SELECT ARRAY_AGG(name) FROM <catalog-name>.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. For Dremio Cloud, the limit is 32KB.

• 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.