Thread: Calling jsonb_array_elements 4 times in the same query

Calling jsonb_array_elements 4 times in the same query

From
Alexander Farber
Date:
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

Re: Calling jsonb_array_elements 4 times in the same query

From
Thomas Kellerer
Date:
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 = '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.
> 
> 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 = '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







Re: Calling jsonb_array_elements 4 times in the same query

From
Alexander Farber
Date:
Thank you Thomas -

On Mon, Oct 21, 2019 at 4:24 PM Thomas Kellerer <spam_eater@gmx.net> 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 = '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.
>
> 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 = '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


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 = 'play' 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


 

Re: Calling jsonb_array_elements 4 times in the same query

From
Adrian Klaver
Date:
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



Re: Calling jsonb_array_elements 4 times in the same query

From
Thomas Kellerer
Date:
> I am trying to create the following strored function based on your suggestion (and I have forgotten to mention, that
Ialso 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 = 'play' 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,
>                                ^
> 

Use ->> to return the value as text (not as JSONB) and you need to use the column alias, not the table alias:

    (t.tile ->> 'col')::int




Re: Calling jsonb_array_elements 4 times in the same query

From
Alexander Farber
Date:
Apologies, I should have shown the JSON structure in my very first email -

On Mon, Oct 21, 2019 at 4:45 PM Thomas Kellerer <spam_eater@gmx.net> wrote:
Use ->> to return the value as text (not as JSONB) and you need to use the column alias, not the table alias:

    (t.tile ->> 'col')::int


It is a JSON-array of JSON-objects with properties col, row, value (integers) and letter (text):

words_ru=> SELECT * FROM words_moves LIMIT 5;

  mid   | action |  gid  | uid  |            played             |                                                                                                                                                                           tiles                                                                                                                                                                            | score | letters |  hand   | puzzle
--------+--------+-------+------+-------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------+---------+--------
 385903 | play   | 17042 | 5476 | 2018-06-20 04:46:13.864758+02 | [{"col": 7, "row": 6, "value": 1, "letter": "А"}, {"col": 7, "row": 5, "value": 2, "letter": "Р"}, {"col": 7, "row": 11, "value": 2, "letter": "В"}, {"col": 7, "row": 10, "value": 1, "letter": "А"}, {"col": 7, "row": 9, "value": 2, "letter": "Л"}, {"col": 7, "row": 8, "value": 2, "letter": "П"}, {"col": 7, "row": 7, "value": 2, "letter": "С"}]  |    29 | АРВАЛПС | ВРЛПААС | f
 391416 | play   | 17055 | 5476 | 2018-06-21 00:36:36.690012+02 | [{"col": 4, "row": 11, "value": 1, "letter": "А"}, {"col": 4, "row": 10, "value": 2, "letter": "К"}, {"col": 4, "row": 9, "value": 0, "letter": "Л"}, {"col": 4, "row": 8, "value": 1, "letter": "Е"}, {"col": 4, "row": 7, "value": 2, "letter": "Д"}, {"col": 4, "row": 5, "value": 2, "letter": "Р"}, {"col": 4, "row": 4, "value": 2, "letter": "П"}]  |    34 | АКЛЕДРП | РКП*АДЕ | f
 394056 | play   | 17264 | 7873 | 2018-06-21 13:39:27.026943+02 | [{"col": 9, "row": 7, "value": 0, "letter": "Р"}, {"col": 8, "row": 7, "value": 0, "letter": "Е"}, {"col": 7, "row": 7, "value": 1, "letter": "Н"}, {"col": 6, "row": 7, "value": 1, "letter": "О"}, {"col": 5, "row": 7, "value": 2, "letter": "Р"}, {"col": 4, "row": 7, "value": 1, "letter": "О"}, {"col": 3, "row": 7, "value": 2, "letter": "К"}]    |    24 | РЕНОРОК | ОК**ОНР | f
    131 | play   |   206 |  404 | 2018-02-20 09:26:05.234006+01 | [{"col": 9, "row": 7, "value": 5, "letter": "Ь"}, {"col": 8, "row": 7, "value": 2, "letter": "Д"}, {"col": 7, "row": 7, "value": 1, "letter": "Е"}, {"col": 6, "row": 7, "value": 2, "letter": "С"}, {"col": 5, "row": 7, "value": 1, "letter": "О"}, {"col": 4, "row": 7, "value": 2, "letter": "Р"}, {"col": 3, "row": 7, "value": 2, "letter": "П"}]    |    32 | ЬДЕСОРП |         | f
  15676 | play   |  2785 | 2997 | 2018-04-18 16:56:58.368445+02 | [{"col": 12, "row": 7, "value": 5, "letter": "Ь"}, {"col": 11, "row": 7, "value": 1, "letter": "Н"}, {"col": 10, "row": 7, "value": 1, "letter": "Е"}, {"col": 8, "row": 7, "value": 0, "letter": "Г"}, {"col": 9, "row": 7, "value": 2, "letter": "Р"}, {"col": 7, "row": 7, "value": 1, "letter": "И"}, {"col": 6, "row": 7, "value": 2, "letter": "М"}] |    28 | МИЬРНГЕ |         | f
(5 rows)
 
This stored function -

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.tile->'col')::int     AS col,
            (t.tile->'row')::int     AS row,
            (t.tile->'letter')::text AS letter,
            (t.tile->'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 = 'play' 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;

gives me same error (why does it think it is JSONB and not integer?)

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.tile->'col')::int     AS col,
                                    ^

And I would prefer not to use ->> because I want col, row, value as integers and not text

Regards
Alex

P.S. Below is the table definition:

words_ru=> \d words_moves
                                      Table "public.words_moves"
 Column  |           Type           | Collation | Nullable |                 Default
---------+--------------------------+-----------+----------+------------------------------------------
 mid     | bigint                   |           | not null | nextval('words_moves_mid_seq'::regclass)
 action  | text                     |           | not null |
 gid     | integer                  |           | not null |
 uid     | integer                  |           | not null |
 played  | timestamp with time zone |           | not null |
 tiles   | jsonb                    |           |          |
 score   | integer                  |           |          |
 letters | text                     |           |          |
 hand    | text                     |           |          |
 puzzle  | boolean                  |           | not null | false
Indexes:
    "words_moves_pkey" PRIMARY KEY, btree (mid)
    "words_moves_gid_played_idx" btree (gid, played DESC)
    "words_moves_uid_action_played_idx" btree (uid, action, played)
    "words_moves_uid_idx" btree (uid)
Check constraints:
    "words_moves_score_check" CHECK (score >= 0)
Foreign-key constraints:
    "words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE
    "words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE
Referenced by:
    TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY (mid) REFERENCES words_moves(mid) ON DELETE CASCADE



Re: Calling jsonb_array_elements 4 times in the same query

From
Adrian Klaver
Date:
On 10/21/19 1:30 PM, Alexander Farber wrote:
> Apologies, I should have shown the JSON structure in my very first email -
> 
> On Mon, Oct 21, 2019 at 4:45 PM Thomas Kellerer <spam_eater@gmx.net 
> <mailto:spam_eater@gmx.net>> wrote:
> 
>     Use ->> to return the value as text (not as JSONB) and you need to
>     use the column alias, not the table alias:
> 
>          (t.tile ->> 'col')::int
> 
> 
> It is a JSON-array of JSON-objects with properties col, row, value 
> (integers) and letter (text):
> 
> words_ru=> SELECT * FROM words_moves LIMIT 5;
> 
>    mid   | action |  gid  | uid  |            played             
> |
                                                     
 
> tiles
                                                          
 
> | score | letters |  hand   | puzzle
>
--------+--------+-------+------+-------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------+---------+--------
>   385903 | play   | 17042 | 5476 | 2018-06-20 04:46:13.864758+02 | 
> [{"col": 7, "row": 6, "value": 1, "letter": "А"}, {"col": 7, "row": 5, 
> "value": 2, "letter": "Р"}, {"col": 7, "row": 11, "value": 2, "letter": 
> "В"}, {"col": 7, "row": 10, "value": 1, "letter": "А"}, {"col": 7, 
> "row": 9, "value": 2, "letter": "Л"}, {"col": 7, "row": 8, "value": 2, 
> "letter": "П"}, {"col": 7, "row": 7, "value": 2, "letter": "С"}]  |    
> 29 | АРВАЛПС | ВРЛПААС | f
>   391416 | play   | 17055 | 5476 | 2018-06-21 00:36:36.690012+02 | 
> [{"col": 4, "row": 11, "value": 1, "letter": "А"}, {"col": 4, "row": 10, 
> "value": 2, "letter": "К"}, {"col": 4, "row": 9, "value": 0, "letter": 
> "Л"}, {"col": 4, "row": 8, "value": 1, "letter": "Е"}, {"col": 4, "row": 
> 7, "value": 2, "letter": "Д"}, {"col": 4, "row": 5, "value": 2, 
> "letter": "Р"}, {"col": 4, "row": 4, "value": 2, "letter": "П"}]  |    
> 34 | АКЛЕДРП | РКП*АДЕ | f
>   394056 | play   | 17264 | 7873 | 2018-06-21 13:39:27.026943+02 | 
> [{"col": 9, "row": 7, "value": 0, "letter": "Р"}, {"col": 8, "row": 7, 
> "value": 0, "letter": "Е"}, {"col": 7, "row": 7, "value": 1, "letter": 
> "Н"}, {"col": 6, "row": 7, "value": 1, "letter": "О"}, {"col": 5, "row": 
> 7, "value": 2, "letter": "Р"}, {"col": 4, "row": 7, "value": 1, 
> "letter": "О"}, {"col": 3, "row": 7, "value": 2, "letter": "К"}]    |    
> 24 | РЕНОРОК | ОК**ОНР | f
>      131 | play   |   206 |  404 | 2018-02-20 09:26:05.234006+01 | 
> [{"col": 9, "row": 7, "value": 5, "letter": "Ь"}, {"col": 8, "row": 7, 
> "value": 2, "letter": "Д"}, {"col": 7, "row": 7, "value": 1, "letter": 
> "Е"}, {"col": 6, "row": 7, "value": 2, "letter": "С"}, {"col": 5, "row": 
> 7, "value": 1, "letter": "О"}, {"col": 4, "row": 7, "value": 2, 
> "letter": "Р"}, {"col": 3, "row": 7, "value": 2, "letter": "П"}]    |    
> 32 | ЬДЕСОРП |         | f
>    15676 | play   |  2785 | 2997 | 2018-04-18 16:56:58.368445+02 | 
> [{"col": 12, "row": 7, "value": 5, "letter": "Ь"}, {"col": 11, "row": 7, 
> "value": 1, "letter": "Н"}, {"col": 10, "row": 7, "value": 1, "letter": 
> "Е"}, {"col": 8, "row": 7, "value": 0, "letter": "Г"}, {"col": 9, "row": 
> 7, "value": 2, "letter": "Р"}, {"col": 7, "row": 7, "value": 1, 
> "letter": "И"}, {"col": 6, "row": 7, "value": 2, "letter": "М"}] |    28 
> | МИЬРНГЕ |         | f
> (5 rows)
> This stored function -
> 
> 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.tile->'col')::int     AS col,
>              (t.tile->'row')::int     AS row,
>              (t.tile->'letter')::text AS letter,
>              (t.tile->'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 = 'play' 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;
> 
> gives me same error (why does it think it is JSONB and not integer?)

As Thomas pointed there is a difference between -> and ->>:

test_(postgres)# select pg_typeof('[{"one": 1, "two": 2}]'::jsonb -> 0 
-> 'one'), '[{"one": 1, "two": 2}]'::jsonb -> 0 -> 'one';
  pg_typeof | ?column?
-----------+----------
  jsonb     | 1
(1 row)

test_(postgres)# select pg_typeof('[{"one": 1, "two": 2}]'::jsonb -> 0 
->> 'one'), '[{"one": 1, "two": 2}]'::jsonb -> 0 -> 'one';
  pg_typeof | ?column?
-----------+----------
  text      | 1

> 
> 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.tile->'col')::int     AS col,
>                                      ^
> 
> And I would prefer not to use ->> because I want col, row, value as 
> integers and not text

You will get an integer:

test_(postgres)# select pg_typeof(('[{"one": 1, "two": 2}]'::jsonb -> 0 
->> 'one')::int), ('[{"one": 1, "two": 2}]'::jsonb -> 0 ->> 'one')::int;
  pg_typeof | int4
-----------+------
  integer   |    1

> 
> Regards
> Alex
> 
> P.S. Below is the table definition:
> 
> words_ru=> \d words_moves
>                                        Table "public.words_moves"
>   Column  |           Type           | Collation | Nullable 
> |                 Default
> ---------+--------------------------+-----------+----------+------------------------------------------
>   mid     | bigint                   |           | not null | 
> nextval('words_moves_mid_seq'::regclass)
>   action  | text                     |           | not null |
>   gid     | integer                  |           | not null |
>   uid     | integer                  |           | not null |
>   played  | timestamp with time zone |           | not null |
>   tiles   | jsonb                    |           |          |
>   score   | integer                  |           |          |
>   letters | text                     |           |          |
>   hand    | text                     |           |          |
>   puzzle  | boolean                  |           | not null | false
> Indexes:
>      "words_moves_pkey" PRIMARY KEY, btree (mid)
>      "words_moves_gid_played_idx" btree (gid, played DESC)
>      "words_moves_uid_action_played_idx" btree (uid, action, played)
>      "words_moves_uid_idx" btree (uid)
> Check constraints:
>      "words_moves_score_check" CHECK (score >= 0)
> Foreign-key constraints:
>      "words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES 
> words_games(gid) ON DELETE CASCADE
>      "words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES 
> words_users(uid) ON DELETE CASCADE
> Referenced by:
>      TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY 
> (mid) REFERENCES words_moves(mid) ON DELETE CASCADE
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Calling jsonb_array_elements 4 times in the same query

From
Alexander Farber
Date:
Thank you -

On Mon, Oct 21, 2019 at 11:20 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
As Thomas pointed there is a difference between -> and ->>:

test_(postgres)# select pg_typeof('[{"one": 1, "two": 2}]'::jsonb -> 0
-> 'one'), '[{"one": 1, "two": 2}]'::jsonb -> 0 -> 'one';
  pg_typeof | ?column?
-----------+----------
  jsonb     | 1
(1 row)

test_(postgres)# select pg_typeof('[{"one": 1, "two": 2}]'::jsonb -> 0
->> 'one'), '[{"one": 1, "two": 2}]'::jsonb -> 0 -> 'one';
  pg_typeof | ?column?
-----------+----------
  text      | 1

I have ended up with the stored function using ->> and casting:

 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,
            (j.tile->>'col')::int   AS col,
            (j.tile->>'row')::int   AS row,
             j.tile->>'letter'      AS letter,
            (j.tile->>'value')::int AS value
        FROM words_moves m
        CROSS JOIN JSONB_ARRAY_ELEMENTS(m.tiles) AS j(tile)
        LEFT JOIN words_games g USING(gid)
        LEFT JOIN LATERAL (SELECT gid, played FROM words_moves WHERE mid = in_mid) AS m2 ON TRUE
        WHERE m.action = 'play'
        AND m.gid = m2.gid
        AND m.played <= m2.played
        ORDER BY m.played ASC;
$func$ LANGUAGE sql;

It gives me the desired output:

 out_bid | out_mid | out_hand | out_col | out_row | out_letter | out_value
---------+---------+----------+---------+---------+------------+-----------
       1 |  385934 | РТМРЕКО  |       7 |       7 | О          |         1
       1 |  385934 | РТМРЕКО  |       7 |       3 | М          |         2
       1 |  385934 | РТМРЕКО  |       7 |       4 | Е          |         1
       1 |  385934 | РТМРЕКО  |       7 |       5 | Т          |         2
       1 |  385934 | РТМРЕКО  |       7 |       6 | Р          |         2
       1 |  386610 | МИЛСЯРО  |       5 |       6 | Л          |         2
       1 |  386610 | МИЛСЯРО  |       6 |       6 | Я          |         3
       1 |  386610 | МИЛСЯРО  |       4 |       6 | О          |         1
       1 |  386610 | МИЛСЯРО  |       3 |       6 | М          |         2
       1 |  391416 | РКП*АДЕ  |       4 |       9 | Л          |         0
       1 |  391416 | РКП*АДЕ  |       4 |      10 | К          |         2
       1 |  391416 | РКП*АДЕ  |       4 |       5 | Р          |         2
       1 |  391416 | РКП*АДЕ  |       4 |       7 | Д          |         2
       1 |  391416 | РКП*АДЕ  |       4 |       4 | П          |         2
       1 |  391416 | РКП*АДЕ  |       4 |       8 | Е          |         1
       1 |  391416 | РКП*АДЕ  |       4 |      11 | А          |         1
(16 rows)

Regards
Alex