需要重叠运算符：

SELECT *
FROM t
WHERE regexp_split_to_array(t.type, '@##@')::text[] && array['red', 'blue'] and
      regexp_split_to_array(t.ip, '@##@')::inet[] && array['1.1.1.1', '2.2.2.2']

匹配 type 这样，一个，另一个，或者两者都可以通过包含操作符来完成，因为基本的比较是相等的。
匹配 inet 类型到子网是另一回事。必须使用
inet && 运算符（contains或is contained by），因此 ip 数组必须由 unnest . 再次匹配一个、另一个或两者的要求意味着我们需要 ip 仅当匹配项的计数等于 ip 价值观。
此查询似乎可以完成此任务。在这儿摆弄。

with asarrays as (
  SELECT row, regexp_split_to_array(t.type, '@##@')::text[] as types,
         unnest(regexp_split_to_array(t.ip, '@##@')::inet[]) as ip
    FROM t
), typematch as (
  select *, count(*) over (partition by row) as totcount
    from asarrays 
   where types <@ array['red', 'blue']
), ipmatch as (  
  select *, count(*) over (partition by row) as matchcount
    from typematch
   where ip && any(array['1.1.1.1'::inet, '2.2.2.2'::inet])
)
select row, types, array_agg(ip) as ip 
  from ipmatch
 where matchcount = totcount
 group by row, types;