I'm struggling trying to aggregate results in a table into a nested json.
this is the table:
+----+------+--------+------+------+-------+-----------+--------------+
| id | area | userId | game | step | score | completed | validAnswers |
+----+------+--------+------+------+-------+-----------+--------------+
| 1 | 2 | 21 | 7 | 53 | 10 | 0 | 0 |
| 2 | 2 | 37 | 7 | 53 | 0 | 0 | 0 |
| 3 | 2 | 21 | 7 | 53 | 10 | 0 | 0 |
| 4 | 2 | 37 | 7 | 53 | 10 | 0 | 0 |
...
| 37 | 3 | 21 | 7 | 57 | 80 | 1 | 8 |
| 38 | 2 | 21 | 8 | 56 | 80 | 1 | 8 |
| 39 | 2 | 21 | 7 | 58 | 100 | 1 | 10 |
| 40 | 2 | 21 | 7 | 59 | 50 | 1 | 5 |
+----+------+--------+------+------+-------+-----------+--------------+
I would like to create a view showing something like this:
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| userId | completedSteps |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 21 | [{"area": 2, "games": [{"id": 7, "steps": [58, 59]},{"id":8,"steps":[15,16,17]}]},{"area": 3, "games": [{"id": 1, "steps": [34, 18]},{"id":4,"steps":[11,12,14]}]}] |
| 18 | [{"area": 2, "games": [{"id": 7, "steps": [58, 59]},{"id":8,"steps":[15,16,17]}]},{"area": 3, "games": [{"id": 1, "steps": [34, 18]},{"id":4,"steps":[11,12,14]}]}] |
| 23 | [{"area": 2, "games": [{"id": 7, "steps": [58, 59]},{"id":8,"steps":[15,16,17]}]},{"area": 3, "games": [{"id": 1, "steps": [34, 18]},{"id":4,"steps":[11,12,14]}]}] |
| 11 | [{"area": 2, "games": [{"id": 7, "steps": [58, 59]},{"id":8,"steps":[15,16,17]}]},{"area": 3, "games": [{"id": 1, "steps": [34, 18]},{"id":4,"steps":[11,12,14]}]}] |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
grouping all games each with its steps into the area they belong. This is a dbfiddle I created.
I tried different approaches like beginning with
select userId,
json_arrayagg(
select distinct area
from scoreTable st2
where st1.userId =st2.userId and
st1.area=st2.area
group by area
) as completedSteps
from scoreTable st1
where completed = 1
group by userId ;
which sorprisingly doesn't group by area, or
select userId,
json_objectagg('areas',
(select distinct area
from scoreTable st2
where st1.userId =st2.userId
group by area)) as completedSteps
from scoreTable st1
where completed = 1
group by userId ;
or many other attempts. I can get discrete area results like:
select area,
json_object('games',json_object('id',game,'steps',JSON_ARRAYAGG(step))) as completedSteps
from scoreTable
where completed = 1
group by userId,area,game;
but whatever attempt tries to aggregate areas into array of objects fails. Could anybody help me undestad what am I missing?
UPDATE:
this is something closer to what I want to get:
select userId, json_arrayagg(json_object('completed',completed)) as completed
from (
select distinct userId, json_arrayagg(json_object('area',area,'games',completed)) as completed
from (
select distinct userId,area, json_object('id',game,'steps',(json_arrayagg(step))) as completed
from scoreTable
where completed = 1 and userId = 21
group by area,game
) st1
group by userId, area
) st3
group by userId
but still doesn't groups games nesting them into area super object. keeping struggling..
