如何在PostgreSQL的jsonb列中按特定值条件查询对象

原查询失败原因 #

你之前的查询用@> '{"type":"Unknown"}'无法匹配，是因为@>操作符仅检查顶层JSON对象是否包含指定键值对，但你的type字段位于第二层嵌套对象中，顶层对象的键是Type A/Type B这类，所以无法匹配到目标数据。

正确查询方法 #

针对嵌套对象结构，需要遍历顶层对象的所有子值，筛选符合type="Unknown"且time>100的记录，以下两种方法均可实现：

方法1：使用jsonb_object_values+EXISTS子查询（兼容PostgreSQL 9.4+） #

SELECT p.*
FROM process p
WHERE EXISTS (
  SELECT 1
  FROM jsonb_object_values(p.process_stat_json) AS sub_obj
  WHERE sub_obj->>'type' = 'Unknown'
    AND (sub_obj->>'time')::integer > 100
);

逻辑说明：

• jsonb_object_values(p.process_stat_json)将顶层对象的所有子值（即每个Type X对应的嵌套对象）提取为行集
• 子查询检查是否存在满足条件的子对象，存在则返回主表对应的记录

方法2：使用JSON路径查询（PostgreSQL 12+支持） #

SELECT p.*
FROM process p
WHERE p.process_stat_json @? '$.* ? (@.type == "Unknown" && @.time > 100)';

逻辑说明：

• $.*表示匹配顶层对象的所有子对象
• ? (@.type == "Unknown" && @.time > 100)筛选出type为Unknown且time大于100的子对象
• @?操作符判断是否存在符合路径条件的元素

验证结果 #

针对你提供的测试数据，上述两种查询都会返回id=1的记录，符合预期需求。

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