Skip to content

sql/jsonpath: support AnyKey for index acceleration #156340

@DrewKimball

Description

@DrewKimball

We don't currently support the AnyKey path when index-accelerating json_path expressions. Appending AnyKey prevents index acceleration, which seems a bit unintuitive and is probably fairly easy to fix. Here's an example opt test:

exec-ddl
CREATE TABLE b
(
    k INT PRIMARY KEY,
    u INT,
    v INT,
    j JSONB,
    INDEX u(u),
    UNIQUE INDEX v(v),
    INVERTED INDEX j_inv_idx(j)
)
----

# Can accelerate this.
opt
SELECT k FROM b WHERE jsonb_path_exists(j, '$.a.b')
----
project
 ├── columns: k:1!null
 ├── immutable
 ├── key: (1)
 └── inverted-filter
      ├── columns: k:1!null
      ├── inverted expression: /7
      │    ├── tight: true, unique: false
      │    └── union spans
      │         ├── [???, "a"/Arr/Arr/)
      │         └── [???, "a"/Arr/)
      ├── key: (1)
      └── scan b@j_inv_idx,inverted
           ├── columns: k:1!null j_inverted_key:7!null
           └── inverted constraint: /7/1
                └── spans
                     ├── [???, "a"/Arr/Arr/)
                     └── [???, "a"/Arr/)

# Can't accelerate this.
opt
SELECT k FROM b WHERE jsonb_path_exists(j, '$.a.b.*')
----
project
 ├── columns: k:1!null
 ├── immutable
 ├── key: (1)
 └── select
      ├── columns: k:1!null j:4!null
      ├── immutable
      ├── key: (1)
      ├── fd: (1)-->(4)
      ├── scan b
      │    ├── columns: k:1!null j:4
      │    ├── key: (1)
      │    └── fd: (1)-->(4)
      └── filters
           └── jsonb_path_exists(j:4, '$."a"."b".*') [outer=(4), immutable, constraints=(/4: (/NULL - ])]

Jira issue: CRDB-55949

Metadata

Metadata

Assignees

Labels

A-sql-jsonJSON handling in SQL.C-enhancementSolution expected to add code/behavior + preserve backward-compat (pg compat issues are exception)O-qaT-sql-queriesSQL Queries Teambranch-release-25.4Used to mark GA and release blockers and technical advisories for 25.4v25.4.1v26.1.0-prerelease

Type

No type

Projects

Status

Done

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions