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
-- Array of values
-- Distinct values collected into one array
-- ARRAY_AGG(DISTINCT) with TRANSFORM GROUP BY (distinct values per group key)
RETURN
    f1 := ZIP(ARRAY(1, 2, 3, 4, 5) AS value).TRANSFORM(
        RETURN ARRAY_AGG(value ORDER BY value)
    ).UNWRAP_ONE(),
    f2 := ZIP(ARRAY(1, 2, 2, 3) AS value).TRANSFORM(
        RETURN ARRAY_AGG(DISTINCT value ORDER BY value)
    ).UNWRAP_ONE(),
    f3 := ZIP(ARRAY(1, 1, 2) AS g, ARRAY('a', 'a', 'b') AS value).TRANSFORM(
        RETURN
            g,
            vals := ARRAY_AGG(DISTINCT value ORDER BY value) GROUP BY (g)
        ORDER BY g
    )
;
Result
f1 ARRAYf2 ARRAYf3 VARBINARY
[1, 2, 3, 4, 5][1, 2, 3][{g: 1, vals: [a]}, {g: 2, vals: [b]}]

Last update at: 2026/10/13 08:32:21