主页 / user-255311

Nik Rubblers's questions

Martin Hope
Nik Rubblers
Asked: 2022-07-09 00:27:57 +0800 CST
  • 1

我正在努力尝试将表中的结果聚合到嵌套的 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

但仍然没有将嵌套它们的游戏分组到区域超级对象中。继续挣扎。。

aggregate json