ARRAY_AGG() GROUP BY ...
All functions > GROUP BY > ARRAY_AGG() GROUP BY ...
Returns an array of the values in the group.
Syntax
ARRAY_AGG([DISTINCT] expr [ORDER BY ...] [LIMIT n]) [ FILTER (WHERE condition) ] [ GROUP BY feature [, feature ...] ]
Notes
- Collects all values in a group into a single array
- Optional DISTINCT removes duplicate values before collecting
- Supports ORDER BY to control the order of elements in the result array
- Supports LIMIT to restrict the number of elements
- Can be combined with WHERE clause to filter values before aggregation
- NULL values are included in the result array
- Returns empty array if group has no values
- Use with GROUP BY to create arrays per group
Examples
FeatureQL
SELECT
-- Array of values
f1 := ZIP(ARRAY(1, 2, 3, 4, 5) AS value).TRANSFORM(
SELECT ARRAY_AGG(value ORDER BY value)
).UNWRAP_ONE(),
-- Distinct values collected into one array
f2 := ZIP(ARRAY(1, 2, 2, 3) AS value).TRANSFORM(
SELECT ARRAY_AGG(DISTINCT value ORDER BY value)
).UNWRAP_ONE(),
-- ARRAY_AGG(DISTINCT) with TRANSFORM GROUP BY (distinct values per group key)
f3 := ZIP(ARRAY(1, 1, 2) AS g, ARRAY('a', 'a', 'b') AS value).TRANSFORM(
SELECT G, ARRAY_AGG(DISTINCT VALUE ORDER BY VALUE) GROUP BY G AS VALS ORDER BY G
)
;Result
| f1 ARRAY | f2 ARRAY | f3 VARCHAR |
|---|---|---|
| [1, 2, 3, 4, 5] | [1, 2, 3] | [{g: 1, vals: [a]}, {g: 2, vals: [b]}] |