Search Postgresql Archives

Re: Calling jsonb_array_elements 4 times in the same query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Thank you Thomas -

On Mon, Oct 21, 2019 at 4:24 PM Thomas Kellerer <spam_eater@xxxxxxx> wrote:
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 = "" 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 = ""
      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


I am trying to create the following strored function based on your suggestion (and I have forgotten to mention, that I also need the board id aka bid from another table, words_games), but hit the next problem:

CREATE OR REPLACE FUNCTION words_get_move(
                in_mid     integer
        ) RETURNS TABLE (
                out_bid    integer,
                out_mid    bigint,
                out_hand   text,
                out_col    integer,
                out_row    integer,
                out_letter text,
                out_value  integer
        ) AS
$func$
        SELECT
            g.bid,
            m.mid,
            m.hand,
            (t->'col')::int     AS col,
            (t->'row')::int     AS row,
            (t->'letter')::text AS letter,
            (t->'value')::int   AS value
        FROM words_moves m
        CROSS JOIN JSONB_ARRAY_ELEMENTS(m.tiles) AS t(tile)
        LEFT JOIN words_games g USING(gid)
        WHERE m.action = "" AND
        m.gid = (SELECT gid FROM words_moves WHERE mid = in_mid)
        AND m.played <= (SELECT played FROM words_moves WHERE mid = in_mid)
        ORDER BY m.played DESC;
$func$ LANGUAGE sql;

words_ru=> \i src/slova/dict/words_get_move.sql
psql:src/slova/dict/words_get_move.sql:28: ERROR:  cannot cast type jsonb to integer
LINE 17:             (t->'col')::int     AS col,
                               ^

How to cast the col to integer here?

Thanks
Alex


 

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux