jsonb_object_agg()

Purpose: This is an aggregate function. (Aggregate functions compute a single result from a SETOF input values.) It creates a JSON object whose values are the JSON representations of the aggregated SQL values. It is most useful when these to-be-aggregated values are "row" type values with two fields. The first represents the key and the second represents the value of the intended JSON object's key-value pair.

Signature:

input value:       anyelement
return value:      jsonb

Notes: The syntax "order by... nulls first" within the parentheses of the aggregate function (a generic feature of aggregate functions) isn't useful here because the order of the key-value pairs of a JSON object has no semantic significance. (The ::text typecast of a jsonb object uses the convention of ordering the pairs alphabetically by the key.

do $body$
declare
  object_agg jsonb not null := '"?"';
  expected_object_agg constant jsonb not null :=
    '{"f1": 1, "f2": 2, "f3": null, "f4": 4}'::jsonb;
begin
  with tab as (
    values
      ('f4'::text, 4::int),
      ('f1'::text, 1::int),
      ('f3'::text, null::int),
      ('f2'::text, 2::int))
  select
    jsonb_object_agg(column1, column2)
    into strict object_agg
  from tab;

  assert (object_agg = expected_object_agg), 'unexpected';
end;
$body$;

An object is a set of key-value pairs where each key is unique and the order is undefined and insignificant. (As explained earlier, when a JSON literal is This example emphasizes the property of a JSON object that keys are unique. (See the accounts of the jsonb_set() and jsonb_insert() functions.) This means that if a key-value pair is specified more than once, then the one that is most recently specified wins. You see the same rule at work here:

select ('{"f2": 42, "f7": 7, "f2": null}'::jsonb)::text;

It shows this:

         text
-----------------------
 {"f2": null, "f7": 7}

The DO block specifies both the value for key "f2" and the value for key "f7" twice:

do $body$
declare
  object_agg jsonb not null := '"?"';
  expected_object_agg constant jsonb not null :=
    '{"f2": null, "f7": 7}'::jsonb;
begin
  with tab as (
    values
      ('f2'::text, 4::int),
      ('f7'::text, 7::int),
      ('f2'::text, 1::int),
      ('f2'::text, null::int))
  select
    jsonb_object_agg(column1, column2)
    into strict object_agg
  from tab;

  assert (object_agg = expected_object_agg), 'unexpected';
end;
$body$;