Re: Calling jsonb_array_elements 4 times in the same query

[Alexander Farber <alexander.farber@xxxxxxxxx>]

Thank you -

On Mon, Oct 21, 2019 at 11:20 PM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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 = "">        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