SQLize Online / PHPize Online  /  SQLtest Online

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 ;