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

On 2/16/20 1:40 PM, Bryn Llewellyn wrote:
>
> Andrew replied
>
> 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.
>
> Bryn replies:
>
>
> Of course I didn’t show error handling. Doing so would have increased the source text size and made it harder to appreciate the point.
>
> I used dynamic SQL because I was modeling the use case where on-the-fly analysis determines what JSON object or array must be built—i.e. the number of components and the datatype of each. It’s nice that jsonb_build_object() and jsonb_build_array() accommodate this dynamic need by being variadic. But I can’t see a way to wrote the invocation using only static code.
>
> What am I missing?

Probably not much, These functions work best from application code which
builds up the query. But if you do that and then call a function which
in turn calls execute you get a double whammy of interpreter overhead.
I'm also not a fan of functions that in effect take bits of SQL text and
interpolate them into a query in plpgsql, like your query does.

json_object() is meant to be an analog of the hstore() function that
takes one or two text arrays and return an hstore. Of course, it doesn't
have the issue you complained about, since all values in an hstore are
strings.

cheers

andrew

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