object_agg

On this page

Collects key-value pairs into an object.

Syntax

object_agg(<key>, <value>)

Arguments

ArgumentTypeDescription
<key>stringObject key. Rows with NULL keys are skipped.
<value>Any typeAssociated value. NULL values are retained.

Returns

Returns object. Returns {} for empty input or when every key is NULL. For duplicate keys, the last value in the aggregation order is kept.

Usage notes

Use unique keys, or WITHIN GROUP ORDER BY before AGGREGATE to define which duplicate value wins.

Examples

Combine attribute updates into one object per event. Order them by sequence to keep the latest value for each key:

VALUES
    ('evt-17', 1, 'region', 'apac'), ('evt-17', 2, 'channel', 'web'),
    ('evt-17', 3, 'region', 'emea'), ('evt-42', 1, 'channel', 'mobile')
SELECT $0 AS event_id, $1 AS sequence, $2 AS attribute, $3 AS value
GROUP BY event_id
WITHIN GROUP ORDER BY sequence
AGGREGATE object_agg(attribute, value) AS attributes
ORDER BY event_id
+----------+-----------------------------------+
| event_id | attributes                        |
+----------+-----------------------------------+
| evt-17   | {"channel":"web","region":"emea"} |
| evt-42   | {"channel":"mobile"}              |
+----------+-----------------------------------+

The region for evt-17 is emea, while its channel is retained.