How to use json_arrayagg function on more than one pivot table



  • As the title suggests consider the following example where there are three tables and two pivot table

    #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));

    If I want to extract the following JSON using json_arrayagg function what approach should I take?

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

    Here is https://www.db-fiddle.com/f/xuMuaXbADVG8FvkKPDpfjj/0 link with schema and sample data and also here is https://www.db-fiddle.com/f/gxxxCrZ3JwXDWbiP1yMGph/1 on how to solve the problem.



  • This is another take:

    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
    ;
    

    as illustrated in https://www.db-fiddle.com/f/xuMuaXbADVG8FvkKPDpfjj/3




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2