Postgres Pro
Downloads
Home   >   mailing lists

Thread: indexed range queries on jsonb?

indexed range queries on jsonb?

From
Larry White
Date:
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.

Re: indexed range queries on jsonb?

From
Christian Ramseyer
Date:
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

Re: indexed range queries on jsonb?

From
Tom Lane
Date:
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

Re: indexed range queries on jsonb?

From
Larry White
Date:
Thank you Tom,

I made the necessary changes and Explain now shows that the query will use the index. 

Thanks again for your help.




On Tue, Aug 26, 2014 at 10:33 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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