Query: compute_virtual_machines_for_network_virtual_network

with subnet_list as (
  select
    lower(id) as vn_id,
    lower(sub ->> 'id') as sub_id,
    sub ->> 'name' as sub_name
  from
    azure_virtual_network as n,
    jsonb_array_elements(subnets) as sub
  where
    lower(id) = $1
),
virtual_machine_nic_list as (
  select
    m.id as machine_id,
    n.ip_configurations as ip_configs
  from
    azure_compute_virtual_machine as m,
    jsonb_array_elements(network_interfaces) as nic
    left join azure_network_interface as n on lower(n.id) = lower(nic ->> 'id')
)
select
  lower(l.machine_id) as virtual_machine_id
from
  virtual_machine_nic_list as l,
  jsonb_array_elements(ip_configs) as ip_config
where
  lower(ip_config -> 'properties' -> 'subnet' ->> 'id') in (
    select
      sub_id
    from
      subnet_list
  );