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.

JSON objects and their keys.

A JSON object is a set of key-value pairs where each key is (taken to be) unique and the order is undefined and insignificant. (See also the accounts of the jsonb_set() and jsonb_insert() functions.)

This means that if a key-value pair is specified more than once, in any context that defines an object value, then the one that is most recently specified wins. Here's a simple example:

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

This is the result:

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

(In the other direction, the ::text typecast of a jsonb object uses the convention of ordering the pairs alphabetically by the key.)

Try this demonstration of the jsonb_object_agg() function:

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$;

It finishes silently, showing that the assert holds.

Now try this counter-example. 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$;

It, too, finishes silently, showing that, again, the assert holds.