Question 114793 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Get average of JSONB array in postgres

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?

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 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 |
+--------+--------------------+
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search