PostgreSQL查询JSON数组内嵌套对象的WHERE条件模糊匹配方案

问题根因 #

你原有写法失效的核心原因是p.json->'activities'返回的是JSON数组，无法直接通过链式的->运算符访问数组内部嵌套对象的venue属性，必须先对数组做拆分解构处理。

实现方案（仅修改WHERE块逻辑，无其他侵入性改动） #

方案1：基于数组展开的EXISTS判断（兼容性好，逻辑直观） #

直接替换你WHERE块中第三个OR分支的代码即可，修改后的完整WHERE逻辑如下：

if (filter.search)
  query.append(sql`
  AND
    (
      p.plan_id::text ILIKE ${`${filter.search}%`}
      OR
      p.json->>'name' ILIKE ${`%${filter.search}%`}
      OR
      EXISTS (
        SELECT 1 FROM jsonb_array_elements(p.json->'activities') AS act
        WHERE act->'venue'->>'name' ILIKE ${`%${filter.search}%`}
      )
    )
`);

注意：如果你的p.json字段是json类型而非jsonb类型，将jsonb_array_elements替换为json_array_elements即可。

方案2：基于JSONPath的实现（PostgreSQL 12+支持，写法更简洁） #

你使用的PostgreSQL 13.4原生支持JSONPath语法，可以直接通过路径匹配实现查询，写法更精简：

OR jsonb_path_exists(
  p.json, 
  '$.activities[*].venue.name ? (@ like_regex $search flag "i")',
  jsonb_build_object('search', ${filter.search})
)

TypeORM实现参考 #

如果你使用TypeORM的QueryBuilder，可以用如下写法添加条件：

if (filter.search) {
  qb.andWhere(`
    (
      p.plan_id::text ILIKE :searchPrefix
      OR p.json->>'name' ILIKE :searchFuzzy
      OR EXISTS (
        SELECT 1 FROM jsonb_array_elements(p.json->'activities') AS act
        WHERE act->'venue'->>'name' ILIKE :searchFuzzy
      )
    )
  `, {
    searchPrefix: `${filter.search}%`,
    searchFuzzy: `%${filter.search}%`
  })
}

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