MySql 8 nested json aggregation functions

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply