Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

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 of expression into 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 BY when expecting grouped arrays, which results in a single array of all values.
  • Using array_agg on non-aggregated columns without grouping, causing errors.
  • Expecting ordered arrays without specifying ORDER BY inside array_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

FeatureDescriptionExample
Basic usageAggregate values into an arrayarray_agg(column)
GroupingUse with GROUP BY to get arrays per groupSELECT col, array_agg(val) FROM table GROUP BY col
OrderingOrder elements inside arrayarray_agg(val ORDER BY val DESC)
Empty groupsReturns empty array if no rowsarray_agg(val)
Null valuesIncludes NULLs unless filteredarray_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.