jsonb_object_agg function

The jsonb_object_agg(keys, values) aggregate function zips together keys and values into a jsonb object. The input values to the aggregate can be filtered.

Syntax

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

Signatures

Return value

jsonb_object_agg returns the aggregated key–value pairs as a jsonb object. Each row in the input corresponds to one key–value pair in the output.

If there are duplicate keys in the input, it is unspecified which key–value pair is retained in the output.

If keys is null for any input row, that entry pair will be dropped.

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.

Usage in dataflows

While jsonb_object_agg is available in Materialize, materializing jsonb_object_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 jsonb_object_agg function call and create a non-materialized view using jsonb_object_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 jsonb_object_agg(key_col, val_col) FROM foo_view;

Examples

Consider this query:

SELECT
  jsonb_object_agg(
    t.col1,
    t.col2
    ORDER BY t.ts ASC
  ) FILTER (WHERE t.col2 IS NOT NULL) AS my_agg
FROM (
  VALUES
  ('k1', 1, now()),
  ('k2', 2, now() - INTERVAL '1s'),
  ('k2', -1, now()),
  ('k2', NULL, now() + INTERVAL '1s')
  ) AS t(col1, col2, ts);

      my_agg
------------------
 {"k1": 1, "k2": -1}

In this example, there are multiple values associated with the k2 key.

The FILTER clause in the statement above returns values that are not NULL and orders them by the timestamp column to return the most recent associated value.

See also

• jsonb_agg