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 <[email protected]> wrote:
On Friday, February 14, 2020, Bryn Llewellyn <[email protected]
<mailto:[email protected]>> 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.
