Query: network_load_balancers_for_compute_virtual_machine_scale_set

with lb_backend_address_pool as (
  select
    b ->> 'id' as backend_address_pool_id
  from
    azure_compute_virtual_machine_scale_set as s,
    jsonb_array_elements(
      virtual_machine_network_profile -> 'networkInterfaceConfigurations'
    ) as p,
    jsonb_array_elements(p -> 'properties' -> 'ipConfigurations') as c,
    jsonb_array_elements(
      c -> 'properties' -> 'loadBalancerBackendAddressPools'
    ) as b
  where
    lower(s.id) = $1
)
select
  lower(lb.id) as lb_id
from
  azure_lb as lb,
  jsonb_array_elements(backend_address_pools) as p
where
  lower(p ->> 'id') in (
    select
      lower(backend_address_pool_id)
    from
      lb_backend_address_pool
  )