json_object returning jsonb reuslt different from returning json, returning text
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:
Show quoted text
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
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:
Show quoted text
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
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
Attachments:
jsonb-uniquefy-fix.patchtext/x-patch; charset=UTF-8; name=jsonb-uniquefy-fix.patchDownload
diff --git a/src/backend/utils/adt/jsonb_util.c b/src/backend/utils/adt/jsonb_util.c
index aa151a53d6..21d874c098 100644
--- a/src/backend/utils/adt/jsonb_util.c
+++ b/src/backend/utils/adt/jsonb_util.c
@@ -1959,8 +1959,18 @@ uniqueifyJsonbObject(JsonbValue *object, bool unique_keys, bool skip_nulls)
if (hasNonUniq || skip_nulls)
{
- JsonbPair *ptr = object->val.object.pairs + 1,
- *res = object->val.object.pairs;
+ JsonbPair *ptr, *res;
+
+ while (skip_nulls && object->val.object.nPairs > 0 &&
+ object->val.object.pairs->value.type == jbvNull)
+ {
+ /* If skip_nulls is true, remove leading items with null */
+ object->val.object.pairs++;
+ object->val.object.nPairs--;
+ }
+
+ ptr = object->val.object.pairs + 1;
+ res = object->val.object.pairs;
while (ptr - object->val.object.pairs < object->val.object.nPairs)
{
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