array_agg

aggregate values into array

Synopsis

array_agg(any) -> [any]|null

Description

The array_agg aggregate function organizes its input into an array. If the aggregated values vary in type, the return type will be an array of union of the types encountered. If no values are aggregated, the return value is null.

Note

See collect for a variant that returns an empty array when no values are aggregated. The null return follows the SQL standard.

Examples

Simple sequence aggregated into an array (see collect docs for more examples):

# spq
array_agg(this)
# input
1
2
3
4
# expected output
[1,2,3,4]

Contrast array_agg with collect when no values have been aggregated:

# spq
aggregate
  array_agg(a) filter (a > 1),
  collect(a) filter (a > 1)
  by k
| sort
# input
{a:1,k:1}
{a:2,k:2}
{a:3,k:2}
# expected output
{k:1,array_agg:null,collect:[]}
{k:2,array_agg:[2,3],collect:[2,3]}