Postgres环境下jsonb对象多条件查询返回0行问题咨询

问题原因 #

你使用jsonb_array_elements对experience字段的JSON数组做行转列处理后，每一行仅对应数组里的单个经验条目，不存在任何一行数据的e字段能同时等于devops和backend dev两个值，所以WHERE子句里的并列AND条件永远为假，自然返回0行。

正确查询方案 #

方案1：分组聚合校验（通用写法） #

先展开数组，再按用户唯一标识分组，通过条件聚合校验两个领域的经验要求同时满足：

WITH parsed_exp AS (
  SELECT 
    "firstName",
    "lastName",
    experience,
    jsonb_array_elements(experience) AS e 
  FROM users
)
SELECT "firstName", "lastName", experience
FROM parsed_exp
GROUP BY "firstName", "lastName", experience
HAVING 
  -- 存在符合要求的devops经验
  COUNT(*) FILTER (WHERE (e->>'field') = 'devops' AND (e->>'years')::INTEGER > 5) >= 1
  AND
  -- 存在符合要求的backend dev经验
  COUNT(*) FILTER (WHERE (e->>'field') = 'backend dev' AND (e->>'years')::INTEGER > 5) >= 1

方案2：JSONB路径查询（高性能写法） #

直接用PostgreSQL原生的jsonb路径查询能力，无需展开数组，执行效率更高，适配20万行级别的数据集查询：

SELECT "firstName", "lastName", experience
FROM users
WHERE
  jsonb_path_exists(experience, '$[*] ? (@.field == "devops" && @.years > 5)')
  AND
  jsonb_path_exists(experience, '$[*] ? (@.field == "backend dev" && @.years > 5)')

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