Query: network_load_balancer_backend_address_pools_for_network_virtual_network

with subnet_list as (
  select
    lower(s ->> 'id') as subnet_id
  from
    azure_virtual_network as v,
    jsonb_array_elements(v.subnets) as s
  where
    lower(v.id) = $1
    and v.subscription_id = split_part($1, '/', 3)
),
nic_subnet_list as (
  select
    lower(nic.id) as nic_id,
    lower(ip_config ->> 'id') as ip_config_id,
    ip_config -> 'properties' -> 'subnet' ->> 'id',
    title
  from
    azure_network_interface as nic,
    jsonb_array_elements(ip_configurations) as ip_config
  where
    lower(ip_config -> 'properties' -> 'subnet' ->> 'id') in (select subnet_id from subnet_list)
)
select
  lower(p.id) as pool_id
from
  azure_lb_backend_address_pool as p,
  jsonb_array_elements(p.backend_ip_configurations) as c
where
  lower(c ->> 'id') in (select ip_config_id from nic_subnet_list);