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

Thank you very much.

It worked.

Regards
Johann
On Thu, 13 Dec 2018 at 11:03, Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> wrote:
>
> >>>>> "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)

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