Re: Calling jsonb_array_elements 4 times in the same query

> I am trying to create the following strored function based on your suggestion (and I have forgotten to mention, that
Ialso need the board id aka bid from another table, words_games), but hit the next problem:
 
> 
> CREATE OR REPLACE FUNCTION words_get_move(
>                 in_mid     integer
>         ) RETURNS TABLE (
>                 out_bid    integer,
>                 out_mid    bigint,
>                 out_hand   text,
>                 out_col    integer,
>                 out_row    integer,
>                 out_letter text,
>                 out_value  integer
>         ) AS
> $func$
>         SELECT
>             g.bid,
>             m.mid,
>             m.hand,
>             (t->'col')::int     AS col,
>             (t->'row')::int     AS row,
>             (t->'letter')::text AS letter,
>             (t->'value')::int   AS value
>         FROM words_moves m
>         CROSS JOIN JSONB_ARRAY_ELEMENTS(m.tiles) AS t(tile)
>         LEFT JOIN words_games g USING(gid)
>         WHERE m.action = 'play' AND
>         m.gid = (SELECT gid FROM words_moves WHERE mid = in_mid)
>         AND m.played <= (SELECT played FROM words_moves WHERE mid = in_mid)
>         ORDER BY m.played DESC;
> $func$ LANGUAGE sql;
> 
> words_ru=> \i src/slova/dict/words_get_move.sql
> psql:src/slova/dict/words_get_move.sql:28: ERROR:  cannot cast type jsonb to integer
> LINE 17:             (t->'col')::int     AS col,
>                                ^
> 

Use ->> to return the value as text (not as JSONB) and you need to use the column alias, not the table alias:

    (t.tile ->> 'col')::int