Re: How to build a btree index with integer values on jsonb data? - Mailing list pgsql-general

Johann Spies wrote:
> How can I transform the following definition to index pubyear as
> integer and not text?
> 
> CREATE INDEX pubyear_idx
>     ON some_table_where_data_field_is_of_type_jsonb USING btree
>     ((((((data -> 'REC'::text) -> 'static_data'::text) ->
> 'summary'::text) -> 'pub_info'::text) ->> '@pubyear'::text) COLLATE
> pg_catalog."default");
> 
> While I can cast the value in a SELECT statement to integer I have
> been able to do the same while creating the index.

Replace

  COLLATE pg_catalog."default"

with

  ::integer

> Why btree index?  I want to do queries like
> 
> select stuff from sometable where pubyear between 2015 and 2018;

Because b-tree indexes are perfect for >= and <=.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com