Hi, I'm trying to find a way to do a range query on json such that it will use an index. This seems possible given that jsonb supports btrees and expression indices.
For example I have: create index t1 on document using btree ((payload->'intTest')); where: payload is a jsonb column and intTest is a json key whose value is always an int. Based on the documentation examples, I created an index like this: create index t1 on document using btree ((payload->'intTest')); Logically, what I want is to be able to make queries like this: select * from document where ((payload->'intTest'))> 5; With casting, I came up with: select * from document where (((payload->'intTest'))::text)::integer > 5; But this query does not use the index according to Explain "Seq Scan on public.document (cost=0.00..1868.33 rows=5764 width=619) (actual time=286.228..1706.638 rows=1974 loops=1)" " Output: owner, document_type, guid, schema_version, payload, last_update, payload_class, instance_version, acl_read, deleted, fts_text" " Filter: ((((document.payload -> 'intTest'::text))::text)::integer > 5)" " Rows Removed by Filter: 15319" " Buffers: shared hit=5420 read=29085" "Planning time: 0.108 ms" "Execution time: 1706.941 ms" Any help at all would be appreciated. Thanks.
