如何快速查询AWS EC2实例已分配的IP地址？附CloudQuery+PostgreSQL方案

问题背景 #

使用CloudQuery将AWS资源数据同步到PostgreSQL后，EC2实例的网络IP信息以JSON格式存储，需要通过SQL解析JSON来提取实例分配的IP地址（包含主私有IP及所有附加私有IP）。

查询SQL语句 #

with aws_account_config as (
  select '111111111111' as account_id, 'account_role_1' as account_name, 1 as account_order union
  select '222222222222' as account_id, 'account_role_2' as account_name, 2 as account_order union
  select '333333333333' as account_id, 'account_role_3' as account_name, 3 as account_order
),
instances_src as (
  select
    ec2i.*,
    i->>'NetworkInterfaceId' as NetworkInterfaceId,
    i->>'PrivateIpAddress' as PrivateIpAddressMain,
    pips->'PrivateIpAddress' as PrivateIpAddress
  from aws_ec2_instances ec2i,
  LATERAL jsonb_array_elements(network_interfaces) as i
  LEFT JOIN LATERAL jsonb_array_elements(
    case when jsonb_typeof(i->'PrivateIpAddresses') <> 'array'
    then null
    else i->'PrivateIpAddresses'
    end
  )  as pips on true
)

select
  aac.account_name, isrc.instance_id, isrc.NetworkInterfaceId, isrc.PrivateIpAddressMain, isrc.PrivateIpAddress
from instances_src isrc
left join aws_account_config aac on aac.account_id = isrc.account_id
order by aac.account_order, isrc.instance_id

语句说明 #

• aws_account_config CTE：自定义账号映射表，把AWS账号ID转换为易读的账号名称，同时指定排序顺序，适配多账号场景的结果展示需求。
• instances_src CTE：解析aws_ec2_instances表的JSON字段：
  • 用jsonb_array_elements展开network_interfaces数组，提取每个网卡的ID和主私有IP；
  • 进一步展开网卡内的PrivateIpAddresses数组，获取所有附加私有IP；
  • 通过case处理非数组格式的异常数据，避免查询报错。
• 最终查询：关联账号映射表，输出账号名称、实例ID、网卡ID、主私有IP及所有私有IP，按账号顺序和实例ID排序。

内容的提问来源于stack exchange，提问作者Mario Mendez