PostgreSQL合并JSON中的IP数组并查询匹配CIDR或等值IP的方法

实现方案 #

你可以通过array_agg聚合函数将拆分行的IP/CIDR转为inet[]数组，完整查询语句如下：

SELECT ip 
FROM ips 
WHERE ip << ANY (
    SELECT array_agg(ip_asset::inet)
    FROM scopes,
         -- 直接合并两个JSON数组后拆分出纯文本，避免处理JSON元素的引号问题
         jsonb_array_elements_text(
             (scopes.definition -> 'ips' -> 'ranges') || 
             (scopes.definition -> 'ips' -> 'addresses')
         ) AS ip_asset
    -- 如果需要指定查询某一条scopes记录的规则，可在这里加过滤条件，例如
    -- WHERE scopes.id = 1
);

逻辑说明 #

1. 用jsonb_array_elements_text替代原来的jsonb_array_elements，直接返回不带双引号的IP/CIDR纯文本，无需额外处理转义字符
2. 合并ranges和addresses两个JSON数组的操作直接在jsonb类型层面完成，无需多次类型转换，性能更好
3. array_agg(ip_asset::inet)会把所有文本格式的IP/CIDR转为inet类型后，聚合成符合要求的inet[]数组
4. 外层用PostgreSQL原生的<<操作符判断IP是否属于数组中任意一个网段/地址，单个IP的inet类型会自动补全掩码（IPv4补/32，IPv6补/128），判断完全准确。

多scope匹配场景 #

如果需要同时匹配多条scopes记录的规则，并且需要关联对应scope的ID，可以用LATERAL子查询实现：

SELECT s.id AS scope_id, i.ip
FROM scopes s
CROSS JOIN LATERAL (
    SELECT array_agg(ip_asset::inet) AS allowed_ips
    FROM jsonb_array_elements_text(
        (s.definition -> 'ips' -> 'ranges') || 
        (s.definition -> 'ips' -> 'addresses')
    ) AS ip_asset
) AS ip_list
JOIN ips i ON i.ip << ANY(ip_list.allowed_ips);

内容的提问来源于stack exchange，提问作者003random