將嵌套的 json_arrayagg 與具有多對多關係的兩個表一起使用

考慮以下mysql數據庫中的兩個表

role(role_id);
task(task_id);

和一個數據透視表

role(role_id,task_id)

我可以使用以下查詢以 JSON 格式提取單個角色

select json_object
          (
              'id', r.role_uuid,
              'tasks', (select json_arrayagg(
                                      json_object(
                                              'id', t.task_id
                                          ))
                       from role r
                                inner join role_task rt on r.role_id = rt.role_id
                                inner join task t on rt.task_id = t.task_id
                       where r.role_id = @role_1)
          )
from role r
where r.role_id = @role_1;

但是，當條件返回多個角色時，我不知道該怎麼做。

例如，如果我將 where 條件修改為以下

where r.role_id =@role_1 or r.role_id=@role_2; 

知道了，我試圖加入表的別名而不是實際的表

select json_arrayagg(
              json_object
                  (
                      'id', r1.role_id,
                      'task', (select json_arrayagg(
                                             json_object(
                                                     'id', task.task_id
                                                 ))
                              from task
                                       join role_task rt on task.task_id = rt.task_id
                                       join role on rt.role_id = role.role_id
                                       where role.role_id = r1.role_id
                      )
                  )
          )
from role r1
where r1.role_id = @role_1
  or r1.role_id = @role_4

WITH 
cte AS (
   SELECT DISTINCT
          role_id,
          JSON_OBJECT('task',
                      JSON_ARRAYAGG(JSON_OBJECT('uuid', 
                                                task_id)) OVER (PARTITION BY role_id),
                      'uuid',
                      role_id) single_object
   FROM role
   NATURAL JOIN role_task
   NATURAL JOIN task
   WHERE role_id IN ('role_1', 'role_4')
)
SELECT JSON_ARRAYAGG(single_object)
INTO @temp
FROM cte;

小提琴

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