MySql 8 嵌套 json 聚合函式

我正在努力嘗試將表中的結果聚合到嵌套的 json 中。

這是表格：

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

我想創建一個顯示如下內容的視圖：

+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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]}]}] |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+

將所有遊戲及其步驟分組到它們所屬的區域中。 這是我創建的 dbfiddle。

我嘗試了不同的方法，比如從

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 ;

奇怪的是，它沒有按區域分組，或者

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 ;

或許多其他嘗試。我可以獲得離散區域結果，例如：

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;
               

但是任何試圖將區域聚合成對像數組的嘗試都失敗了。任何人都可以幫助我了解我錯過了什麼嗎？

更新：

這更接近我想要得到的東西：

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

但仍然沒有將嵌套它們的遊戲分組到區域超級對像中。繼續掙扎。。

這似乎是實際的解決方案

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;

即使我仍在調查select distinct steps哪些可能重複

引用自：https://dba.stackexchange.com/questions/314195