Query: opensearch_domain_in_vpc

with public_subnets as (
  select
    distinct a -> 'SubnetId' as SubnetId
  from
    aws_vpc_route_table as t,
    jsonb_array_elements(associations) as a,
    jsonb_array_elements(routes) as r
  where
    r ->> 'DestinationCidrBlock' = '0.0.0.0/0'
    and  r ->>  'GatewayId' like 'igw-%'
), opensearch_domain_with_public_subnet as (
  select
    arn
  from
    aws_opensearch_domain ,
    jsonb_array_elements(vpc_options -> 'SubnetIds') as s
  where
    s in (select SubnetId from public_subnets)
)
select
  d.arn as resource,
  case
    when d.vpc_options ->> 'VPCId' is null then 'alarm'
    when d.vpc_options ->> 'VPCId' is not null and p.arn is not null then 'alarm'
    else 'ok'
  end status,
  case
    when vpc_options ->> 'VPCId' is null then title || ' not in VPC.'
    when d.vpc_options ->> 'VPCId' is not null and p.arn is not null then title || ' attached to public subnet.'
    else title || ' in VPC ' || (vpc_options ->> 'VPCId') || '.'
  end reason
  
  , d.region, d.account_id
from
  aws_opensearch_domain as d
  left join opensearch_domain_with_public_subnet as p on d.arn = p.arn;