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

On 2/15/20 12:06 AM, Bryn Llewellyn wrote:
> 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$;
>

Please don't top-post on PostgreSQL lists.  See
<http://idallen.com/topposting.html>

The function above has many deficiencies, including lack of error
checking and use of 'execute' which will significantly affect
performance. Still, if it works for you, that's your affair.

These functions were written to accommodate PostgreSQL limitations. We
don't have a heterogenous array type.  So json_object() will return an
object where all the values are strings, even if they look like numbers,
booleans etc. And indeed, this is shown in the documented examples.
jsonb_build_object and jsonb_build_array overcome that issue, but there
the PostgreSQL limitation is that you can't pass in an actual array as
the variadic element, again because we don't have heterogenous arrays.

cheers

andrew

--
Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services