OBJECT_AGG

​

Syntax

​

Aggregate

object_agg(key, value)

​

Window

object_agg(key, 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: VARIANT
• NULL behavior: Rows with NULL key or SQL-NULL value are ignored. Duplicate keys raise an error. DISTINCT removes identical key/value tuples only.
• Empty input: Returns an empty object on empty input.
• Shape: VARIANT object containing retained keys and values.
• Ordering: Object field order is unspecified.

​

Properties

​

Usage notes

• With DISTINCT the same key plus different values still errors.
• DISTINCT removes duplicates in grouped aggregation. Do not use it to remove duplicates in a window call.

​

Examples

​

Build a VARIANT object

• The table displays the logical VARIANT object; object key order and client formatting can differ.

WITH t("key","value") AS (VALUES ('k1','"v1"'),('k2','"v2"')) SELECT object_agg("key",parse_json("value")) AS result FROM t;

​

Build an object with numeric and boolean values

WITH sample(k, v) AS (VALUES ('count', '2'), ('active', 'true'))
SELECT object_agg(k, parse_json(v)) AS result FROM sample;

​

Limitations

• Sliding windows that drop earlier rows are not supported. Start the frame at the first row in the group.
• The value must already be VARIANT; other values need explicit conversion.