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

Andrew Dunstan <andrew.dunstan@2ndquadrant.com> wrote:

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
appreciatethe point. 
>
> I used dynamic SQL because I was modeling the use case where on-the-fly analysis determines what JSON object or array
mustbe 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
onlystatic 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.

Bryn replied:

We don’t yet support the hstore() function in YugabyteDB. So, meanwhile, I see no alternative to the approach that I
illustrated—whateverthat implies for doing things of which you’re not a fan. That’s why I asked “ What am I missing?”.
Butyour “ Probably not much” seems, then, to force my hand. 

B.t.w., you earlier said “The double quotes  [around “dog”] serve a specific purpose, to allow values containing commas
tobe treated as a single value (see syntax details for the exact rules) in the resulting array of text values.” But
thistest shows that they are not needed for that purpose: 

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

This is the result:

 {                    +
     "a": "17",       +
     "b": "dog house",+
     "c": "true"      +
 }

The commas are sufficient separators.

It seems to me, therefore, that writing the double quotes gives the wrong message: they make it look like you are
indeedspecifying a text value rather than a numeric or integer value. But we know that the double quotes do *not*
achievethis.