Query: network_application_gateways_for_compute_virtual_machine_scale_set

with application_gateway_backend_address_pool as (
  select
    lower(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' -> 'applicationGatewayBackendAddressPools' ) as b
  where
    lower(s.id) = $1
    and s.subscription_id = split_part($1, '/', 3)
)
select
  lower(g.id) as application_gateway_id
from
  azure_application_gateway as g,
  jsonb_array_elements(backend_address_pools) as p
  left join application_gateway_backend_address_pool as pool on lower(pool.backend_address_pool_id) = lower(p ->> 'id')
where
  pool.backend_address_pool_id is not null