Re: Calling jsonb_array_elements 4 times in the same query

[Thomas Kellerer <spam_eater@xxxxxxx>]

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