Query: compute_virtual_machines_for_network_security_group

with network_interface_list as (
  select
    nsg.id as nsg_id,
    nic.id as nic_id
  from
    azure_network_security_group as nsg,
    jsonb_array_elements(network_interfaces) as ni
    left join azure_network_interface as nic on lower(nic.id) = lower(ni ->> 'id')
  where
    lower(nsg.id )= $1
    and nsg.subscription_id = split_part($1, '/', 3)
)
select
  lower(vm.id) as virtual_machine_id
from
  azure_compute_virtual_machine as vm,
  jsonb_array_elements(network_interfaces) as ni
  left join network_interface_list as nic on lower(nic.nic_id) = lower(ni ->> 'id')
where
  lower(nic.nsg_id) = $1
  and subscription_id = split_part($1, '/', 3);