Query: ram_user_manage_policies_sankey

with args as (
    select $1 as ram_user_arn
)

-- User
select
  null as from_id,
  name as id,
  title,
  0 as depth,
  'alicloud_ram_user' as category
from
  alicloud_ram_user
where
  arn in (select ram_user_arn from args)
  and account_id in (select split_part(ram_user_arn,':',4) from args)
-- Groups
union select
  u.name as from_id,
  g.name as id,
  user_groups ->> 'GroupName' as title,
  1 as depth,
  'alicloud_ram_group' as category
from
  alicloud_ram_user as u,
  jsonb_array_elements(u.groups) as user_groups
  inner join alicloud_ram_group g on g.name = user_groups ->> 'GroupName'
where
  u.arn in (select ram_user_arn from args)
  and u.account_id in (select split_part(ram_user_arn,':',4) from args)

-- Policies (attached to groups)
union select
  g.name as from_id,
  p.title as id,
  p.title as title,
  2 as depth,
  'alicloud_ram_policy' as category
from
  alicloud_ram_user as u,
  alicloud_ram_policy as p,
  jsonb_array_elements(u.groups) as user_groups
  inner join alicloud_ram_group g on g.name = user_groups ->> 'GroupName',
  jsonb_array_elements(g.attached_policy) as user_policy
where
  user_policy ->> 'PolicyName' = p.title
  and u.arn in (select ram_user_arn from args)
  and u.account_id in (select split_part(ram_user_arn,':',4) from args)
-- Policies (attached to user)
union select
  u.name as from_id,
  p.title as id,
  p.title as title,
  2 as depth,
  'alicloud_ram_policy' as category
from
  alicloud_ram_user as u,
  jsonb_array_elements(u.attached_policy) as pol_arn,
  alicloud_ram_policy as p
where
  pol_arn ->> 'PolicyName' = p.title
  and u.arn in (select ram_user_arn from args)
  and u.account_id in (select split_part(ram_user_arn,':',4) from args);