Re: jsonb_object() seems to be buggy. jsonb_build_object() is good.
On 16-Feb-2020, at 16:40, Andrew Dunstan 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 commas to be treated as a single value
> (see syntax details for the exact rules) in the resulting array of text
> values.” But this 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 indeed specifying a text value rather
> than a numeric or integer value. But we know that the double quotes do *not*
> achieve this.
>
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.
Re: jsonb_object() seems to be buggy. jsonb_build_object() is good.
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 commas to be treated as a single value
> (see syntax details for the exact rules) in the resulting array of text
> values.” But this 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 indeed specifying a text value rather
> than a numeric or integer value. But we know that the double quotes do *not*
> achieve this.
>
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 Dunstanhttps://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: jsonb_object() seems to be buggy. jsonb_build_object() is good.
Andrew Dunstan 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 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.
Bryn replied:
We don’t yet support the hstore() function in YugabyteDB. So, meanwhile, I see
no alternative to the approach that I illustrated—whatever that implies for
doing things of which you’re not a fan. That’s why I asked “ What am I
missing?”. But your “ 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 to be treated as a single value (see
syntax details for the exact rules) in the resulting array of text values.” But
this test 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 indeed specifying a text value rather
than a numeric or integer value. But we know that the double quotes do *not*
achieve this.
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 Dunstanhttps://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: jsonb_object() seems to be buggy. jsonb_build_object() is good.
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
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 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?
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
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 Dunstanhttps://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: jsonb_object() seems to be buggy. jsonb_build_object() is good.
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$;
create type t1 as(a int, b varchar);
———
— Test it.
select jsonb_pretty(my_jsonb_build(
'array',
$$
17::integer, 'dog'::varchar, true::boolean
$$));
select jsonb_pretty(my_jsonb_build(
'array',
$$
17::integer,
'dog'::varchar,
true::boolean,
(17::int, 'dog'::varchar)::t1
$$));
select jsonb_pretty(my_jsonb_build(
'object',
$$
'a'::varchar, 17::integer,
'b'::varchar, 'dog'::varchar,
'c'::varchar, true::boolean
$$));
It produces the result that I want. And I’m prepared to pay the price of using
$$ to avoid doubling up interior single quotes..
On 14-Feb-2020, at 19:24, David G. Johnston wrote:
On Friday, February 14, 2020, Bryn Llewellyn mailto:b...@yugabyte.com>> wrote:
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.
The double quotes serve a specific purpose, to allow values containing commas
to be treated as a single value (see syntax details for the exact rules) in the
resulting array of text values. The fact you don’t have to quote the other
strings is a convenience behavior of the feature.
David J.
Re: jsonb_object() seems to be buggy. jsonb_build_object() is good.
On Friday, February 14, 2020, Bryn Llewellyn wrote:
>
>
> 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.
>
The double quotes serve a specific purpose, to allow values containing
commas to be treated as a single value (see syntax details for the exact
rules) in the resulting array of text values. The fact you don’t have to
quote the other strings is a convenience behavior of the feature.
David J.
Re: jsonb_object() seems to be buggy. jsonb_build_object() is good.
On Friday, February 14, 2020, Bryn Llewellyn wrote: > > The doc (“Builds a JSON object out of a text array.”) is simply too terse > to inform an answer to this question. > It does presume knowledge but it precisely defines the outcome: PostgreSQL arrays are typed and all members are of the same type. A text array’s members are all text. Given the above knowledge the fact that the resultant json object contains exclusively text keys and text values directly follows. David J.
Re: jsonb_object() seems to be buggy. jsonb_build_object() is good.
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 text primitive 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
Re: jsonb_object() seems to be buggy. jsonb_build_object() is good.
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 text primitive value? 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.
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 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 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 JSON primitive 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
Re: jsonb_object() seems to be buggy. jsonb_build_object() is good.
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 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 JSON primitive 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
