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`.
Example to Reproduce the Issue
```sql
postgres=# -- Create a test table with a jsonb column
CREATE TABLE jsonb_test (j jsonb PRIMARY KEY);
-- Insert various jsonb values
INSERT INTO jsonb_test VALUES ('null');
INSERT INTO jsonb_test VALUES ('true');
INSERT INTO jsonb_test VALUES ('false');
INSERT INTO jsonb_test VALUES ('0');
INSERT INTO jsonb_test VALUES ('1');
INSERT INTO jsonb_test VALUES ('"string"');
INSERT INTO jsonb_test VALUES ('[]');
INSERT INTO jsonb_test VALUES ('[1, 2, 3]');
INSERT INTO jsonb_test VALUES ('{}');
INSERT INTO jsonb_test VALUES ('{"a": 1}');
-- Query the table to check ordering
SELECT * FROM jsonb_test ORDER BY j;
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
j
-----------
[]
null
"string"
0
1
false
true
[1, 2, 3]
{}
{"a": 1}
(10 rows)
```
The empty array ([]) is incorrectly placed before null.
Analysis
The issue stems from how the rawScalar flag is evaluated in the
compareJsonbContainers() function. When comparing arrays, the function does not
prioritize the rawScalar flag before comparing the number of elements (nElems),
leading to incorrect ordering for arrays treated as “raw scalars.”
Proposed Fix
The proposed fix ensures the rawScalar flag is checked first, and only when
both values have the same flag, the number of elements is compared. This
guarantees correct ordering of arrays and scalar values. The details are in the
attached patch.
Testing
I have added new test cases to validate the B-Tree ordering of various `jsonb`
values, including:
1. Scalars (null, true, false, numbers, strings).
2. Arrays (empty, single-element, nested).
3. Objects (empty, single-key, nested).
4. Mixed types (arrays containing objects, scalars, etc.).
The test cases are included in jsonb.sql and the corresponding expected output
file.
I have run make check, and all tests pass successfully.
I would appreciate feedback on the proposed solution or suggestions for
improvement.
References
[1] JSON Types - https://www.postgresql.org/docs/current/datatype-json.html
Best regards,
Chengpeng(Jack) Yan
v1-0001-fix-jsonb-compare.patch
Description: v1-0001-fix-jsonb-compare.patch
