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@xxxxxxx> 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 = "" 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 = "" 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
