How to build a btree index with integer values on jsonb data?

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

How to build a btree index with integer values on jsonb data?

Johann Spies-2
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.

Why btree index?  I want to do queries like

select stuff from sometable where pubyear between 2015 and 2018;

Regards
Johann
--
Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)

Reply | Threaded
Open this post in threaded view
|

Re: How to build a btree index with integer values on jsonb data?

Laurenz Albe
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