Home > mailing lists

jsonb and comparison operators - Mailing list pgsql-general

From	Joe Van Dyk
Subject	jsonb and comparison operators
Date	September 3, 2014 04:39:35
Msg-id	CACfv+pKoL+-sn3g_4FPyJXqb1+hbv97B8+8umXYh1qpHf9eDTA@mail.gmail.com Whole thread
Responses	Re: jsonb and comparison operators Re: jsonb and comparison operators
List	pgsql-general

Is it possible to get this query (or a similar one) to use an index?

I want to return all rows that have a value of less than 10. I have arbitrary keys I want to check (not just 'a').

drop table if exists test;

create table test (j jsonb);

insert into test select json_build_object('a', i)::jsonb from generate_series(1, 100000) i;

create index on test using gin(j);

vacuum analyze test;

select * from test where (j->>'a')::int < 10;

I tried

select * from test where j->'a' < 10::json::jsonb;

but didn't seem to use the index.

From: Patrick Dung
Date: 03 September 2014, 03:03:25
Subject: Re: Question about gin index not used on a tsv column

From: Peter Geoghegan
Date: 03 September 2014, 04:55:53
Subject: Re: jsonb and comparison operators