On 2024-12-03 Tu 9:11 AM, Andrew Dunstan wrote:
On 2024-11-18 Mo 9:25 AM, Yan Chengpeng wrote:
Dear PostgreSQL Hackers,
*Problem Description*
I encountered an issue with the B-Tree ordering of `jsonb` values.
According to the PostgreSQL documentation[1], the ordering should
follow this precedence:
`Object > Array > Boolean > Number > String > Null`
However, empty arrays (`[]`) are currently considered smaller than
`null`, which violates the documented rules. This occurs due to
improper handling of the `rawScalar` flag when comparing arrays in
the `compareJsonbContainers()` function in
`src/backend/utils/adt/jsonb_util.c`.
I agree that this is a (10 year old) bug:
- if (va.val.array.nElems != vb.val.array.nElems)
+ else if (va.val.array.nElems !=
vb.val.array.nElems)
But I don't think we can fix it, because there could well be indexes
that would no longer be valid if we change the sort order. Given that,
I think the best we can do is adjust the documentation to mention the
anomaly.
So the actual sort order as implemented is, AIUI,
Object > Non-Empty-Array > Boolean > Number > String > Null > Empty-Array
which is ugly, but fortunately not many apps rely on jsonb sort order.
Nobody else has commented, so I propose to apply this patch documenting
the anomaly.
cheers
andrew
--
Andrew Dunstan
EDB:https://www.enterprisedb.com
diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml
index 54648c459c..f314d55422 100644
--- a/doc/src/sgml/json.sgml
+++ b/doc/src/sgml/json.sgml
@@ -590,6 +590,7 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@ '$.tags[*] == "qui"';
<replaceable>Array with n elements</replaceable> > <replaceable>array with n - 1 elements</replaceable>
</synopsis>
+ with the exception that (for historical reasons) an empty array sorts less than <replaceable>NULL</replaceable>.
Objects with equal numbers of pairs are compared in the order:
<synopsis>
<replaceable>key-1</replaceable>, <replaceable>value-1</replaceable>, <replaceable>key-2</replaceable> ...
