Query: network_application_gateways_for_compute_virtual_machine

with network_interface as (
  select
    vm.id,
    nic.id,
    nic.ip_configurations as ip_configurations
  from
    azure_compute_virtual_machine as vm,
    jsonb_array_elements(network_interfaces) as n
    left join azure_network_interface as nic on nic.id = n ->> 'id'
  where
    lower(vm.id) = $1
    and vm.subscription_id = split_part($1, '/', 3)
),
vm_application_gateway_backend_address_pool as (
  select
    p ->> 'id' as id
  from
    network_interface,
    jsonb_array_elements(ip_configurations) as i,
    jsonb_array_elements(i -> 'properties' -> 'applicationGatewayBackendAddressPools') as p
)
select
  lower(g.id) as application_gateway_id
from
  azure_application_gateway as g,
  jsonb_array_elements(backend_address_pools) as p
where
  lower(p ->> 'id') in (select lower(id) from vm_application_gateway_backend_address_pool);