Query: network_application_gateways_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)
)
select
  lower(g.id) as application_gateway_id
from
  azure_application_gateway as g,
  jsonb_array_elements(g.gateway_ip_configurations) as ip_config
where
  lower(ip_config -> 'properties' -> 'subnet' ->> 'id') in (select subnet_id from subnet_list);