From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Calling jsonb_array_elements 4 times in the same query |
Date: | 2019-10-21 14:40:25 |
Message-ID: | bb363e8b-b2cd-275f-a86e-67a23c6b0032@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Kellerer | 2019-10-21 14:45:27 | Re: Calling jsonb_array_elements 4 times in the same query |
Previous Message | Alan Hodgson | 2019-10-21 14:39:50 | Re: Postgres Point in time Recovery (PITR), |