如何在多个枢轴表上使用json_arrayagg函数

Question

如标题所示，请考虑下面的示例，其中有三个表和两个枢轴表

#Tables
user(user_id char(42));
role(role_id char(42));
task(task_id char(42));

#Pivot tables
user_role(user_id char(42),role_id char(42));
role_task(role_id char(42),task_id char(42));

如果我想使用json_arrayagg函数提取以下JSON，应该采取什么方法？

[{
    "id": "userId",
    "roles": [{
        "id": "roleId",
        "tasks": [{
            "id": "taskId"
        }]
    }]
}]

下面是与模式和示例数据的Db小提琴链接，以及关于如何解决问题的我的想法链接。

Answer 1

这是另一个例子：

select json_arrayagg(jt.jo ) from (
select
  json_object(
    'id',
    ur.user_id,
    'roles',
    json_arrayagg(
      (select json_object(
                'id',
                rt.role_id,
                'tasks',
                json_arrayagg(
                  json_object('id', rt.task_id)
                )
              )
         from role_task rt
         where  rt.role_id = ur.role_id
         group by rt.role_id
      ))
    ) jo
  from user_role ur
  group by ur.user_id
  order by ur.user_id
) jt
;

如这把小提琴所示