From: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Calling jsonb_array_elements 4 times in the same query |
Date: | 2019-10-21 13:39:45 |
Message-ID: | CAADeyWiD2r3NK5coRcfW-bsS2KYaEviCmyPYmFjAT_HzTEuS2Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
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?
Thank you
Alex
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Kellerer | 2019-10-21 14:24:43 | Re: Calling jsonb_array_elements 4 times in the same query |
Previous Message | Andrew Dunstan | 2019-10-21 13:28:06 | Re: jsonb_set() strictness considered harmful to data |