PostgreSQL中无需拆分JSONB数组的多条件过滤方法咨询

我有一张包含JSONB类型字段（字段名attributes）的表，该字段结构如下：

[
  {
    "Id": "98f2a810-8f29-4707-9098-759eec83b2d5",
    "Data": {
      "value": 6
    },
    "Created": "2022-12-15T10:04:04.8100461Z"
  },
  {
    "Id": "950df931-abfd-460f-916f-14b00d3e1593",
    "Data": {
      "value": "hello world"
    },
    "Created": "2022-12-15T10:04:04.9270694Z"
  }
]

目前我通过拆分JSONB数组为单行并关联的方式过滤：

LEFT JOIN LATERAL jsonb_array_elements("attributes") as x(attribute) ON TRUE

过滤条件为：

WHERE x.attribute ->> 'Id' ILIKE '98f2a810-8f29-4707-9098-759eec83b2d5' 
  AND cast(x.attribute -> 'Data' ->> 'value' as numeric) = 6

但当需要用AND同时过滤两个不同的数组元素时，这种拆分关联的方式会导致条件相互排斥，无法得到正确结果。请问如何无需拆分并关联元素即可过滤JSONB数组？

方法1：用jsonb_path_exists直接检查（PostgreSQL 12及以上可用） #

这个函数能直接在JSONB数组内验证条件，无需拆分数组，完美解决多条件排斥问题。

场景1：筛选存在单个元素同时满足多条件 #

如果要找数组里某个元素同时符合Id匹配且Data.value=6，可以这么写：

SELECT *
FROM your_table
WHERE jsonb_path_exists(
  attributes,
  '$[*] ? (@.Id == "98f2a810-8f29-4707-9098-759eec83b2d5" && @.Data.value == 6)'
);

场景2：筛选数组同时包含两个不同条件的元素 #

如果要找数组里既有符合条件A的元素，又有符合条件B的元素，可以多次调用jsonb_path_exists：

SELECT *
FROM your_table
WHERE 
  jsonb_path_exists(attributes, '$[*] ? (@.Id == "98f2a810-8f29-4707-9098-759eec83b2d5" && @.Data.value == 6)')
  AND jsonb_path_exists(attributes, '$[*] ? (@.Id == "950df931-abfd-460f-916f-14b00d3e1593" && @.Data.value == "hello world")');

方法2：聚合函数配合拆分（兼容低版本PostgreSQL） #

如果你的PostgreSQL版本低于12，没法用JSON路径函数，可以拆分后通过聚合统计规避条件排斥：

SELECT t.*
FROM your_table t
LEFT JOIN LATERAL jsonb_array_elements(t.attributes) as x(attribute) ON TRUE
GROUP BY t.id -- 替换成你的表主键或唯一标识字段
HAVING 
  -- 统计符合第一个条件的元素数量，至少有1个
  COUNT(CASE WHEN x.attribute ->> 'Id' ILIKE '98f2a810-8f29-4707-9098-759eec83b2d5' AND cast(x.attribute -> 'Data' ->> 'value' as numeric) = 6 THEN 1 END) >= 1
  -- 统计符合第二个条件的元素数量，至少有1个
  AND COUNT(CASE WHEN x.attribute ->> 'Id' ILIKE '950df931-abfd-460f-916f-14b00d3e1593' AND x.attribute -> 'Data' ->> 'value' = 'hello world' THEN 1 END) >= 1;

这种方式通过分组后统计每个条件的匹配次数，确保两个条件都有对应的元素存在，不会出现拆分后行级条件互相排斥的问题。

方法3：@>操作符（精确匹配元素场景） #

如果你的过滤条件是精确匹配某个元素的指定键值对，可以用@>操作符直接判断数组是否包含该元素，性能较好：

SELECT *
FROM your_table
WHERE attributes @> '[
  {"Id": "98f2a810-8f29-4707-9098-759eec83b2d5", "Data": {"value": 6}}
]'::jsonb;

注意：@>会检查数组是否包含完全匹配的元素，如果只需要匹配部分键值，还是用jsonb_path_exists更灵活。

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