Given the PostgreSQL 13.3 table and data
CREATE TABLE transaction (message jsonb NOT NULL);
INSERT INTO transaction(message) VALUES ('{"amount": 1.00}'), ('{"amount": 2.00}');
the index
CREATE INDEX ON transaction (((message->'amount')::numeric));
is used in the query
SELECT t.message FROM transaction t
WHERE (t.message->'amount')::numeric > 1.00;
Neither of the indexes
CREATE INDEX ON transaction USING GIN (message);
CREATE INDEX ON transaction USING GIN (message jsonb_path_ops);
is used in the query
SELECT t.message FROM transaction t
WHERE t.message @@ format('$.amount > %s', 1.00)::jsonpath;
Is there a way in PostgreSQL to index the transaction.message column so the JSONPath @@ operator uses the index instead of doing a Seq Scan?