1

I am trying to use jsonb_path_match() as per https://www.postgresql.org/docs/current/functions-json.html as suggested in How can I prevent SQL injection with arbitrary JSONB query string provided by an external client? so that I can query my JSON data using parameterized queries with constraints provided by an external, potentially unsafe, client. After some investigation I found that I can use a btree index like:

CREATE INDEX IF NOT EXISTS idxName ON "tableName" ((jsonbCol->>'field'))

Which is used in queries like:

SELECT * FROM "tableName" WHERE jsonbCol->>'field' < 'someVal'

However using jsonb_path_match() the index is not used:

SELECT * FROM "tableName" WHERE jsonb_path_match(jsonbCol, '$.field < "someVal"')

What kind of index can I make to support the query using jsonb_path_match()?

If there is a solution for the specific example, can it be applied to other types of queries like:

$.f1[*].f2 == "someString"
exists($.f1)
exists($.f1[*] ? (@.f2 == "someString" && @.f3 < someNumber))

https://dev.to/scalegrid/using-jsonb-in-postgresql-how-to-effectively-store-index-json-data-in-postgresql-5d7e says that

However, index support for JSONPath is very limited at this point

What does that mean exactly?

Shahin
  • 53
  • 6
  • I think you are out of luck with the `<` operator. `jsonbcol @@ '$.fieldName == someNumber'` can make use of a GIN index, but I don't think it will support range queries –  Aug 28 '20 at 22:15

0 Answers0