Larry White <ljw1001@gmail.com> writes:
> 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
Nope. You would have to create an index on the casted expression if you
want to use integer comparisons with the index. The raw -> expression is
of type jsonb, which doesn't sort the same as integer.
BTW, you could save a small amount of notation with the ->> operator, ie
(payload->>'intTest')::integer
regards, tom lane