Query: network_load_balancers_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
),
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
    )
),
azure_lb_backend_address_pool as (
  select
    lower(p.id) as pool_id,
    lower(c ->> '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
    )
)
select
  distinct on (id) lower(id) as network_load_balancer_id
from
  azure_lb,
  jsonb_array_elements(backend_address_pools) as pool
where
  lower(pool ->> 'id') in (
    select
      pool_id
    from
      azure_lb_backend_address_pool
  );