SQL tricks
September 6

How to calculate average value from json array (PostgreSQL)

Assume we have table stored values within json array. For example:

+========+=============================================+
| gameid | gameinfo                                    |
+========+=============================================+
| 1      | {"scores": [                                |
|        |     {"score": 1, "scoreType": "skill"},     | 
|        |     {"score": 3, "scoreType": "speed"},     | 
|        |     {"score": 2, "scoreType": "strength"}   |
|        |   ]                                         |
|        | }                                           |
+--------+---------------------------------------------+
| 2      | {"scores": [                                |
|        |     {"score": 4, "scoreType": "skill"},     | 
|        |     {"score": 4, "scoreType": "speed"},     | 
|        |     {"score": 4, "scoreType": "strength"}   |
|        |   ]                                         |
|        | }                                           |
+--------+---------------------------------------------+
| 3      | {"scores": [                                |
|        |     {"score": 1, "scoreType": "skill"},     | 
|        |     {"score": 3, "scoreType": "speed"},     | 
|        |     {"score": 5, "scoreType": "strength"}   |
|        |   ]                                         |
|        | }                                           |
+--------+---------------------------------------------+

We need to get average score for each game. We can do it in next way:

json_array_elements - convert json array into rows, so we can use lateral join and get next table

select gameID, s
from g, lateral jsonb_array_elements((gameInfo->>'scores')::jsonb) s
;
+========+=======================================+
| gameid | s                                     |
+========+=======================================+
| 1      | {"score": 1, "scoreType": "skill"}    |
+--------+---------------------------------------+
| 1      | {"score": 3, "scoreType": "speed"}    |
+--------+---------------------------------------+
| 1      | {"score": 2, "scoreType": "strength"} |
+--------+---------------------------------------+
| 2      | {"score": 4, "scoreType": "skill"}    |
+--------+---------------------------------------+
| 2      | {"score": 4, "scoreType": "speed"}    |
+--------+---------------------------------------+
| 2      | {"score": 4, "scoreType": "strength"} |
+--------+---------------------------------------+
| 3      | {"score": 1, "scoreType": "skill"}    |
+--------+---------------------------------------+
| 3      | {"score": 3, "scoreType": "speed"}    |
+--------+---------------------------------------+
| 3      | {"score": 5, "scoreType": "strength"} |
+--------+---------------------------------------+

Now we have plane table with gameid and scores object. After that we can calculate average score using aggregation by gameid:

select gameID, avg((s->>'score')::int) avg_score
from g, lateral jsonb_array_elements((gameInfo->>'scores')::jsonb) s
group by gameID
;
Result:
+========+====================+
| gameid | avg_score          |
+========+====================+
| 3      | 3.0000000000000000 |
+--------+--------------------+
| 2      | 4.0000000000000000 |
+--------+--------------------+
| 1      | 2.0000000000000000 |
+--------+--------------------+

Test this example on SQL editor online