Calling jsonb_array_elements 4 times in the same query

Started by Alexander Farberover 6 years ago8 messagesgeneral

alexander.farber@gmail.com

over 6 years ago

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

Thomas Kellerer

spam_eater@gmx.net

over 6 years ago

In reply to: Alexander Farber (#1)

Re: Calling jsonb_array_elements 4 times in the same query

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

Alexander Farber

alexander.farber@gmail.com

over 6 years ago

In reply to: Thomas Kellerer (#2)

Re: Calling jsonb_array_elements 4 times in the same query

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

Adrian Klaver

adrian.klaver@aklaver.com

over 6 years ago

In reply to: Alexander Farber (#1)

Re: Calling jsonb_array_elements 4 times in the same query

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

Thomas Kellerer

spam_eater@gmx.net

over 6 years ago

In reply to: Alexander Farber (#3)

Re: Calling jsonb_array_elements 4 times in the same query

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,
                               ^

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

Alexander Farber

alexander.farber@gmail.com

over 6 years ago

In reply to: Thomas Kellerer (#5)

Re: Calling jsonb_array_elements 4 times in the same query

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:

Adrian Klaver

adrian.klaver@aklaver.com

over 6 years ago

In reply to: Alexander Farber (#6)

Re: Calling jsonb_array_elements 4 times in the same query

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

Alexander Farber

alexander.farber@gmail.com

over 6 years ago

In reply to: Adrian Klaver (#7)

Re: Calling jsonb_array_elements 4 times in the same query

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