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@xxxxxxx
<mailto: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)
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@xxxxxxxxxxx
