Re: jsonb_object() seems to be buggy. jsonb_build_object() is good.

Thank you both, Vik, and David, for bing so quick to respond. All is clear now. It seems to me that the price (giving up the ability to say explicitly what primitive JSON values you want) is too great to pay for the benefit (being able to build the semantic equivalent of a variadic list of actual arguments as text.

So I wrote my own wrapper for jsonb_build_array() and jsonb_build_object():

create function my_jsonb_build(
kind in varchar,
variadic_elements in varchar)
returns jsonb
immutable
language plpgsql
as $body$
declare
stmt varchar :=
case kind
when 'array' then
'select jsonb_build_array('||variadic_elements||')'
when 'object' then
'select jsonb_build_object('||variadic_elements||')'
end;
j jsonb;
begin
execute stmt into j;
return j;
end;
$body$;

create type t1 as(a int, b varchar);

———————————————————————————————————
— Test it.

select jsonb_pretty(my_jsonb_build(
'array',
$$
17::integer, 'dog'::varchar, true::boolean
$$));

select jsonb_pretty(my_jsonb_build(
'array',
$$
17::integer,
'dog'::varchar,
true::boolean,
(17::int, 'dog'::varchar)::t1
$$));

select jsonb_pretty(my_jsonb_build(
'object',
$$
'a'::varchar, 17::integer,
'b'::varchar, 'dog'::varchar,
'c'::varchar, true::boolean
$$));

It produces the result that I want. And I’m prepared to pay the price of using $$ to avoid doubling up interior single quotes..

On 14-Feb-2020, at 19:24, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

On Friday, February 14, 2020, Bryn Llewellyn <bryn(at)yugabyte(dot)com <mailto:bryn(at)yugabyte(dot)com>> wrote:

select jsonb_pretty(jsonb_object(
'{a, 17, b, "dog", c, true}'::varchar[]
))

In other words, do the double quotes around "dog" have no effect? That would be a bad thing—and it would limit the usefulness of the jsonb_object() function.

The double quotes serve a specific purpose, to allow values containing commas to be treated as a single value (see syntax details for the exact rules) in the resulting array of text values. The fact you don’t have to quote the other strings is a convenience behavior of the feature.

David J.