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