Query: compute_virtual_machines_for_network_load_balancer

with backend_address_pools as (
  select
    lb.id as lb_id,
    p.id as backend_address_id,
    p.backend_ip_configurations as backend_ip_configurations
  from
    azure_lb as lb,
    jsonb_array_elements(backend_address_pools) as b
    left join azure_lb_backend_address_pool as p on lower(p.id) = lower(b ->> 'id')
  where
    p.backend_ip_configurations is not null
    and lower(lb.id) = $1
    and lb.subscription_id = split_part($1, '/', 3)
),
backend_ip_configurations as (
  select
    lb_id,
    backend_address_id,
    c ->> 'id' as backend_ip_configuration_id
  from
    backend_address_pools,
    jsonb_array_elements(backend_ip_configurations) as c
),
network_interface as (
  select
    lb_id,
    backend_address_id,
    nic.id as nic_id,
    nic.virtual_machine_id as virtual_machine_id,
    c ->> 'id' as backend_ip_configuration_id
  from
    azure_network_interface as nic,
    jsonb_array_elements(ip_configurations) as c,
    backend_ip_configurations as b
  where
    c ->> 'id' = b.backend_ip_configuration_id
)
select
  lower(id) as virtual_machine_id
from
  azure_compute_virtual_machine
where
  lower(id) in (select lower(virtual_machine_id) from network_interface)