create table g (gameID int, gameInfo jsonb);
insert into g values (1, '{"scores": [
{"scoreType": "skill", "score": 1 },
{"scoreType": "speed", "score": 3 },
{"scoreType": "strength", "score": 2}
]}'),
(2, '{"scores": [
{"scoreType": "skill", "score": 4 },
{"scoreType": "speed", "score": 4 },
{"scoreType": "strength", "score": 4 }
]}'),
(3, '{"scores": [
{"scoreType": "skill", "score": 1 },
{"scoreType": "speed", "score": 3 },
{"scoreType": "strength", "score": 5 }
]}');
select * from g;
select gameID, s
from g, lateral jsonb_array_elements((gameInfo->>'scores')::jsonb) s
;
select gameID, avg((s->>'score')::int) avg_score
from g, lateral jsonb_array_elements((gameInfo->>'scores')::jsonb) s
group by gameID
;