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(at)gmx(dot)net
> <mailto:spam_eater(at)gmx(dot)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(dot)klaver(at)aklaver(dot)com