jsonb_build_object() and json_build_object()
Purpose: Create a JSON object from a variadic list that specifies keys with values of arbitrary SQL data type.
Signature For the jsonb
variant:
input value: VARIADIC "any"
return value: jsonb
Notes: The key names are given as SQL text
values. The SQL data type of key's value argument must have a direct JSON equivalent or allow implicit conversion to such an equivalent.
The jsonb_build_object()
variadic function is the obvious counterpart to jsonb_build_array()
.
The argument list has the form:
key1::text, value1::the_data_type1,
key1::text, value2::the_data_type2,
...
keyN::text, valueN::the_data_typeN
Use this ysqlsh
script to create the required type "t" and then to execute the ASSERT
.
create type t as (a int, b text);
do $body$
declare
v_17 constant int := 17;
v_dog constant text := 'dog';
v_true constant boolean := true;
v_t constant t := (17::int, 'cat'::text);
j constant jsonb := jsonb_build_object(
'a', v_17,
'b', v_dog,
'c', v_true,
'd', v_t);
expected_j constant jsonb :=
'{"a": 17, "b": "dog", "c": true, "d": {"a": 17, "b": "cat"}}';
begin
assert
j = expected_j,
'unexpected';
end;
$body$;
Just as with jsonb_build_array()
, using jsonb_build_object()
is straightforward when you know the structure of your target JSON value statically, and just discover the values dynamically. But again, it doesn't accommodate the case that you discover the desired structure dynamically.
The following ysqlsh
script shows a feasible general workaround for this use case. The helper function "f()" generates the variadic argument list as the text representation of a comma-separated list of SQL literals of various data types. Then it invokes jsonb_build_object()
dynamically. Obviously this brings a performance cost. But you might not have an alternative.
create function f(variadic_array_elements in text) returns jsonb
language plpgsql
as $body$
declare
stmt text := '
select jsonb_build_object('||variadic_array_elements||')';
j jsonb;
begin
execute stmt into j;
return j;
end;
$body$;
-- Relies on "type t as (a int, b text)" created above.
do $body$
declare
v_17 constant int := 17;
v_dog constant text := 'dog';
v_true constant boolean := true;
v_t constant t := (17::int, 'cat'::text);
expected_j constant jsonb := jsonb_build_object(
'a', v_17,
'b', v_dog,
'c', v_true,
'd', v_t);
j constant jsonb := f(
$$
'a'::text, 17::integer,
'b'::text, 'dog'::text,
'c'::text, true::boolean,
'd'::text, (17::int, 'cat'::text)::t
$$);
begin
assert
j = expected_j,
'unexpected';
end;
$body$;