jsonb and comparison operators

From: Joe Van Dyk <joe(at)tanga(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: jsonb and comparison operators
Date: 2014-09-03 04:38:49
Message-ID: CACfv+pKoL+-sn3g_4FPyJXqb1+hbv97B8+8umXYh1qpHf9eDTA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Peter Geoghegan 2014-09-03 04:55:46 Re: jsonb and comparison operators
Previous Message Patrick Dung 2014-09-03 03:03:15 Re: Question about gin index not used on a tsv column