Re: Calling jsonb_array_elements 4 times in the same query

Thank you Thomas -

On Mon, Oct 21, 2019 at 4:24 PM Thomas Kellerer <spam_eater(at)gmx(dot)net> wrote:

> Alexander Farber schrieb am 21.10.2019 um 15:39:
> > I am trying to construct a query, which would draw a game board when
> given a move id (aka mid):
> >
> > SELECT
> > hand,
> > JSONB_ARRAY_ELEMENTS(tiles)->'col' AS col,
> > JSONB_ARRAY_ELEMENTS(tiles)->'row' AS row,
> > JSONB_ARRAY_ELEMENTS(tiles)->'letter' AS letter,
> > JSONB_ARRAY_ELEMENTS(tiles)->'value' AS value
> > FROM words_moves
> > WHERE action = 'play' AND
> > gid = (SELECT gid FROM words_moves WHERE mid = 391416)
> > AND played <= (SELECT played FROM words_moves WHERE WHERE mid =
> 391416)
> > ORDER BY played DESC
> >
> > The above query works for me and fetches all moves performed in a game
> id (aka gid) up to the move id 391416.
> >
> > 1. Is it okay to call JSONB_ARRAY_ELEMENTS four times in the query, will
> PostgreSQL optimize that to a single call?
>
> Typically set returning functions should be used in the FROM clause, not
> the SELECT list:
>
> SELECT
> hand,
> t.tile -> 'col' AS col,
> t.tile -> 'row' AS row,
> t.tile -> 'letter' AS letter,
> t.tile -> 'value' AS value
> FROM words_moves
> cross join jsonb_array_elements(tiles) as t(tile)
> WHERE action = 'play'
> AND gid = (SELECT gid FROM words_moves WHERE mid = 391416)
> AND played <= (SELECT played FROM words_moves WHERE WHERE mid =
> 391416)
> ORDER BY played DESC
>
>
I am trying to create the following strored function based on your
suggestion (and I have forgotten to mention, that I also 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,
^

How to cast the col to integer here?

Thanks
Alex