Query: backup_vaults_for_backup_plan

with plan_details as (
  select
    arn,
    jsonb_array_elements(backup_plan -> 'Rules') as rule
  from
    aws_backup_plan
  where
    arn = $1
    and account_id = split_part($1, ':', 5)
    and region = split_part($1, ':', 4)
),
vault_names as (
  select
    rule ->> 'TargetBackupVaultName' as vault_name
  from
    plan_details
)
select
  v.arn as backup_vault_arn
from
  aws_backup_vault v
join
  vault_names vn on v.name = vn.vault_name
where
  v.account_id = split_part($1, ':', 5)
  and v.region = split_part($1, ':', 4);