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

Execute this:

select jsonb_pretty(jsonb_build_object(
  'a'::varchar, 1.7::numeric,
  'b'::varchar, 'dog'::varchar,
  'c'::varchar, true::boolean
  ))

It produces the result that I expect:

 {              +
     "a": 1.7,  +
     "b": "dog",+
     "c": true  +
 }

Notice that the numeric, text, and boolean primitive values are properly rendered with the text value double-quoted and
thenumeric and boolean values unquoted. 

Now execute this supposed functional equivalent:

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

It is meant to be a nice alternative when you want to build an object (rather than an array) because the syntax is less
verbose.

However, it gets the wrong answer, thus:

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

Now, the numeric value and the boolean value are double-quoted—in other words, they have been implicitly converted to
JSONprimitive text values. 

Do you agree that this is a bug?

Notice that I see this behavior in vanilla PostgreSQL 11.2 and in YugabyteDB Version 2.0.11.0. See this blogpost:

“Distributed PostgreSQL on a Google Spanner Architecture—Query Layer”
https://blog.yugabyte.com/distributed-postgresql-on-a-google-spanner-architecture-query-layer/

YugabyteDB uses the PostgreSQL source code for its SQL upper half.

Regards, Bryn Llewellyn, Yugabyte

On 15/02/2020 03:21, Bryn Llewellyn wrote:
> Now execute this supposed functional equivalent:
> 
> select jsonb_pretty(jsonb_object(
>   '{a, 17, b, "dog", c, true}'::varchar[]
>   ))
> 
> It is meant to be a nice alternative when you want to build an object (rather than an array) because the syntax is
lessverbose.
 
> 
> However, it gets the wrong answer, thus:
> 
>  {              +
>      "a": "17", +
>      "b": "dog",+
>      "c": "true"+
>  }
> 
> Now, the numeric value and the boolean value are double-quoted—in other words, they have been implicitly converted to
JSONprimitive text values.
 

They haven't been implicitly converted, you gave an array of varchars.
How should it know that you don't want texts?

> Do you agree that this is a bug?
No.
-- 
Vik Fearing

This:

select jsonb_pretty(jsonb_build_object(
 'a'::varchar, 1.7::numeric,
 'b'::varchar, 'dog'::varchar,
 'c'::varchar, true::boolean
 ))

allows me to express what I want. That’s a good thing. Are you saying that this:

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

simply lacks that power of expression and that every item in the array is assumed to be intended to end up as a JSON
textprimitive value? In other words, do the double quotes around "dog" have no effect? That would be a bad thing—and it
wouldlimit the usefulness of the jsonb_object() function. 

The doc (“Builds a JSON object out of a text array.”) is simply too terse to inform an answer to this question.

On 14-Feb-2020, at 18:28, Vik Fearing <vik@postgresfriends.org> wrote:

On 15/02/2020 03:21, Bryn Llewellyn wrote:
> Now execute this supposed functional equivalent:
>
> select jsonb_pretty(jsonb_object(
>  '{a, 17, b, "dog", c, true}'::varchar[]
>  ))
>
> It is meant to be a nice alternative when you want to build an object (rather than an array) because the syntax is
lessverbose. 
>
> However, it gets the wrong answer, thus:
>
> {              +
>     "a": "17", +
>     "b": "dog",+
>     "c": "true"+
> }
>
> Now, the numeric value and the boolean value are double-quoted—in other words, they have been implicitly converted to
JSONprimitive text values. 

They haven't been implicitly converted, you gave an array of varchars.
How should it know that you don't want texts?

> Do you agree that this is a bug?
No.
--
Vik Fearing

On 15/02/2020 04:07, Bryn Llewellyn wrote:
> This:
> 
> select jsonb_pretty(jsonb_build_object(
>  'a'::varchar, 1.7::numeric,
>  'b'::varchar, 'dog'::varchar,
>  'c'::varchar, true::boolean
>  ))
> 
> allows me to express what I want. That’s a good thing. Are you saying that this:
> 
> select jsonb_pretty(jsonb_object(
>  '{a, 17, b, "dog", c, true}'::varchar[]
>  ))
> 
> simply lacks that power of expression and that every item in the array is assumed to be intended to end up as a JSON
textprimitive value? In other words, do the double quotes around "dog" have no effect?
 

That is correct.

> That would be a bad thing—and it would limit the usefulness of the jsonb_object() function.

Use the long form if you need to mix datatypes.
-- 
Vik Fearing

The doc (“Builds a JSON object out of a text array.”) is simply too terse to inform an answer to this question.

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.

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

Bryn Llewellyn wrote:

> ...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$;
>

Andrew replied

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.

Bryn replies:

Ah… I didn’t know about the bottom-posting rule.

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?

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
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.


cheers


andrew


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

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. 

On 2/16/20 7:25 PM, Bryn Llewellyn wrote:
>
> B.t.w., you earlier said “The double quotes  [around “dog”] serve a specific purpose, to allow values containing
commasto be treated as a single value (see syntax details for the exact rules) in the resulting array of text values.”
Butthis test shows that they are not needed for that purpose:
 


I didn't say that. Someone else did.


>
> 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.
 
>


No, you haven't understood what they said. If the field value contains a
comma it needs to be quoted. But none of the fields in your example do.
If your field were "dog,house" instead of "dog house" it would need to
be quoted. This had nothing to do with json, BTW, it's simply from the
rules for array literals.


cheers


andrew


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

On 16-Feb-2020, at 16:40, Andrew Dunstan <andrew.dunstan@2ndquadrant.com> wrote:

On 2/16/20 7:25 PM, Bryn Llewellyn wrote:
>
> B.t.w., you earlier said “The double quotes  [around “dog”] serve a specific purpose, to allow values containing
commasto be treated as a single value (see syntax details for the exact rules) in the resulting array of text values.”
Butthis test shows that they are not needed for that purpose: 


I didn't say that. Someone else did.


>
> 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. 
>


No, you haven't understood what they said. If the field value contains a
comma it needs to be quoted. But none of the fields in your example do.
If your field were "dog,house" instead of "dog house" it would need to
be quoted. This had nothing to do with json, BTW, it's simply from the
rules for array literals.

Bryn replied:

Got it! Thanks for helping me out, Andrew.