ARRAY_AGG

​

Syntax

​

Aggregate

array_agg([DISTINCT] value)

​

Window

array_agg(value) OVER ([PARTITION BY key] [ORDER BY ordering_key] [ROWS frame])

array_agg(value) WITHIN GROUP (ORDER BY value ASC NULLS LAST)
  OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

​

Arguments

​

Aggregate and window

​

Returns

• Type: ARRAY with the SQL type of value as its element type
• NULL behavior: Returns a non-NULL array. Grouped DISTINCT calls keep one NULL when present; ordinary calls skip NULL values.
• Empty input: Returns [] on empty input. All-NULL non-DISTINCT input returns []; all-NULL grouped DISTINCT input returns [NULL].
• Shape: One-dimensional array; grouped DISTINCT calls retain one of each value.
• Ordering: Grouped aggregation returns elements in an unspecified order, even with WITHIN GROUP. Use ARRAY_SORT for a sorted grouped result. In a full-group window call, WITHIN GROUP ordering is applied, as shown in Examples.

​

Properties

​

Usage notes

• Use ARRAY_SORT(array_agg(value)) for sorted non-NULL values.
• COLLECT_SET keeps at most one NULL; ARRAY_UNIQUE_AGG removes NULL.
• INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.
• In grouped aggregation, DISTINCT removes duplicate values and retains one NULL. Without DISTINCT, NULL values are skipped.

​

Examples

​

Basic example

WITH t("value") AS (VALUES (1),(1),(CAST(NULL AS INTEGER))) SELECT array_agg("value") AS result FROM t;

​

Ordinary and DISTINCT NULL policies

• ARRAY_SORT displays ascending values with NULL last.

WITH sample(id, v) AS (VALUES (1, CAST(NULL AS INTEGER)), (2, 1), (3, 1))
SELECT array_sort(array_agg(v)) AS ordinary,
  array_sort(array_agg(DISTINCT v)) AS distinct_values
FROM sample;

​

Sliding collection skips NULL

WITH sample(id, v) AS (VALUES (1, CAST(NULL AS INTEGER)), (2, 1), (3, 1))
SELECT id, array_sort(array_agg(v) OVER (ORDER BY id ASC NULLS LAST
  ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)) AS result
FROM sample ORDER BY id ASC NULLS LAST;

​

Sort the collected values

WITH sample(v) AS (VALUES (3), (CAST(NULL AS INTEGER)), (1), (1))
SELECT array_sort(array_agg(v)) AS result FROM sample;

WITH t(v) AS (VALUES (30), (10), (20)) SELECT array_agg(v) WITHIN GROUP (ORDER BY v ASC NULLS LAST) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS result FROM t;

​

Limitations

• In grouped aggregation, WITHIN GROUP ordering is currently ignored, with or without DISTINCT. ARRAY_SORT gives an ascending grouped result with NULL last.
• WITHIN GROUP accepts only one ordering expression, and it must be the collected value. A separate sort column is rejected. Full-group window calls apply this ordering.
• Sliding ROWS frames are supported for the window syntax shown above.
• RANGE frames with numeric or time offsets are not supported.
• DISTINCT in a window call currently has no effect: duplicate rows still contribute to the result. Use grouped DISTINCT aggregation when duplicate removal is required.