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 ...] ]

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

SELECT
    f1 := ZIP(ARRAY[1, 2, 3, 4, 5] AS value).TRANSFORM(SELECT ARRAY_AGG(value ORDER BY value)).UNWRAP_ONE(), -- Array of values
    f2 := ZIP(ARRAY[1, 2, 2, 3] AS value).TRANSFORM(SELECT ARRAY_AGG(DISTINCT value ORDER BY value)).UNWRAP_ONE(), -- Distinct values collected into one array
    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) -- ARRAY_AGG(DISTINCT) with TRANSFORM GROUP BY (distinct values per group key)
;