[PATCH] Fix jsonb comparison for raw scalar pseudo arrays
Dear PostgreSQL Hackers,
Problem Description
I encountered an issue with the B-Tree ordering of `jsonb` values. According to the PostgreSQL documentation[1]JSON Types - https://www.postgresql.org/docs/current/datatype-json.html, 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
Attachments:
v1-0001-fix-jsonb-compare.patchapplication/octet-stream; name=v1-0001-fix-jsonb-compare.patchDownload
From 54ab7c97cf6ccea090b5b69dfc5c00467fcf1b10 Mon Sep 17 00:00:00 2001
From: yanchengpeng <chengpeng.yan@protonbase.io>
Date: Mon, 18 Nov 2024 13:48:32 +0000
Subject: [PATCH] Fix jsonb comparison for raw scalar pseudo arrays
---
src/backend/utils/adt/jsonb_util.c | 10 ++--
src/test/regress/expected/jsonb.out | 88 +++++++++++++++++++++++++++++
src/test/regress/sql/jsonb.sql | 56 ++++++++++++++++++
3 files changed, 150 insertions(+), 4 deletions(-)
diff --git a/src/backend/utils/adt/jsonb_util.c b/src/backend/utils/adt/jsonb_util.c
index 9941daad2b..8b29b312d2 100644
--- a/src/backend/utils/adt/jsonb_util.c
+++ b/src/backend/utils/adt/jsonb_util.c
@@ -239,14 +239,16 @@ compareJsonbContainers(JsonbContainer *a, JsonbContainer *b)
case jbvArray:
/*
- * This could be a "raw scalar" pseudo array. That's
+ * This could be a "raw scalar" pseudo array. That's
* a special case here though, since we still want the
- * general type-based comparisons to apply, and as far
- * as we're concerned a pseudo array is just a scalar.
+ * general type-based comparisons to apply. As far
+ * as we're concerned, a pseudo array is just a scalar.
+ * If both are either raw scalars or both are arrays,
+ * compare the number of elements.
*/
if (va.val.array.rawScalar != vb.val.array.rawScalar)
res = (va.val.array.rawScalar) ? -1 : 1;
- if (va.val.array.nElems != vb.val.array.nElems)
+ else if (va.val.array.nElems != vb.val.array.nElems)
res = (va.val.array.nElems > vb.val.array.nElems) ? 1 : -1;
break;
case jbvObject:
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 7d163a156e..1043b8eee1 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -5715,3 +5715,91 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
12345
(1 row)
+-- Create a table for JSONB ordering tests
+CREATE TABLE jsonb_test_order (j jsonb PRIMARY KEY);
+-- Insert JSONB values for scalar types
+INSERT INTO jsonb_test_order VALUES
+ ('null'),
+ ('true'),
+ ('false'),
+ ('0'),
+ ('3.14159'),
+ ('-3.14159'),
+ ('1E-10'),
+ ('1E+10'),
+ ('"A"'),
+ ('"a"'),
+ ('""');
+-- Insert JSONB values for arrays
+INSERT INTO jsonb_test_order VALUES
+ ('[]'),
+ ('[null]'),
+ ('[true]'),
+ ('[false]'),
+ ('[0]'),
+ ('[1, 2, 3]'),
+ ('[3, 2, 1]'),
+ ('["a", "b", "c"]'),
+ ('["c", "b", "a"]'),
+ ('[[1, 2], [3, 4]]'),
+ ('[[]]');
+-- Insert JSONB values for objects
+INSERT INTO jsonb_test_order VALUES
+ ('{}'),
+ ('{"a": null}'),
+ ('{"a": true}'),
+ ('{"a": false}'),
+ ('{"a": 1}'),
+ ('{"b": 1}'),
+ ('{"a": "a"}'),
+ ('{"a": {"nested": true}}'),
+ ('{"a": [1, 2, 3]}');
+-- Insert mixed JSONB values
+INSERT INTO jsonb_test_order VALUES
+ ('[{}, [], null, ""]'),
+ ('[{"a": 1}, [2, 3], false]'),
+ ('{"nested": {"key": "value"}}'),
+ ('[{"array": [1, 2]}, {"array": [3, 4]}]');
+-- Select all rows, ordered by the JSONB column
+SELECT * FROM jsonb_test_order ORDER BY j;
+ j
+----------------------------------------
+ null
+ ""
+ "A"
+ "a"
+ -3.14159
+ 0
+ 0.0000000001
+ 3.14159
+ 10000000000
+ false
+ true
+ []
+ [null]
+ [0]
+ [false]
+ [true]
+ [[]]
+ [[1, 2], [3, 4]]
+ [{"array": [1, 2]}, {"array": [3, 4]}]
+ ["a", "b", "c"]
+ ["c", "b", "a"]
+ [1, 2, 3]
+ [3, 2, 1]
+ [{"a": 1}, [2, 3], false]
+ [{}, [], null, ""]
+ {}
+ {"a": null}
+ {"a": "a"}
+ {"a": 1}
+ {"a": false}
+ {"a": true}
+ {"a": [1, 2, 3]}
+ {"a": {"nested": true}}
+ {"b": 1}
+ {"nested": {"key": "value"}}
+(35 rows)
+
+-- Clean up the test table
+DROP TABLE jsonb_test_order;
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index 5f0190d5a2..e2ff06c4a9 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -1559,3 +1559,59 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::float8;
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int2;
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int4;
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
+
+-- Create a table for JSONB ordering tests
+CREATE TABLE jsonb_test_order (j jsonb PRIMARY KEY);
+
+-- Insert JSONB values for scalar types
+INSERT INTO jsonb_test_order VALUES
+ ('null'),
+ ('true'),
+ ('false'),
+ ('0'),
+ ('3.14159'),
+ ('-3.14159'),
+ ('1E-10'),
+ ('1E+10'),
+ ('"A"'),
+ ('"a"'),
+ ('""');
+
+-- Insert JSONB values for arrays
+INSERT INTO jsonb_test_order VALUES
+ ('[]'),
+ ('[null]'),
+ ('[true]'),
+ ('[false]'),
+ ('[0]'),
+ ('[1, 2, 3]'),
+ ('[3, 2, 1]'),
+ ('["a", "b", "c"]'),
+ ('["c", "b", "a"]'),
+ ('[[1, 2], [3, 4]]'),
+ ('[[]]');
+
+-- Insert JSONB values for objects
+INSERT INTO jsonb_test_order VALUES
+ ('{}'),
+ ('{"a": null}'),
+ ('{"a": true}'),
+ ('{"a": false}'),
+ ('{"a": 1}'),
+ ('{"b": 1}'),
+ ('{"a": "a"}'),
+ ('{"a": {"nested": true}}'),
+ ('{"a": [1, 2, 3]}');
+
+-- Insert mixed JSONB values
+INSERT INTO jsonb_test_order VALUES
+ ('[{}, [], null, ""]'),
+ ('[{"a": 1}, [2, 3], false]'),
+ ('{"nested": {"key": "value"}}'),
+ ('[{"array": [1, 2]}, {"array": [3, 4]}]');
+
+-- Select all rows, ordered by the JSONB column
+SELECT * FROM jsonb_test_order ORDER BY j;
+
+-- Clean up the test table
+DROP TABLE jsonb_test_order;
--
2.39.3 (Apple Git-145)
On Mon, Nov 18, 2024 at 10:25 PM Yan Chengpeng
<chengpeng_yan@outlook.com> wrote:
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`.
```
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.
per https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING
Object > Array > Boolean > Number > String > Null
JsonbValue->val.array.rawScalar is false (that is the real array)
should be larger than scalar (Boolean, Number, String, Null).
while sorting, rawScalar flag should have more priority than comparing
the number of elements in an array.
if two jsonb, JsonbValue->val.array.rawScalar values are different,
then we don't need to compare val.array.nElems.
so I think you are right.
but I am confused with your comments change.
src5=# select 'a' < 'A' collate "en_US.utf8";
?column?
----------
t
(1 row)
src5=# select 'a' < 'A' collate "C";
?column?
----------
f
(1 row)
docs says:
""Primitive JSON values are compared using the same comparison rules
as for the underlying PostgreSQL data type.
Strings are compared using the default database collation.
""
To make the regress tests stable, you may need to change the regress
test value ("a", "A")
the only corner case is the empty jsonb array [].
so sql test like:
select jsonb '[]' < jsonb 'null';
should enough?
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.
cheers
andrew
--
Andrew Dunstan
EDB:https://www.enterprisedb.com
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
Attachments:
jsonb-sort-doc.patchtext/x-patch; charset=UTF-8; name=jsonb-sort-doc.patchDownload
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> ...
On Sun, Dec 8, 2024 at 10:58 PM Andrew Dunstan <andrew@dunslane.net> wrote:
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.
while at it. we can fix the appearance of jsonb null.
since
select jsonb 'Null';
select jsonb 'NULL';
will fail.
so maybe change
<replaceable>Null</replaceable> in <synopsis> section and
<replaceable>NULL</replaceable>
to
<replaceable>null</replaceable>
If many people are already using this ‘wrong’ behavior. I agree to change the doc. I also think using ‘null’ may be a better choice. Thanks for your comments.
From: jian he <jian.universality@gmail.com>
Date: Monday, December 9, 2024 at 16:56
To: Andrew Dunstan <andrew@dunslane.net>
Cc: Yan Chengpeng <chengpeng_yan@outlook.com>, pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Subject: Re: [PATCH] Fix jsonb comparison for raw scalar pseudo arrays
On Sun, Dec 8, 2024 at 10:58 PM Andrew Dunstan <andrew@dunslane.net> wrote:
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.
while at it. we can fix the appearance of jsonb null.
since
select jsonb 'Null';
select jsonb 'NULL';
will fail.
so maybe change
<replaceable>Null</replaceable> in <synopsis> section and
<replaceable>NULL</replaceable>
to
<replaceable>null</replaceable>
Attachments:
v1-0001-fix-jsonb-compare.patchapplication/octet-stream; name=v1-0001-fix-jsonb-compare.patchDownload
From 54ab7c97cf6ccea090b5b69dfc5c00467fcf1b10 Mon Sep 17 00:00:00 2001
From: yanchengpeng <chengpeng.yan@protonbase.io>
Date: Mon, 18 Nov 2024 13:48:32 +0000
Subject: [PATCH] Fix jsonb comparison for raw scalar pseudo arrays
---
src/backend/utils/adt/jsonb_util.c | 10 ++--
src/test/regress/expected/jsonb.out | 88 +++++++++++++++++++++++++++++
src/test/regress/sql/jsonb.sql | 56 ++++++++++++++++++
3 files changed, 150 insertions(+), 4 deletions(-)
diff --git a/src/backend/utils/adt/jsonb_util.c b/src/backend/utils/adt/jsonb_util.c
index 9941daad2b..8b29b312d2 100644
--- a/src/backend/utils/adt/jsonb_util.c
+++ b/src/backend/utils/adt/jsonb_util.c
@@ -239,14 +239,16 @@ compareJsonbContainers(JsonbContainer *a, JsonbContainer *b)
case jbvArray:
/*
- * This could be a "raw scalar" pseudo array. That's
+ * This could be a "raw scalar" pseudo array. That's
* a special case here though, since we still want the
- * general type-based comparisons to apply, and as far
- * as we're concerned a pseudo array is just a scalar.
+ * general type-based comparisons to apply. As far
+ * as we're concerned, a pseudo array is just a scalar.
+ * If both are either raw scalars or both are arrays,
+ * compare the number of elements.
*/
if (va.val.array.rawScalar != vb.val.array.rawScalar)
res = (va.val.array.rawScalar) ? -1 : 1;
- if (va.val.array.nElems != vb.val.array.nElems)
+ else if (va.val.array.nElems != vb.val.array.nElems)
res = (va.val.array.nElems > vb.val.array.nElems) ? 1 : -1;
break;
case jbvObject:
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 7d163a156e..1043b8eee1 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -5715,3 +5715,91 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
12345
(1 row)
+-- Create a table for JSONB ordering tests
+CREATE TABLE jsonb_test_order (j jsonb PRIMARY KEY);
+-- Insert JSONB values for scalar types
+INSERT INTO jsonb_test_order VALUES
+ ('null'),
+ ('true'),
+ ('false'),
+ ('0'),
+ ('3.14159'),
+ ('-3.14159'),
+ ('1E-10'),
+ ('1E+10'),
+ ('"A"'),
+ ('"a"'),
+ ('""');
+-- Insert JSONB values for arrays
+INSERT INTO jsonb_test_order VALUES
+ ('[]'),
+ ('[null]'),
+ ('[true]'),
+ ('[false]'),
+ ('[0]'),
+ ('[1, 2, 3]'),
+ ('[3, 2, 1]'),
+ ('["a", "b", "c"]'),
+ ('["c", "b", "a"]'),
+ ('[[1, 2], [3, 4]]'),
+ ('[[]]');
+-- Insert JSONB values for objects
+INSERT INTO jsonb_test_order VALUES
+ ('{}'),
+ ('{"a": null}'),
+ ('{"a": true}'),
+ ('{"a": false}'),
+ ('{"a": 1}'),
+ ('{"b": 1}'),
+ ('{"a": "a"}'),
+ ('{"a": {"nested": true}}'),
+ ('{"a": [1, 2, 3]}');
+-- Insert mixed JSONB values
+INSERT INTO jsonb_test_order VALUES
+ ('[{}, [], null, ""]'),
+ ('[{"a": 1}, [2, 3], false]'),
+ ('{"nested": {"key": "value"}}'),
+ ('[{"array": [1, 2]}, {"array": [3, 4]}]');
+-- Select all rows, ordered by the JSONB column
+SELECT * FROM jsonb_test_order ORDER BY j;
+ j
+----------------------------------------
+ null
+ ""
+ "A"
+ "a"
+ -3.14159
+ 0
+ 0.0000000001
+ 3.14159
+ 10000000000
+ false
+ true
+ []
+ [null]
+ [0]
+ [false]
+ [true]
+ [[]]
+ [[1, 2], [3, 4]]
+ [{"array": [1, 2]}, {"array": [3, 4]}]
+ ["a", "b", "c"]
+ ["c", "b", "a"]
+ [1, 2, 3]
+ [3, 2, 1]
+ [{"a": 1}, [2, 3], false]
+ [{}, [], null, ""]
+ {}
+ {"a": null}
+ {"a": "a"}
+ {"a": 1}
+ {"a": false}
+ {"a": true}
+ {"a": [1, 2, 3]}
+ {"a": {"nested": true}}
+ {"b": 1}
+ {"nested": {"key": "value"}}
+(35 rows)
+
+-- Clean up the test table
+DROP TABLE jsonb_test_order;
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index 5f0190d5a2..e2ff06c4a9 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -1559,3 +1559,59 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::float8;
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int2;
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int4;
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
+
+-- Create a table for JSONB ordering tests
+CREATE TABLE jsonb_test_order (j jsonb PRIMARY KEY);
+
+-- Insert JSONB values for scalar types
+INSERT INTO jsonb_test_order VALUES
+ ('null'),
+ ('true'),
+ ('false'),
+ ('0'),
+ ('3.14159'),
+ ('-3.14159'),
+ ('1E-10'),
+ ('1E+10'),
+ ('"A"'),
+ ('"a"'),
+ ('""');
+
+-- Insert JSONB values for arrays
+INSERT INTO jsonb_test_order VALUES
+ ('[]'),
+ ('[null]'),
+ ('[true]'),
+ ('[false]'),
+ ('[0]'),
+ ('[1, 2, 3]'),
+ ('[3, 2, 1]'),
+ ('["a", "b", "c"]'),
+ ('["c", "b", "a"]'),
+ ('[[1, 2], [3, 4]]'),
+ ('[[]]');
+
+-- Insert JSONB values for objects
+INSERT INTO jsonb_test_order VALUES
+ ('{}'),
+ ('{"a": null}'),
+ ('{"a": true}'),
+ ('{"a": false}'),
+ ('{"a": 1}'),
+ ('{"b": 1}'),
+ ('{"a": "a"}'),
+ ('{"a": {"nested": true}}'),
+ ('{"a": [1, 2, 3]}');
+
+-- Insert mixed JSONB values
+INSERT INTO jsonb_test_order VALUES
+ ('[{}, [], null, ""]'),
+ ('[{"a": 1}, [2, 3], false]'),
+ ('{"nested": {"key": "value"}}'),
+ ('[{"array": [1, 2]}, {"array": [3, 4]}]');
+
+-- Select all rows, ordered by the JSONB column
+SELECT * FROM jsonb_test_order ORDER BY j;
+
+-- Clean up the test table
+DROP TABLE jsonb_test_order;
--
2.39.3 (Apple Git-145)
Sorry, I uploaded the wrong file. I uploaded a new patch with the modified document. Please take a review. Thanks!
From: Yan Chengpeng <chengpeng_yan@outlook.com>
Date: Monday, December 9, 2024 at 21:22
To: jian he <jian.universality@gmail.com>, Andrew Dunstan <andrew@dunslane.net>
Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Subject: Re: [PATCH] Fix jsonb comparison for raw scalar pseudo arrays
If many people are already using this ‘wrong’ behavior. I agree to change the doc. I also think using ‘null’ may be a better choice. Thanks for your comments.
From: jian he <jian.universality@gmail.com>
Date: Monday, December 9, 2024 at 16:56
To: Andrew Dunstan <andrew@dunslane.net>
Cc: Yan Chengpeng <chengpeng_yan@outlook.com>, pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Subject: Re: [PATCH] Fix jsonb comparison for raw scalar pseudo arrays
On Sun, Dec 8, 2024 at 10:58 PM Andrew Dunstan <andrew@dunslane.net> wrote:
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.
while at it. we can fix the appearance of jsonb null.
since
select jsonb 'Null';
select jsonb 'NULL';
will fail.
so maybe change
<replaceable>Null</replaceable> in <synopsis> section and
<replaceable>NULL</replaceable>
to
<replaceable>null</replaceable>
Attachments:
json-doc-update.patchapplication/octet-stream; name=json-doc-update.patchDownload
commit 6f4c911aa98b7ced0b8e93d36e222531cd4f1c87
Author: yanchengpeng <chengpeng_yan@outlook.com>
Date: Mon Dec 9 12:59:28 2024 +0000
docs: Clarify JSONB sorting for empty arrays
Added a note to the JSONB documentation explaining that empty arrays sort less than null, due to historical reasons. This provides better clarity on JSONB sorting rules.
diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml
index 54648c459c..2870df34d8 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> ...
On Mon, Dec 9, 2024 at 9:27 PM Yan Chengpeng <chengpeng_yan@outlook.com> wrote:
Sorry, I uploaded the wrong file. I uploaded a new patch with the modified document. Please take a review. Thanks!
sorry. maybe i didn't mention it explicitly.
i mean something like:
diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml
index 54648c459c..d9b24e413e 100644
--- a/doc/src/sgml/json.sgml
+++ b/doc/src/sgml/json.sgml
@@ -584,12 +584,13 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE
jdoc @@ '$.tags[*] == "qui"';
The <literal>btree</literal> ordering for <type>jsonb</type>
datums is seldom
of great interest, but for completeness it is:
<synopsis>
-<replaceable>Object</replaceable> > <replaceable>Array</replaceable><replaceable>Boolean</replaceable> >
<replaceable>Number</replaceable> > <replaceable>String</replaceable>
<replaceable>Null</replaceable>
+<replaceable>Object</replaceable> > <replaceable>Array</replaceable>
<replaceable>Boolean</replaceable> >
<replaceable>Number</replaceable> > <replaceable>String</replaceable>
<replaceable>null</replaceable>
<replaceable>Object with n pairs</replaceable> > <replaceable>object
with n - 1 pairs</replaceable>
<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> ...
Attachments:
v2_json_doc_update.difftext/x-patch; charset=US-ASCII; name=v2_json_doc_update.diffDownload
diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml
index 54648c459c..d9b24e413e 100644
--- a/doc/src/sgml/json.sgml
+++ b/doc/src/sgml/json.sgml
@@ -584,12 +584,13 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@ '$.tags[*] == "qui"';
The <literal>btree</literal> ordering for <type>jsonb</type> datums is seldom
of great interest, but for completeness it is:
<synopsis>
-<replaceable>Object</replaceable> > <replaceable>Array</replaceable> > <replaceable>Boolean</replaceable> > <replaceable>Number</replaceable> > <replaceable>String</replaceable> > <replaceable>Null</replaceable>
+<replaceable>Object</replaceable> > <replaceable>Array</replaceable> > <replaceable>Boolean</replaceable> > <replaceable>Number</replaceable> > <replaceable>String</replaceable> > <replaceable>null</replaceable>
<replaceable>Object with n pairs</replaceable> > <replaceable>object with n - 1 pairs</replaceable>
<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> ...
On 2024-12-09 Mo 11:16 AM, jian he wrote:
On Mon, Dec 9, 2024 at 9:27 PM Yan Chengpeng <chengpeng_yan@outlook.com> wrote:
Sorry, I uploaded the wrong file. I uploaded a new patch with the modified document. Please take a review. Thanks!
sorry. maybe i didn't mention it explicitly.
i mean something like:diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml index 54648c459c..d9b24e413e 100644 --- a/doc/src/sgml/json.sgml +++ b/doc/src/sgml/json.sgml @@ -584,12 +584,13 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@ '$.tags[*] == "qui"'; The <literal>btree</literal> ordering for <type>jsonb</type> datums is seldom of great interest, but for completeness it is: <synopsis> -<replaceable>Object</replaceable> > <replaceable>Array</replaceable><replaceable>Boolean</replaceable> >
<replaceable>Number</replaceable> > <replaceable>String</replaceable>
<replaceable>Null</replaceable>
+<replaceable>Object</replaceable> > <replaceable>Array</replaceable>
<replaceable>Boolean</replaceable> >
<replaceable>Number</replaceable> > <replaceable>String</replaceable>
<replaceable>null</replaceable>
<replaceable>Object with n pairs</replaceable> > <replaceable>object
with n - 1 pairs</replaceable><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> ...
Pushed something along these lines. In master I also added a code
comment so nobody might be tempted to "fix" the anomaly.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
Andrew Dunstan <andrew@dunslane.net> writes:
Pushed something along these lines. In master I also added a code
comment so nobody might be tempted to "fix" the anomaly.
There is still a commitfest entry [1]https://commitfest.postgresql.org/51/5394/ pointing at this thread.
Should it be closed as committed, or is there more to do?
regards, tom lane
On 2025-01-18 Sa 1:17 PM, Tom Lane wrote:
Andrew Dunstan <andrew@dunslane.net> writes:
Pushed something along these lines. In master I also added a code
comment so nobody might be tempted to "fix" the anomaly.There is still a commitfest entry [1] pointing at this thread.
Should it be closed as committed, or is there more to do?regards, tom lane
Nope, that was it. I have marked the item as committed, thanks for noticing.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com