Thread: json_object returning jsonb reuslt different from returning json, returning text

json_object returning jsonb reuslt different from returning json, returning text

From

alias

Date:

25 April 2022, 08:11:42

select json_objectagg(
    k:v absent on null with unique keys returning text )
from (
    values(1,1),(0, null),(3, null),(2,2),(4,null)
) foo(k, v);

return

json_objectagg

----------------------

{ "1" : 1, "2" : 2 }

--------------------

select json_objectagg(k:v absent on null with unique keys)
from (
    values(1,1),(0, null),(3, null),(2,2),(4,null)
) foo(k, v);

return

json_objectagg ---------------------- { "1" : 1, "2" : 2 }

But

select json_objectagg(
    k:v absent on null with unique keys returning jsonb )
from (
    values(1,1),(0, null),(3, null),(2,2),(4,null)
) foo(k, v);

return

json_objectagg ----------------------------- {"0": null, "1": 1, "2": 2}

the last query "returning jsonb" should be { "1" : 1, "2" : 2 } ?

version:

PostgreSQL 15devel (Ubuntu 15~~devel~20220407.0430-1~713.git79b716c.pgdg20.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit

Re: json_object returning jsonb reuslt different from returning json, returning text

From

alias

Date:

25 April 2022, 08:19:23

seems it's a bug around value 0.

SELECT JSON_OBJECTAGG(k: v ABSENT ON NULL WITH UNIQUE KEYS RETURNING jsonb)

FROM (VALUES (1, 1), (10, NULL),(4, null), (5, null),(6, null),(2, 2)) foo(k, v);

return:

{"1": 1, "2": 2}

SELECT JSON_OBJECTAGG(k: v ABSENT ON NULL WITH UNIQUE KEYS RETURNING jsonb)

FROM (VALUES (1, 1), (0, NULL),(4, null), (5, null),(6, null),(2, 2)) foo(k, v);

return

{"0": null, "1": 1, "2": 2}

On Mon, Apr 25, 2022 at 10:41 AM alias <postgres.rocks@gmail.com> wrote:

select json_objectagg(
    k:v absent on null with unique keys returning text )
from (
    values(1,1),(0, null),(3, null),(2,2),(4,null)
) foo(k, v);
return

json_objectagg
----------------------
{ "1" : 1, "2" : 2 }
--------------------
select json_objectagg(k:v absent on null with unique keys)
from (
    values(1,1),(0, null),(3, null),(2,2),(4,null)
) foo(k, v);
return

json_objectagg ---------------------- { "1" : 1, "2" : 2 }

But
select json_objectagg(
    k:v absent on null with unique keys returning jsonb )
from (
    values(1,1),(0, null),(3, null),(2,2),(4,null)
) foo(k, v);
return
json_objectagg ----------------------------- {"0": null, "1": 1, "2": 2}

the last query "returning jsonb" should be { "1" : 1, "2" : 2 } ?

version:
PostgreSQL 15devel (Ubuntu 15~~devel~20220407.0430-1~713.git79b716c.pgdg20.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit

Re: json_object returning jsonb reuslt different from returning json, returning text

From

Andrew Dunstan

Date:

25 April 2022, 17:14:41

On 2022-04-25 Mo 01:19, alias wrote:
>
> seems it's a bug around value 0.
>
> SELECT JSON_OBJECTAGG(k: v ABSENT ON NULL WITH UNIQUE KEYS RETURNING
> jsonb)
> FROM (VALUES (1, 1), (10, NULL),(4, null), (5, null),(6, null),(2, 2))
> foo(k, v);
> return:
> {"1": 1, "2": 2}
>
> SELECT JSON_OBJECTAGG(k: v ABSENT ON NULL WITH UNIQUE KEYS RETURNING
> jsonb)
> FROM (VALUES (1, 1), (0, NULL),(4, null), (5, null),(6, null),(2, 2))
> foo(k, v);
>
> return
>  {"0": null, "1": 1, "2": 2}

Thanks for the report.

I don't think there's anything special about '0' except that it sorts
first. There appears to be a bug in the uniquefying code where the first
item(s) have nulls. The attached appears to fix it. Please test and see
if you can break it.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

Attachment

jsonb-uniquefy-fix.patch

Re: json_object returning jsonb reuslt different from returning json, returning text

From

Andrew Dunstan

Date:

28 April 2022, 22:35:24

On 2022-04-25 Mo 10:14, Andrew Dunstan wrote:
> On 2022-04-25 Mo 01:19, alias wrote:
>> seems it's a bug around value 0.
>>
>> SELECT JSON_OBJECTAGG(k: v ABSENT ON NULL WITH UNIQUE KEYS RETURNING
>> jsonb)
>> FROM (VALUES (1, 1), (10, NULL),(4, null), (5, null),(6, null),(2, 2))
>> foo(k, v);
>> return:
>> {"1": 1, "2": 2}
>>
>> SELECT JSON_OBJECTAGG(k: v ABSENT ON NULL WITH UNIQUE KEYS RETURNING
>> jsonb)
>> FROM (VALUES (1, 1), (0, NULL),(4, null), (5, null),(6, null),(2, 2))
>> foo(k, v);
>>
>> return
>>  {"0": null, "1": 1, "2": 2}
>
> Thanks for the report.
>
> I don't think there's anything special about '0' except that it sorts
> first. There appears to be a bug in the uniquefying code where the first
> item(s) have nulls. The attached appears to fix it. Please test and see
> if you can break it.


Fix pushed.


cheers


andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com