array_agg function

The array_agg(value) function aggregates values (including nulls) as an array. The input values to the aggregate can be filtered.

Syntax

array_agg ( <value>
  [ORDER BY <col_ref> [ASC | DESC] [NULLS FIRST | NULLS LAST] [, ...]]
)
[FILTER (WHERE <filter_clause>)]

Signatures

Return value

array_agg returns the aggregated values as an array.

Any ORDER BY applied before the aggregate function is evaluated, such as in a feeding subquery, is ignored. Unless ORDER BY is included in the aggregate function call itself, the order in which the values are aggregated is unspecified.

Details

Usage in dataflows

While array_agg is available in Materialize, materializing array_agg(values) is considered an incremental view maintenance anti-pattern. Any change to the data underlying the function call will require the function to be recomputed entirely, discarding the benefits of maintaining incremental updates.

Instead, we recommend that you materialize all components required for the array_agg function call and create a non-materialized view using array_agg on top of that. That pattern is illustrated in the following statements:

CREATE MATERIALIZED VIEW foo_view AS SELECT * FROM foo;
CREATE VIEW bar AS SELECT array_agg(foo_view.bar) FROM foo_view;

Examples

SELECT
    title,
    ARRAY_AGG (
        first_name || ' ' || last_name
        ORDER BY
            last_name
    ) actors
FROM
    film
GROUP BY
    title;