Query: network_security_groups_for_compute_virtual_machine

with network_interface_id as (
  select
    id as vm_id,
    jsonb_array_elements(network_interfaces)->>'id' as n_id
  from
    azure_compute_virtual_machine
  where
    lower(id) = $1
    and subscription_id = split_part($1, '/', 3)
)
select
  lower(s.id) as nsg_id
from
  network_interface_id as vn
  left join azure_network_interface as n on lower(vn.n_id) = lower(n.id)
  left join azure_network_security_group as s on lower(n.network_security_group_id) = lower(s.id)
where
  s.id is not null