Sequelize查询PostgreSQL jsonb对象数组列iLike无结果如何解决

问题背景 #

现有名为items的数据表，表结构与示例数据如下：

table name: items
slug     name        metadata
blue    round       [
                       {
                         "value": "sweet",
                         "type": "block",
                        }
                     ]

需求为实现多字段OR条件模糊查询，初始编写的Sequelize查询逻辑如下：

where: [{
  [Op.or]: [
     { slug: { [Op.iLike]: `%${value}%` } },
     { name: { [Op.iLike]: `%${value}%` } },
     { 'metadata.value': { [Op.iLike]: `%${value}%`}},
  ],
}]

实际执行生成的SQL片段为：

("items"."metadata"#>>'{value}') ILIKE '%sweet%')

语句运行无报错，但无法返回预期查询结果。

问题根因 #

metadata字段为JSON数组类型，根节点是数组而非对象，Sequelize默认的点路径（metadata.value）写法，只会读取JSON根节点下key为value的属性，不会遍历数组内的元素。该路径在数组结构下永远返回NULL，NULL与任意值做ILIKE匹配都不会命中，因此查不到结果。

正确实现方式 #

需要通过原生SQL片段遍历JSON数组的每个元素，判断是否存在元素的value字段满足模糊匹配规则，完整查询代码如下：

const { Op, literal } = require('sequelize');

// 对传入的查询关键词做单引号转义，避免SQL注入
const escapedValue = value.replace(/'/g, "''");

where: {
  [Op.or]: [
     { slug: { [Op.iLike]: `%${value}%` } },
     { name: { [Op.iLike]: `%${value}%` } },
     literal(`EXISTS (
       SELECT 1 
       FROM jsonb_array_elements(metadata) AS elem
       WHERE elem->>'value' ILIKE '%${escapedValue}%'
     )`)
  ],
}

适配说明 #

• 如果metadata是普通JSON类型而非JSONB类型，把代码中jsonb_array_elements替换为json_array_elements即可
• 该写法的逻辑是将metadata存储的JSON数组拆分为独立元素行，逐个校验元素的value属性，只要有一个元素匹配模糊规则，当前记录就会被命中

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