Query: vpc_routes_for_vpc

select
  route_table_id as "Route Table ID",
  tags ->> 'Name' as "Name",
  r ->> 'State' as "State",
  case
    when r ->> 'GatewayId' is not null then r ->> 'GatewayId'
    when r ->> 'InstanceId' is not null then r ->> 'InstanceId'
    when r ->> 'NatGatewayId' is not null then r ->> 'NatGatewayId'
    when r ->> 'LocalGatewayId' is not null then r ->> 'LocalGatewayId'
    when r ->> 'CarrierGatewayId' is not null then r ->> 'CarrierGatewayId'
    when r ->> 'TransitGatewayId' is not null then r ->> 'TransitGatewayId'
    when r ->> 'VpcPeeringConnectionId' is not null then r ->> 'VpcPeeringConnectionId'
    when r ->> 'DestinationPrefixListId' is not null then r ->> 'DestinationPrefixListId'
    when r ->> 'DestinationIpv6CidrBlock' is not null then r ->> 'DestinationIpv6CidrBlock'
    when r ->> 'EgressOnlyInternetGatewayId' is not null then r ->> 'EgressOnlyInternetGatewayId'
    when r ->> 'NetworkInterfaceId' is not null then r ->> 'NetworkInterfaceId'
    when r ->> 'CoreNetworkArn' is not null then r ->> 'CoreNetworkArn'
    when r ->> 'InstanceOwnerId' is not null then r ->> 'InstanceOwnerId'
  end as "Gateway",
  r ->> 'DestinationCidrBlock' as "Destination CIDR",
  case
    when a ->> 'Main' = 'true' then vpc_id
    when a ->> 'SubnetId' is not null then  a->> 'SubnetId'
    else '??'
  end as "Associated To"
from
  aws_vpc_route_table,
  jsonb_array_elements(routes) as r,
  jsonb_array_elements(associations) as a
where
  vpc_id = $1
order by
  route_table_id,
  "Associated To"