postgresqlindexinggwt-ginjsonb

How to index jsonb integer values

I'm trying to use the "newish" JSONB type.

I have a documents table with a properties jsonb field, and in that is a field publication_year. I want to find all document records within a year range e.g. 2013-2015. [EDIT: Querying for a range of values is the main challenge here, even though I have used an exact match example below. The requested approach would also apply for, say dollar ranges (price > $20 and price < $40) or timestamp ranges).]

I have tried:

create index test1 on documents using gin ((cast(properties->'announced_on_year' as integer)));

ERROR:  cannot cast type jsonb to integer

as well as:

create index test1 on documents using gin (cast(properties->>'publication_year' as integer));

ERROR:  data type integer has no default operator class for access method "gin"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.`

I saw from this post http://www.postgresql.org/message-id/10736.1409063604@sss.pgh.pa.us that this should be possible, but I can't figure out the right syntax.

When I just do a straightforward index:

create index test1 on documents using gin ((properties->'publication_year'));

an index is created, but I cannot query it using integer values to get a range going, it says

select count(*) from documents where properties->>'publication_year' = 2015;
ERROR:  operator does not exist: text = integer
LINE 1: ...*) from documents where properties->>'publication_year' = 2015;
                              ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

Any tips and hints highly appreciated. I'm sure others will benefit too. TIA

Solution

  • Why don't you define an index for the whole jsonb field, as described in the doc? 

    create index test1 on documents using gin (properties);