SELECT id, SUM(win) AS Wins, SUM(draw) AS Draws, SUM(loss) AS Losses FROM (SELECT t.participants.prisoner_a AS id, CASE WHEN r.result.winner='prisoner_a' THEN 1 ELSE 0 END AS win, CASE WHEN r.result.winner='draw' THEN 1 ELSE 0 END AS draw, CASE WHEN r.result.winner='prisoner_b' THEN 1 ELSE 0 END AS loss FROM results t CROSS JOIN UNNEST(t.results) AS r(result) UNION ALL SELECT t.participants.prisoner_b AS id, CASE WHEN r.result.winner='prisoner_b' THEN 1 ELSE 0 END AS win, CASE WHEN r.result.winner='draw' THEN 1 ELSE 0 END AS draw, CASE WHEN r.result.winner='prisoner_a' THEN 1 ELSE 0 END AS loss FROM results t CROSS JOIN UNNEST(t.results) AS r(result) ) GROUP BY id ORDER BY wins DESC, losses ASC, id;