Re: Calling jsonb_array_elements 4 times in the same query

Thank you -

On Mon, Oct 21, 2019 at 11:20 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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;

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