I have tried switching to SELECT INTO, but IF FOUND is still always true, which gives me [ null, null, null ] for some users:
SELECT JSONB_BUILD_ARRAY(
SUM(CASE WHEN (player1 = in_viewer AND state1 = 'won') OR (player2 = in_viewer AND state2 = 'won') THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN (player1 = in_viewer AND state1 = 'lost') OR (player2 = in_viewer AND state2 = 'lost') THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN (player1 = in_viewer AND state1 = 'draw') OR (player2 = in_viewer AND state2 = 'draw') THEN 1 ELSE 0 END)::integer
)
FROM words_games
WHERE finished IS NOT NULL
AND in_viewer IN (player1, player2)
AND in_uid IN (player1, player2)
INTO _versus;
IF FOUND THEN -- for some reason this is always true
out_data := JSONB_INSERT(out_data, '{versus}', _versus);
END IF;
What works for me is:
IF _versus <> '[null,null,null]'::jsonb THEN
out_data := JSONB_INSERT(out_data, '{versus}', _versus);
END IF;
Greetings
Alex