Query: ecs_security_group_unassociated_status

with associated_sg as (
  select
    group_id
  from
    alicloud_ecs_network_interface,
    jsonb_array_elements_text(security_group_ids) as group_id
),
sg_list as (
  select
    sg.security_group_id,
    case
      when a.group_id is null then false
      else true
    end as is_associated
  from
    alicloud_ecs_security_group as sg
    left join associated_sg a on sg.security_group_id = a.group_id
)
select
  case
    when is_associated then 'associated'
    else 'unassociated'
  end as sg_association_status,
    count(*)
from
  sg_list
group by
  is_associated;