Query: backup_plans_for_backup_vault

with filtered_vault as (
  select
    name
  from
    aws_backup_vault
  where
    arn = $1
    and account_id = split_part($1, ':', 5)
    and region = split_part($1, ':', 4)
),
backup_rules as (
  select
    p.arn as backup_plan_arn,
    jsonb_array_elements(p.backup_plan -> 'Rules') as rule
  from
    aws_backup_plan p
where
  p.account_id = split_part($1, ':', 5)
  and p.region = split_part($1, ':', 4)
)
select
  br.backup_plan_arn
from
  backup_rules br
  join filtered_vault fv on br.rule ->> 'TargetBackupVaultName' = fv.name;