Query: ec2_application_load_balancers_for_ec2_instance

with aws_ec2_instances as (
  select
    instance_id,
    region,
    account_id,
    arn
  from
    aws_ec2_instance
  where
    account_id = split_part($1, ':', 5)
    and region = split_part($1, ':', 4)
    and arn = $1
),
aws_ec2_target_groups as (
  select
    target_health_descriptions,
    load_balancer_arns
  from
    aws_ec2_target_group
  where
    account_id = split_part($1, ':', 5)
    and region = split_part($1, ':', 4)
),
aws_ec2_application_load_balancers as (
  select
    arn
  from
    aws_ec2_application_load_balancer
  where
    account_id = split_part($1, ':', 5)
    and region = split_part($1, ':', 4)
)
select
  distinct lb.arn as application_load_balancer_arn
from
  aws_ec2_instances as i,
  aws_ec2_target_groups as target,
  jsonb_array_elements(target.target_health_descriptions) as health_descriptions,
  jsonb_array_elements_text(target.load_balancer_arns) as l,
  aws_ec2_application_load_balancers as lb
where
  health_descriptions -> 'Target' ->> 'Id' = i.instance_id
  and l = lb.arn;