Query: virtual_network_routes

with route_tables as (
  select
    distinct (s -> 'properties' -> 'routeTable' ->> 'id') as id
  from
    azure_virtual_network,
    jsonb_array_elements(subnets) as s
  where
    lower(id) = $1
),
data as (
  select
    *
  from
    route_tables as t
    left join azure_route_table as rt on lower(t.id) = lower(rt.id)
)
select
  r ->> 'name' as "Name",
  r -> 'properties' ->> 'addressPrefix' as "Address Prefix",
  r -> 'properties' ->> 'nextHopType' as "Next Hop Type",
  r ->> 'id' as "Route ID"
from
  data,
  jsonb_array_elements(routes) as r;