MySql 8 nested json aggregation functions

Question

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

Answer 1

this seems to be the actual solution

select userId, json_arrayagg(areas) as completedSteps
from (
 select distinct userId,
 json_object('area',area,'games',(json_arrayagg(games))) as areas
 from (
 select distinct userId,area,
 json_object('id',game,'steps',(json_arrayagg(step))) as games
 from scoreTable
 where completed = 1 
 group by area,game,userId
 ) st1
 group by userId, area) st2
group by userId;

even if I'm still investigating how to select distinct steps which are possibly duplicate