Re: jsonb_object() seems to be buggy. jsonb_build_object() is good. - Mailing list pgsql-hackers

From Andrew Dunstan
Subject Re: jsonb_object() seems to be buggy. jsonb_build_object() is good.
Date
Msg-id 762adea3-efca-3f19-561b-af6b5e33beaa@2ndQuadrant.com
Whole thread Raw
In response to Re: jsonb_object() seems to be buggy. jsonb_build_object() is good.  (Bryn Llewellyn <bryn@yugabyte.com>)
Responses Re: jsonb_object() seems to be buggy. jsonb_build_object() is good.
List pgsql-hackers
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




pgsql-hackers by date:

Previous
From: Michael Paquier
Date:
Subject: Re: pgsql: walreceiver uses a temporary replication slot by default
Next
From: Andrew Dunstan
Date:
Subject: Re: Parallel copy