Get average of JSONB array in postgres

Question

I have a postgres table 'games' containing different scores for a game. I want to query all the games and have the average score of all the scores for that specific game. I tried a lot of different queries but I always get in trouble because of the JSONB datatype. The data of the games are saved in JSONB format and the games table looks like this:

gameID     gameInfo
---------------------------------------------------------------
  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
            }
            ]}

Expected output:

GameId	AverageScore
1	2
2	4
2	3

What query can I use to get the expected output?

Answer 1

Extract JSONB representing an array, use a JSONB function to get array of JSONB, extract the string value.

select gameid, avg(score::int) s
from (
  select gameid, jsonb_array_elements(gameInfo #>'{scores}') ->'score' score
  from foo
) t
group by gameid
order by gameid