How to Use array_agg Function in Snowflake: Syntax and Examples
In Snowflake, use the
array_agg function to collect multiple values from rows into a single array. It is typically used with GROUP BY to aggregate column values into an array per group.Syntax
The array_agg function collects values from multiple rows into a single array. It is used as an aggregate function, often combined with GROUP BY.
Syntax parts:
array_agg(expression): Aggregates the values ofexpressioninto an array.expression: The column or value to aggregate.
sql
array_agg(expression)
Example
This example shows how to use array_agg to group employee names by their department into arrays.
sql
CREATE OR REPLACE TABLE employees (id INT, name STRING, department STRING); INSERT INTO employees VALUES (1, 'Alice', 'Sales'), (2, 'Bob', 'Sales'), (3, 'Charlie', 'HR'), (4, 'David', 'HR'), (5, 'Eve', 'IT'); SELECT department, array_agg(name) AS employee_names FROM employees GROUP BY department ORDER BY department;
Output
DEPARTMENT | EMPLOYEE_NAMES
---------- | --------------
HR | ["Charlie", "David"]
IT | ["Eve"]
Sales | ["Alice", "Bob"]
Common Pitfalls
Common mistakes when using array_agg include:
- Not using
GROUP BYwhen expecting grouped arrays, which results in a single array of all values. - Using
array_aggon non-aggregated columns without grouping, causing errors. - Expecting ordered arrays without specifying
ORDER BYinsidearray_agg.
To get ordered arrays, use array_agg(expression ORDER BY column).
sql
SELECT department, array_agg(name ORDER BY name) AS employee_names_ordered FROM employees GROUP BY department ORDER BY department;
Output
DEPARTMENT | EMPLOYEE_NAMES_ORDERED
---------- | ---------------------
HR | ["Charlie", "David"]
IT | ["Eve"]
Sales | ["Alice", "Bob"]
Quick Reference
| Feature | Description | Example |
|---|---|---|
| Basic usage | Aggregate values into an array | array_agg(column) |
| Grouping | Use with GROUP BY to get arrays per group | SELECT col, array_agg(val) FROM table GROUP BY col |
| Ordering | Order elements inside array | array_agg(val ORDER BY val DESC) |
| Empty groups | Returns empty array if no rows | array_agg(val) |
| Null values | Includes NULLs unless filtered | array_agg(val) |
Key Takeaways
Use
array_agg to collect multiple row values into a single array.Always pair
array_agg with GROUP BY to aggregate per group.Use
ORDER BY inside array_agg to control element order.Be aware that
array_agg includes NULLs unless filtered out.Test queries to ensure arrays contain expected values and order.
