Re: Calling jsonb_array_elements 4 times in the same query

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