Query: service_tree

with pods as (
  select
    uid as pod_uid,
    title as pod_title
  from
    kubernetes_pod
  where
    selector_search in (
      select
        selector_query
      from
        kubernetes_service
      where
        uid = $1
    )
),
services as (
  select
    uid,
    title,
    pod_uid,
    pod_title,
    cluster_ip,
    external_ips,
    selector_query,
    l as lb,
    p ->> 'protocol' as protocol_number,
    concat(p ->> 'port', '/', p ->> 'protocol') as port,
    concat(p ->> 'targetPort', '/', p ->> 'protocol') as targetPort
  from
    pods,
    kubernetes_service,
    jsonb_array_elements(ports) as p,
    jsonb_array_elements(load_balancer_ingress) as l
  where
    uid = $1
  union
  select
    uid,
    title,
    pod_uid,
    pod_title,
    cluster_ip,
    external_ips,
    selector_query,
    load_balancer_ingress as lb,
    p ->> 'protocol' as protocol_number,
    concat(p ->> 'port', '/', p ->> 'protocol') as port,
    concat(p ->> 'targetPort', '/', p ->> 'protocol') as targetPort
  from
    pods,
    kubernetes_service,
    jsonb_array_elements(ports) as p
  where
    uid = $1
    and load_balancer_ingress is null
) -- LB
select
  lb :: text as id,
  lb ->> 'ip' as title,
  'lb' as category,
  null as from_id,
  null as to_id
from
  services -- EIP
union all
select
  eip as id,
  eip as title,
  'external_ip' as category,
  null as from_id,
  null as to_id
from
  services,
  jsonb_array_elements_text(external_ips) as eip -- ClusterIP
union all
select
  cluster_ip as id,
  cluster_ip as title,
  'cluster_ip' as category,
  null as from_id,
  null as to_id
from
  services -- Ports
union all
select
  port as id,
  port as title,
  'port' as category,
  null as from_id,
  null as to_id
from
  services -- service
union all
select
  distinct title as id,
  title as title,
  'service' as category,
  null as from_id,
  null as to_id
from
  services -- targetPorts
union all
select
  concat(targetPort, ' (Target Port)') as id,
  targetPort as title,
  'targetPort' as category,
  null as from_id,
  null as to_id
from
  services -- pods
union all
select
  distinct pod_title as id,
  pod_title as title,
  'pod' as category,
  null as from_id,
  null as to_id
from
  services -- externalIP -> port
union
select
  null as id,
  null as title,
  'external_ip' as category,
  eip as from_id,
  port as to_id
from
  services,
  jsonb_array_elements_text(external_ips) as eip -- clusterIP -> port
union
select
  null as id,
  null as title,
  'cluster_ip' as category,
  cluster_ip as from_id,
  port as to_id
from
  services -- lb -> port
union
select
  null as id,
  null as title,
  'lb' as category,
  lb :: text as from_id,
  port as to_id
from
  services -- port -> service
union
select
  null as id,
  null as title,
  'port' as category,
  port as from_id,
  title as to_id
from
  services -- service -> target
union
select
  null as id,
  null as title,
  'service' as category,
  title as from_id,
  concat(targetPort, ' (Target Port)') as to_id
from
  services -- target -> pod
union
select
  null as id,
  null as title,
  'targetPort' as category,
  concat(targetPort, ' (Target Port)') as from_id,
  pod_title as to_id
from
  services