Home  /  SQL commands  /  SQL functions & operators

map_agg function

The map_agg(keys, values) aggregate function zips together keys and values into a map.

The input values to the aggregate can be filtered.

Syntax

map_agg ( <keys>, <values>
  [ORDER BY <col_ref> [ASC | DESC] [NULLS FIRST | NULLS LAST] [, ...]]
)
[FILTER (WHERE <filter_clause>)]
Syntax element Description
<keys> The keys to aggregate. Must be of type text.
<values> The values to aggregate.
ORDER BY <col_ref> [ASC | DESC] [NULLS FIRST | NULLS LAST] [, …] Optional. Specifies the ordering of values within the aggregation. If not specified, incoming rows are not guaranteed any order.
FILTER (WHERE <filter_clause>) Optional. Specifies which rows are sent to the aggregate function. Rows for which the <filter_clause> evaluates to true contribute to the aggregation. See Aggregate function filters for details.

Signatures

Parameter Type Description
keys text The keys to aggregate.
values any The values to aggregate.

Return value

map_agg returns the aggregated key–value pairs as a map.

  • Each row in the input corresponds to one key–value pair in the output, unless the key is null, in which case the pair is ignored. (map keys must be non-NULL strings.)
  • If multiple rows have the same key, we retain only the value sorted in the greatest/last position. You can determine this order using ORDER BY within the aggregate function itself; otherwise, incoming rows are not guaranteed to be handled in any order.

Usage in dataflows

While map_agg is available in Materialize, materializing map_agg(expression) 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 map_agg function call and create a non-materialized view using map_agg on top of that. That pattern is illustrated in the following statements:

CREATE MATERIALIZED VIEW foo_view AS SELECT key_col, val_col FROM foo;
CREATE VIEW bar AS SELECT map_agg(key_col, val_col) FROM foo_view;

Examples

Consider this query:

SELECT
  map_agg(
    t.k,
    t.v
    ORDER BY t.ts ASC, t.v DESC
  ) FILTER (WHERE t.v != -8) AS my_agg
FROM (
  VALUES
    -- k1
    ('k1', 3, now()),
    ('k1', 2, now() + INTERVAL '1s'),
    ('k1', 1, now() + INTERVAL '1s'),
    -- k2
    ('k2', -9, now() - INTERVAL '1s'),
    ('k2', -8, now()),
    ('k2', NULL, now() + INTERVAL '1s'),
    -- null
    (NULL, 99, now()),
    (NULL, 100, now())
  ) AS t(k, v, ts);

      my_agg
------------------
 {k1=>1,k2=>-9}

In this example:

  • We order values by their timestamp (t.ts ASC) and then break ties using the smallest values (t.v DESC).
  • We filter out any values equal to exactly -8.
  • All keys with a NULL value get excluded automatically.
  • k1 has two values tied with the same t.ts value; because we’ve also ordered t.v DESC, the last value we see will be 1.
  • k2 has its value for -8 filtered out FILTER (WHERE t.v != -8); however, this FILTER also removes the NULL value at now() + INTERVAL '1s' because WHERE null != -8 evaluates to false.
Back to top ↑