PostgreSQL中如何查询同时包含指定JSON元素的CI记录

表结构与数据 #

select * from ci_grp;

      ci    |                             grp
------------+-------------------------------------------------------------------------------------------
 Ci1        | [{"Name":"TEAM.Virtu"},{"Name":"TECH.LinuxDevice"}]
 Ci2        | [{"Name":"TECH.LinuxDevice"},{"Name":"TEAM.Monitoring"}]
 Ci3        | [{"Name":"TEAM.Noc"},{"Name":"TEAM.Virtu"},{"Name":"TECH.DellEsx"},{"Name":"TECH.SNMP"}]
 Ci4        | [{"Name":"TEAM.Monitoring"},{"Name":"TECH.Postgresql"}]
 Ci5        | [{"Name":"TEAM.Monitoring"},{"Name":"TECH.LinuxDevice"},{"Name":"TECH.ZabbixProxy"}]

需求 #

获取grp数组中同时包含{"Name":"TECH.LinuxDevice"}和{"Name":"TEAM.Monitoring"}的CI名称（即Ci2和Ci5）。

最优SQL方案 #

方案1：使用JSON包含操作符@>（性能最优）

如果grp字段是json或jsonb类型，直接用PostgreSQL内置的JSON包含操作符即可，无需展开数组：

SELECT ci
FROM ci_grp
WHERE grp @> '[{"Name":"TECH.LinuxDevice"},{"Name":"TEAM.Monitoring"}]'::json;

• 说明：@>操作符判断左侧JSON数组是否包含右侧所有指定的JSON对象，匹配逻辑精准且效率高。如果你的grp字段是jsonb类型，还可以创建GIN索引进一步提升查询性能：

  CREATE INDEX idx_ci_grp_jsonb ON ci_grp USING GIN (grp);
  

方案2：展开数组后分组统计（灵活适配复杂场景）

如果需要更复杂的匹配逻辑，可先展开JSON数组，再通过分组计数判断是否同时包含目标元素：

SELECT ci
FROM (
    SELECT ci, json_array_elements(grp)->>'Name' AS grp_name
    FROM ci_grp
) AS grp_elements
WHERE grp_name IN ('TECH.LinuxDevice', 'TEAM.Monitoring')
GROUP BY ci
HAVING COUNT(DISTINCT grp_name) = 2;

• 说明：先将每个CI对应的grp数组展开为单行记录，筛选出目标的两个分组名称；再按CI分组，统计不同分组名称的数量等于2，即可确认该CI同时包含两个目标元素。

原查询问题分析 #

你最初的查询将数组展开后，用AND同时匹配两个分组名称，而单行记录仅对应一个分组名称，因此无法满足条件导致无结果。分组统计的方式则通过聚合逻辑解决了这个问题，确保两个元素都存在于同一CI的数组中。

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