Content-Type: multipart/mixed; boundary="------------rY2y6m0qCeEK7sph0uq047Id"
Message-ID: <2a30c387-3c59-59a2-cd24-e85b7ab17e80@dunslane.net>
Date: Mon, 25 Apr 2022 10:14:41 -0400
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.5.0
Subject: Re: json_object returning jsonb reuslt different from returning json,
 returning text
Content-Language: en-US
To: alias <postgres.rocks@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: <CAJA4AWQ_XbSmsNbW226UqNyRLJ+wb=iQkQMj77cQyoNkqtf=2Q@mail.gmail.com>
 <CAJA4AWTJWt06LA5CAxuFb39X5KmD8fF0JeC1Qrb_jXuR8Lvcyg@mail.gmail.com>
From: Andrew Dunstan <andrew@dunslane.net>
In-Reply-To: <CAJA4AWTJWt06LA5CAxuFb39X5KmD8fF0JeC1Qrb_jXuR8Lvcyg@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/2a30c387-3c59-59a2-cd24-e85b7ab17e80%40dunslane.net>
Precedence: bulk

This is a multi-part message in MIME format.
--------------rY2y6m0qCeEK7sph0uq047Id
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit


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

--------------rY2y6m0qCeEK7sph0uq047Id
Content-Type: text/x-patch; charset=UTF-8; name="jsonb-uniquefy-fix.patch"
Content-Disposition: attachment; filename="jsonb-uniquefy-fix.patch"
Content-Transfer-Encoding: base64

ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3V0aWxzL2FkdC9qc29uYl91dGlsLmMgYi9zcmMv
YmFja2VuZC91dGlscy9hZHQvanNvbmJfdXRpbC5jCmluZGV4IGFhMTUxYTUzZDYuLjIxZDg3
NGMwOTggMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL3V0aWxzL2FkdC9qc29uYl91dGlsLmMK
KysrIGIvc3JjL2JhY2tlbmQvdXRpbHMvYWR0L2pzb25iX3V0aWwuYwpAQCAtMTk1OSw4ICsx
OTU5LDE4IEBAIHVuaXF1ZWlmeUpzb25iT2JqZWN0KEpzb25iVmFsdWUgKm9iamVjdCwgYm9v
bCB1bmlxdWVfa2V5cywgYm9vbCBza2lwX251bGxzKQogCiAJaWYgKGhhc05vblVuaXEgfHwg
c2tpcF9udWxscykKIAl7Ci0JCUpzb25iUGFpciAgKnB0ciA9IG9iamVjdC0+dmFsLm9iamVj
dC5wYWlycyArIDEsCi0JCQkJICAgKnJlcyA9IG9iamVjdC0+dmFsLm9iamVjdC5wYWlyczsK
KwkJSnNvbmJQYWlyICAqcHRyLCAqcmVzOworCisJCXdoaWxlIChza2lwX251bGxzICYmIG9i
amVjdC0+dmFsLm9iamVjdC5uUGFpcnMgPiAwICYmCisJCQkgICBvYmplY3QtPnZhbC5vYmpl
Y3QucGFpcnMtPnZhbHVlLnR5cGUgPT0gamJ2TnVsbCkKKwkJeworCQkJLyogSWYgc2tpcF9u
dWxscyBpcyB0cnVlLCByZW1vdmUgbGVhZGluZyBpdGVtcyB3aXRoIG51bGwgKi8KKwkJCW9i
amVjdC0+dmFsLm9iamVjdC5wYWlycysrOworCQkJb2JqZWN0LT52YWwub2JqZWN0Lm5QYWly
cy0tOworCQl9CisKKwkJcHRyID0gb2JqZWN0LT52YWwub2JqZWN0LnBhaXJzICsgMTsKKwkJ
cmVzID0gb2JqZWN0LT52YWwub2JqZWN0LnBhaXJzOwogCiAJCXdoaWxlIChwdHIgLSBvYmpl
Y3QtPnZhbC5vYmplY3QucGFpcnMgPCBvYmplY3QtPnZhbC5vYmplY3QublBhaXJzKQogCQl7
Cg==

--------------rY2y6m0qCeEK7sph0uq047Id--