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;
