Re: Calling jsonb_array_elements 4 times in the same query

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