How to index jsonb integer values

Question

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/[email protected] 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

Answer 1

I've found in my experiences that using GIN indexes on JSONB columns was not faster. You can just create a normal index by casting it to an integer

CREATE INDEX test1 ON documents ((properties->>'publication_year')::int);

Also, GIN has some limitations that should be considered before creating one. Even indexing the entire JSONB column can result in massive table-sized indexes.

This is based on my experience and looking through the Postgres documentation.

Answer 2

1) There are no GIN indexes for integer (at least not out of the box), use a btree.

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

2) The error is pretty self-explanatory, cast the integer as text or use text for the comparison:

select count(*) from documents where properties->>'publication_year' = '2015';