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

From: Johann Spies <johann(dot)spies(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: How to build a btree index with integer values on jsonb data?
Date: 2018-12-06 13:43:17
Message-ID: CAGZ55DSm4kGiBSJaPtTk6L7hp6xDFKaKThDLmXd_4cCFFFJe5Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rene Romero Benavides 2018-12-06 14:46:21 Re: order of reading the conf files
Previous Message Pavel Stehule 2018-12-06 11:21:33 Re: debugging intermittent slow updates under higher load