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))
However, index support for JSONPath is very limited at this point
What does that mean exactly?