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]