Обсуждение: Calling jsonb_array_elements 4 times in the same query
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
);
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
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?
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
Alex
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
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;
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,
^
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
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
> 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
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)
--------+--------+-------+------+-------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------+---------+--------
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;
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,
^
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
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
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
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;
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)
---------+---------+----------+---------+---------+------------+-----------
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