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
