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

From: Andrew Dunstan <[email-masked]>
Date: Mon, 25 Apr 2022 10:14:41 -0400

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
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)
 		{

---

From: Andrew Dunstan <[email-masked]>
Date: Thu, 28 Apr 2022 15:35:24 -0400

> item(s) have nulls. The attached appears to fix it. Please test and see

Fix pushed.

cheers

andrew