Re: Calling jsonb_array_elements 4 times in the same query

On 10/21/19 6:39 AM, Alexander Farber wrote:
> Hello, good afternoon!
> 
> With PostgreSQL 10 I host a word game, which stores player moves as a 
> JSON array of objects with properties: col, row, value, letter -
> 
> CREATE TABLE words_moves (
>          mid     BIGSERIAL PRIMARY KEY,
>          action  text NOT NULL,
>          gid     integer NOT NULL REFERENCES words_games ON DELETE CASCADE,
>          uid     integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
>          played  timestamptz NOT NULL,
>          tiles   jsonb,
>          letters text,
>          hand    text,
>          score   integer CHECK(score >= 0),
>          puzzle  boolean NOT NULL DEFAULT false
> );
> 
> 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.
> 
> In my Java program I then just draw the tiles at the board, one by one 
> (here a picture: https://slova.de/game-62662/ )
> 
> I have however 3 questions please:
> 
> 1. Is it okay to call JSONB_ARRAY_ELEMENTS four times in the query, will 
> PostgreSQL optimize that to a single call?

What is the structure of the JSON in tiles?

In other words could you expand the data in one go using jsonb_to_record()?

> 2. Do you think if it is okay to sort by played timestamp or should I 
> better sort by mid?
> 3. Performancewise is it okay to use the 2 subqueries for finding gid 
> and played when given a mid?

I could see collapsing them into a single query: Something like:

FROM
   words_moves
JOIN
    (select gid, played from word_moves where mid = 39146) AS m_id
ON
   word_moves.gid = m_id.gid
WHERE
    ...

> 
> Thank you
> Alex
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com