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?

Serg · Accepted Answer · 2022-09-06 11:15:16Z

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

answered Sep 6, 2022 at 11:15

Serg

22.8k5 gold badges23 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Slava Rozhnev · Accepted Answer · 2022-09-06 11:21:26Z

0

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 |
+--------+--------------------+

answered Sep 6, 2022 at 11:21

Slava Rozhnev

10.2k6 gold badges27 silver badges43 bronze badges

Collectives™ on Stack Overflow

Get average of JSONB array in postgres

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related