Sequelize JSON对象搜索大小写敏感问题及转小写匹配方法咨询

要解决Sequelize中JSON嵌套字段（如customer.firstname）的大小写敏感搜索问题，核心思路是统一字段值与搜索关键词的大小写，或使用数据库原生的大小写不敏感匹配能力，以下是几种可行方案：

方案1：使用LOWER()函数统一转换大小写 #

通过Sequelize的fn和col方法，将JSON字段的值转为小写，同时把搜索关键词也转成小写，确保匹配时大小写一致。

PostgreSQL 版本 #

PostgreSQL支持->>操作符直接提取JSON字段的文本值，写法更简洁：

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

const searchKeyword = 'janice';
const data = await sequelize.models.orders.findAll({
  where: {
    [Op.substring]: [
      fn('LOWER', col('customer->>\'firstname\'')),
      searchKeyword.toLowerCase()
    ]
  }
});

MySQL 版本 #

MySQL需要用JSON_UNQUOTE和JSON_EXTRACT组合提取JSON字段值：

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

const searchKeyword = 'janice';
const data = await sequelize.models.orders.findAll({
  where: {
    [Op.substring]: [
      fn('LOWER', fn('JSON_UNQUOTE', fn('JSON_EXTRACT', col('customer'), '$.firstname'))),
      searchKeyword.toLowerCase()
    ]
  }
});

方案2：使用数据库原生大小写不敏感匹配操作符 #

如果你的数据库支持（如PostgreSQL的ILIKE），可以直接用Sequelize提供的Op.iLike操作符，无需手动转换大小写：

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

const searchKeyword = 'janice';
const data = await sequelize.models.orders.findAll({
  where: {
    ['customer.firstname']: {
      [Op.iLike]: `%${searchKeyword}%`
    }
  }
});

注意：Op.iLike仅适用于PostgreSQL；MySQL可以通过设置字段的排序规则为utf8mb4_general_ci（默认不区分大小写），直接用Op.like即可实现不敏感匹配。

方案3：使用Sequelize的where函数表达式 #

部分Sequelize版本支持直接对JSON嵌套字段调用函数，写法更直观：

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

const searchKeyword = 'janice';
const data = await sequelize.models.orders.findAll({
  where: fn('LOWER', col('customer.firstname')), {
    [Op.substring]: searchKeyword.toLowerCase()
  }
});

验证效果 #

无论使用哪种方案，执行后搜索'Janice'或'janice'都会返回一致的结果，解决大小写敏感问题。

内容的提问来源于stack exchange，提问作者Mahir Altınkaya