COLLECT_SET

​

Syntax

​

Aggregate

collect_set([DISTINCT] value)

​

Window

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

• COLLECT_SET always removes duplicates. For windows, use all rows in the group or a frame starting at its first row; sliding frames are not supported.
• Use a frame that includes all rows in the group or starts at the first row (UNBOUNDED PRECEDING). Sliding frames that drop earlier rows are not supported. RANGE frames with a numeric or time offset are not supported.

​

Arguments

​

Aggregate and window

​

Returns

• Type: ARRAY with the SQL type of value as its element type
• NULL behavior: The result ARRAY is non-NULL. NULL input values are retained as one distinct NULL element; all-NULL nonempty input yields [NULL].
• Empty input: Returns [] for an empty global aggregate or empty frame. An all-NULL nonempty group/frame returns [NULL].
• Shape: One-dimensional array containing one element for each distinct input value, including at most one NULL.
• Ordering: Element order is unspecified. WITHIN GROUP is accepted but currently does not apply its ordering; use ARRAY_SORT on the result when sorted values are needed.

​

Properties

​

Usage notes

• Duplicate elimination is inherent; no DISTINCT keyword is needed.
• COLLECT_SET retains a NULL element when any input is NULL. ARRAY_UNIQUE_AGG explicitly removes NULL instead.
• To exclude NULL, filter the input with WHERE value IS NOT NULL before aggregation.
• Use a frame that includes all rows in the group or starts at the first row (UNBOUNDED PRECEDING). Sliding frames that drop earlier rows are not supported.
• 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.
• Duplicate values are removed whether or not you write DISTINCT. Repeated NULL values become one NULL; DISTINCT does not remove that NULL.

​

Examples

​

Distinct values including NULL, sorted

• ARRAY_SORT orders ascending with NULL last.

WITH t(v) AS (VALUES (7),(7),(CAST(NULL AS INTEGER))) SELECT array_sort(collect_set(v)) AS result FROM t;

​

All NULL values

WITH t(v) AS (VALUES (CAST(NULL AS INTEGER)),(CAST(NULL AS INTEGER))) SELECT array_sort(collect_set(v)) AS result FROM t;

​

Empty input

WITH t(v) AS (VALUES (1)) SELECT collect_set(v) AS result FROM t WHERE FALSE;

​

Filter NULL before collecting

• WHERE removes NULL rows before aggregation.

WITH t(v) AS (VALUES (7),(7),(CAST(NULL AS INTEGER))) SELECT array_sort(collect_set(v)) AS result FROM t WHERE v IS NOT NULL;

​

Distinct values across all rows in each group

WITH sample(id, v) AS (VALUES (1, CAST(NULL AS INTEGER)), (2, 1), (3, 1))
SELECT id, array_sort(collect_set(v) OVER ()) AS result
FROM sample ORDER BY id ASC NULLS LAST;

​

Sort distinct values and NULL

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

​

Limitations

• WITHIN GROUP ordering is currently ignored. Sort the result with ARRAY_SORT for ascending values with NULL last.
• Sliding frames that drop earlier rows and RANGE frames with numeric or time offsets are not supported.
• IGNORE NULLS/RESPECT NULLS clauses are unsupported; filter the input to remove NULL.