postgres.git / summary / log / commit / refs
commit 0cf2c7e38fa1ed81224e028014fef9ee666abcc4
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Sat Oct 24 18:27:12 2026 +0000
First released in: 14.25, 15.20, 16.16, 17.12, 18.7, 19.0
Fix GIN index search for root strings in strict jsonpath queries.
A jsonb_ops index scan for 'strict $ == "a scalar"' missed a matching
root string. Root scalars are stored in single-element arrays, so
gin_extract_jsonb() indexes strings with JGINFLAG_KEY. In strict mode,
jsonb_ops__extract_nodes() instead searches for JGINFLAG_STR at the
root. Leave gin_extract_jsonb() alone (else we'd force people to
reindex) and make jsonb_ops__extract_nodes() search for JGINFLAG_KEY
instead.
Author: Rui Zhao <zhaorui126@gmail.com>
Reviewed-by: shihao zhong <zhong950419@gmail.com>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: https://postgr.es/m/CAHWVJhHH0k3PxWmS==V1p_PONOMUaSm6LjUV487fwnOqd0jTQw@mail.gmail.com
Backpatch-through: 14
src/backend/utils/adt/jsonb_gin.c | 5 +++--
src/test/regress/expected/jsonb.out | 27 +++++++++++++++++++++++++++
src/test/regress/sql/jsonb.sql | 8 ++++++++
3 files changed, 38 insertions(+), 2 deletions(-)
diff --git a/src/backend/utils/adt/jsonb_gin.c b/src/backend/utils/adt/jsonb_gin.c
index 1ae543669cc..3dafc3fbdc5 100644
--- a/src/backend/utils/adt/jsonb_gin.c
+++ b/src/backend/utils/adt/jsonb_gin.c
@@ -435,13 +435,14 @@ jsonb_ops__extract_nodes(JsonPathGinContext *cxt, JsonPathGinPath path,
* keys, we may extract key or non-key entry or even both. In the
* latter case we create OR-node. It is possible in lax mode
* where arrays are automatically unwrapped, or in strict mode for
- * jpiAny items.
+ * jpiAny items. Also, root scalars are stored as single-element
+ * arrays, so those will be key entries.
*/
if (cxt->lax)
key_entry = GIN_MAYBE;
else if (!last) /* root ($) */
- key_entry = GIN_FALSE;
+ key_entry = GIN_TRUE;
else if (last->type == jpiAnyArray || last->type == jpiIndexArray)
key_entry = GIN_TRUE;
else if (last->type == jpiAny)
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 8c0c9394e35..5ea976dddeb 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -3255,6 +3255,33 @@ SELECT count(*) FROM testjsonb WHERE j @? '$.bar';
0
(1 row)
+-- check indexed lookup of root scalars
+INSERT INTO testjsonb VALUES('"a scalar"');
+SELECT count(*) FROM testjsonb WHERE j @@ 'strict $ == "a scalar"';
+ count
+-------
+ 1
+(1 row)
+
+SELECT count(*) FROM testjsonb WHERE j @? 'strict $ ? (@ == "a scalar")';
+ count
+-------
+ 1
+(1 row)
+
+SELECT count(*) FROM testjsonb WHERE j @@ 'lax $ == "a scalar"';
+ count
+-------
+ 1
+(1 row)
+
+SELECT count(*) FROM testjsonb WHERE j @? 'lax $ ? (@ == "a scalar")';
+ count
+-------
+ 1
+(1 row)
+
+DELETE FROM testjsonb WHERE j = '"a scalar"';
-- array exists - array elements should behave as keys (for GIN index scans too)
CREATE INDEX jidx_array ON testjsonb USING gin((j->'array'));
SELECT count(*) from testjsonb WHERE j->'array' ? 'bar';
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index cc05a351663..ec0ed87732e 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -865,6 +865,14 @@ SELECT count(*) FROM testjsonb WHERE j @? '$';
SELECT count(*) FROM testjsonb WHERE j @? '$.public';
SELECT count(*) FROM testjsonb WHERE j @? '$.bar';
+-- check indexed lookup of root scalars
+INSERT INTO testjsonb VALUES('"a scalar"');
+SELECT count(*) FROM testjsonb WHERE j @@ 'strict $ == "a scalar"';
+SELECT count(*) FROM testjsonb WHERE j @? 'strict $ ? (@ == "a scalar")';
+SELECT count(*) FROM testjsonb WHERE j @@ 'lax $ == "a scalar"';
+SELECT count(*) FROM testjsonb WHERE j @? 'lax $ ? (@ == "a scalar")';
+DELETE FROM testjsonb WHERE j = '"a scalar"';
+
-- array exists - array elements should behave as keys (for GIN index scans too)
CREATE INDEX jidx_array ON testjsonb USING gin((j->'array'));
SELECT count(*) from testjsonb WHERE j->'array' ? 'bar';
[parent: 774c47de1b3b]