Re: How to return a jsonb list of lists (with integers)

[Alexander Farber]

I have ended up with the following (to avoid returning [null, null, null]
for players who never played with each other):

                _versus := JSONB_BUILD_ARRAY(
                        SUM(CASE WHEN (player1 = in_uid AND state1 =
'won')  OR (player2 = in_uid AND state2 = 'won')  THEN 1 ELSE 0
END)::integer,
                        SUM(CASE WHEN (player1 = in_uid AND state1 =
'lost') OR (player2 = in_uid AND state2 = 'lost') THEN 1 ELSE 0
END)::integer,
                        SUM(CASE WHEN (player1 = in_uid AND state1 =
'draw') OR (player2 = in_uid AND state2 = 'draw') THEN 1 ELSE 0
END)::integer
                )
                FROM words_games
                WHERE finished IS NOT NULL
                AND (
                        (player1 = in_uid AND player2 = in_opponent) OR
                        (player2 = in_uid AND player1 = in_opponent)
                );

                IF _versus <> '[null, null, null]'::jsonb THEN
                        out_data := JSONB_INSERT(out_data, '{versus}',
_versus);
                END IF;