ARRAY_UNIQUE_AGG

​

Syntax

​

Aggregate

array_unique_agg(value)

​

Window

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

• 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<same SQL type as value>
• NULL behavior: NULL values are ignored. The array itself is non-NULL.
• Empty input: Returns an empty array for an empty global aggregate or all-NULL frame.
• Shape: A one-dimensional array containing each non-NULL input value once.
• Ordering: Element order is unspecified; sort the output if ordered presentation is required.

​

Properties

​

Usage notes

• Duplicate and NULL values are removed automatically. DISTINCT is not needed.
• 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.

​

Examples

​

Duplicate and NULL values

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

​

All NULL values

WITH t("value") AS (VALUES (CAST(NULL AS INTEGER))) SELECT array_unique_agg("value") AS result FROM t;

​

Limitations

• 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.
• WITHIN GROUP does not guarantee ordered output for this function. Sort the returned array when you need a particular order.