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

>>>>> "Johann" == Johann Spies <johann(dot)spies(at)gmail(dot)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)