Thank you very much. It worked. Regards Johann On Thu, 13 Dec 2018 at 11:03, Andrew Gierth <andrew@xxxxxxxxxxxxxxxxxxxx> wrote: > > >>>>> "Johann" == Johann Spies <johann.spies@xxxxxxxxx> 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)
