Calling jsonb_array_elements 4 times in the same query

Поиск
Список
Период
Сортировка
От Alexander Farber
Тема Calling jsonb_array_elements 4 times in the same query
Дата
Msg-id CAADeyWiD2r3NK5coRcfW-bsS2KYaEviCmyPYmFjAT_HzTEuS2Q@mail.gmail.com
обсуждение исходный текст
Ответы Re: Calling jsonb_array_elements 4 times in the same query  (Thomas Kellerer <spam_eater@gmx.net>)
Re: Calling jsonb_array_elements 4 times in the same query  (Adrian Klaver <adrian.klaver@aklaver.com>)
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: Andrew Dunstan
Дата:
Сообщение: Re: jsonb_set() strictness considered harmful to data
Следующее
От: Thomas Kellerer
Дата:
Сообщение: Re: Calling jsonb_array_elements 4 times in the same query