PostgreSQL如何查询jsonb数组字段中指定步骤均为成功的记录

你使用的是PostgreSQL数据库，orders表结构如下：

• id：主键
• name：业务名称
• steps：存储步骤信息的字段，根据你的描述可能为jsonb（存储JSON数组）或jsonb[]（PostgreSQL原生数组，每个元素为jsonb）类型

你需要筛选Step1、Step2、Step3三个步骤的status均为success的记录，可根据你的字段类型选择对应SQL：

情况1：steps为存储JSON数组的jsonb类型 #

方案1：使用包含运算符（推荐，性能最优） #

该写法简洁，若steps字段建立了GIN索引可直接走索引，查询效率高：

SELECT * FROM orders
WHERE
  steps @> '[{"step_name":"Step1","status":"success"}]'
  AND steps @> '[{"step_name":"Step2","status":"success"}]'
  AND steps @> '[{"step_name":"Step3","status":"success"}]';

方案2：拆分数组聚合查询（扩展性强） #

如果后续需要增加额外校验逻辑（比如校验步骤时间先后、执行顺序），可使用该方案：

SELECT o.id, o.name, o.steps
FROM orders o,
     jsonb_array_elements(o.steps) step
WHERE step->>'step_name' IN ('Step1', 'Step2', 'Step3')
GROUP BY o.id, o.name, o.steps
HAVING COUNT(*) = 3 -- 确保三个步骤都存在
   AND BOOL_AND(step->>'status' = 'success'); -- 确保三个步骤状态均为成功

情况2：steps为PostgreSQL原生jsonb[]数组类型 #

直接使用ANY匹配数组元素即可：

SELECT * FROM orders
WHERE
  '{"step_name":"Step1","status":"success"}'::jsonb = ANY(steps)
  AND '{"step_name":"Step2","status":"success"}'::jsonb = ANY(steps)
  AND '{"step_name":"Step3","status":"success"}'::jsonb = ANY(steps);

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