ARRAY_AGG() OVER ...

All functions > WINDOW FUNCTION > ARRAY_AGG() OVER ...

Returns an array of all values in the window frame.

Syntax

ARRAY_AGG([DISTINCT] expr) OVER ([PARTITION BY expr [, ...]] [ORDER BY sort_item [, ...]] [ROWS|RANGE|GROUPS frame])

Notes

  • Collects all values from the window frame into an array
  • Optional DISTINCT collects unique values per frame position
  • ORDER BY determines the order of elements in the array
  • NULL values are included in the array
  • Returns ARRAY type with element type matching input

See also

Examples

FeatureQL
SELECT
    f1 := ZIP(ARRAY[1,2,3] AS id, ARRAY[30,10,20] AS v).TRANSFORM(SELECT ARRAY_AGG(v) OVER (ORDER BY id ASC)).UNWRAP(), -- Growing list of values in the frame (v permuted so inner arrays are not sorted)
    f2 := ZIP(ARRAY[1,2,3,4] AS id, ARRAY['a','b','b','c'] AS v).TRANSFORM(SELECT ARRAY_AGG(DISTINCT v) OVER (ORDER BY id ASC)).UNWRAP() -- Growing distinct values in the frame (pairs with the COUNT(DISTINCT … OVER …) example)
;
Result
f1 ARRAYf2 ARRAY
[[30], [30, 10], [30, 10, 20]][[a], [a, b], [a, b], [a, b, c]]

Suggest changes to this page
Last update at: 2026/05/26 17:22:09