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
)
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
  );