Get average of JSONB array in postgres-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

Also you can use lateral join in next way:

select gameID, avg((s->>'score')::int) avg_score
from g, lateral jsonb_array_elements((gameInfo->>'scores')::jsonb) s
group by gameID
;

SQL editor online

Result:

 ======== ==================== 
| gameid | avg_score          |
 ======== ==================== 
| 3      | 3.0000000000000000 |
 -------- -------------------- 
| 2      | 4.0000000000000000 |
 -------- -------------------- 
| 1      | 2.0000000000000000 |
 -------- --------------------