如何在Sequelize与MySQL中对JSON列SELECT使用LOWER函数？避免原生查询

我完全理解你想避开原生SQL、用Sequelize内置能力实现JSON字段搜索的需求——这不仅能保持代码的可维护性，还能兼容不同数据库的语法差异。咱们基于你现有的代码思路，优化出一套优雅的实现方案：

完整代码实现 #

const { Op } = require('sequelize'); // 务必引入操作符，Sequelize v5+ 默认不全局暴露

// 初始基础条件
let where = { "foo": "bar" };
// 需要搜索的JSON字段键列表
const string_json_keys = ['title', 'description'];

// 遍历查询参数，构建JSON字段搜索条件
string_json_keys.forEach(key => {
  // 仅当查询参数存在时才添加条件
  if (params[key]) {
    // 1. 定位JSON字段的字符串值：用->>而非->，确保取出的是字符串（而非JSON类型）
    const jsonField = sequelizeInstance.col(`metadata->>$.${key}`);
    // 2. 统一转为小写，实现大小写不敏感匹配
    const lowerJsonValue = sequelizeInstance.fn('lower', jsonField);
    const lowerParamValue = params[key].toLowerCase().trim();

    // 选择匹配模式：精确匹配 OR 模糊匹配
    // --- 精确匹配 ---
    const jsonCondition = sequelizeInstance.where(lowerJsonValue, lowerParamValue);
    
    // --- 模糊匹配（包含关键词）---
    // const jsonCondition = sequelizeInstance.where(lowerJsonValue, {
    //   [Op.like]: `%${lowerParamValue}%`
    // });

    // 将新条件合并到基础where中，用AND确保所有条件同时生效
    where[Op.and] = where[Op.and] || [];
    where[Op.and].push(jsonCondition);
  }
});

// 执行查询
const results = await YourTargetModel.findAll({ where });

关键细节说明 #

• JSON字段取值语法：metadata->>$.${key} 是PostgreSQL的语法，能直接提取JSON字段的字符串值；如果是MySQL，需要调整为 metadata->>'$.${key}'（注意引号位置），或者用Sequelize的函数组合实现跨库兼容：

  const jsonField = sequelizeInstance.fn(
    'JSON_UNQUOTE',
    sequelizeInstance.fn('JSON_EXTRACT', sequelizeInstance.col('metadata'), `$.${key}`)
  );
  

• 条件合并逻辑：用Op.and把JSON搜索条件和初始的foo: bar条件组合，确保所有筛选规则同时生效；如果需要多个JSON字段满足任意一个即可，换成Op.or即可。
• 参数预处理：对查询参数做toLowerCase()和trim()，避免大小写不一致或前后空格导致的匹配失败，提升搜索的鲁棒性。

额外提示 #

如果你的JSON字段嵌套更深（比如metadata->$.author->name），只需要调整col方法里的路径即可，比如metadata->>$.author.name（PostgreSQL）或metadata->>'$.author.name'（MySQL），Sequelize会自动适配数据库语法。

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