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

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

On Thu, 6 Dec 2018 at 19:27, Laurenz Albe <laurenz.albe@cybertec.at> wrote:

Replace

COLLATE pg_catalog."default"

with

::integer

which results in

syntax error at or near "::"
LINE 2: ...'::text) -> 'pub_info'::text) ->> '@pubyear'::text)::integer

moving the ::integer into the bracket also:

syntax error at end of input
LINE 2: ...'::text) -> 'pub_info'::text) ->> '@pubyear'::text::integer)
^
I have tried this before.

Thanks for your try.

Regards
Johann

"Johann" == Johann Spies <johann.spies@gmail.com> writes:

Johann> How can I transform the following definition to index pubyear
Johann> as integer and not text?

Johann> CREATE INDEX pubyear_idx
Johann> ON some_table_where_data_field_is_of_type_jsonb USING btree
Johann> ((((((data -> 'REC'::text) -> 'static_data'::text) ->
Johann> 'summary'::text) -> 'pub_info'::text) ->> '@pubyear'::text) COLLATE
Johann> pg_catalog."default");

Johann> While I can cast the value in a SELECT statement to integer I
Johann> have been able to do the same while creating the index.

Laurenz' answer was almost correct, just got the position of the parens
wrong.

When you use an expression in an index, the outermost level of the
expression must either be (syntactically) a function call, or it must
have parens around its _outermost_ level.

You can simplify selecting from nested json using #>> in place of the ->
and ->> operators. (x #>> array['foo','bar']) is equivalent to doing
((x -> 'foo') ->> 'bar')

So:

CREATE INDEX pubyear_idx
ON some_table_where_data_field_is_of_type_jsonb USING btree
(
((data #>> array['REC','static_data','summary','pub_info','@pubyear'])::integer)
);

Note the ::integer is inside the parens that define the column value
within the outermost ( ) which enclose the column _list_.

--
Andrew (irc:RhodiumToad)

Thank you very much.

It worked.

Regards
Johann
On Thu, 13 Dec 2018 at 11:03, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:

"Johann" == Johann Spies <johann.spies@gmail.com> writes:

Johann> How can I transform the following definition to index pubyear
Johann> as integer and not text?

Johann> CREATE INDEX pubyear_idx
Johann> ON some_table_where_data_field_is_of_type_jsonb USING btree
Johann> ((((((data -> 'REC'::text) -> 'static_data'::text) ->
Johann> 'summary'::text) -> 'pub_info'::text) ->> '@pubyear'::text) COLLATE
Johann> pg_catalog."default");

Johann> While I can cast the value in a SELECT statement to integer I
Johann> have been able to do the same while creating the index.

Laurenz' answer was almost correct, just got the position of the parens
wrong.

When you use an expression in an index, the outermost level of the
expression must either be (syntactically) a function call, or it must
have parens around its _outermost_ level.

You can simplify selecting from nested json using #>> in place of the ->
and ->> operators. (x #>> array['foo','bar']) is equivalent to doing
((x -> 'foo') ->> 'bar')

So:

CREATE INDEX pubyear_idx
ON some_table_where_data_field_is_of_type_jsonb USING btree
(
((data #>> array['REC','static_data','summary','pub_info','@pubyear'])::integer)
);

Note the ::integer is inside the parens that define the column value
within the outermost ( ) which enclose the column _list_.

--
Andrew (irc:RhodiumToad)

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