Re: indexed range queries on jsonb?

On 8/26/14 3:30 PM, Larry White wrote:


>
> 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
>

I have not tested this with a jsonb property but you should be able to
define an index over payload->'intTest'::integer using an expression
index, described here:

http://www.postgresql.org/docs/9.1/static/indexes-expressional.html

Christian