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

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)

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)