如何用sequelize.fn在PostgreSQL中实现无序多值匹配查询？

现有PostgreSQL表结构及数据如下：

id name variants
0  foo one
1  bar two one
2  foobar one two

需要根据传入的variants数组（如['one', 'two']），查询表中variants列**包含所有指定值（顺序无关）**的行，具体需求：

• 仅指定'one'时，返回所有行；
• 仅指定'two'时，返回id为1、2的行；
• 同时指定'one'和'two'时，返回id为1、2的行。

当前使用Sequelize的写法是将数组join成字符串做模糊匹配，但无法满足无序匹配需求：

model = await Item.findAndCountAll(
    {
        where: {name: sequelize.where(sequelize.fn('LOWER', sequelize.col('variants')), 'LIKE', '%' + variants + '%')}
    })

解决方案 #

方法1：利用PostgreSQL字符串转数组+包含检查 #

PostgreSQL可以将variants列按空格拆分为数组，再用数组包含操作符@>判断是否覆盖所有指定值，直接用Sequelize的literal实现：

const targetVariants = ['one', 'two']; // 传入的参数数组

model = await Item.findAndCountAll({
  where: sequelize.literal(`string_to_array(variants, ' ') @> ARRAY[${targetVariants.map(v => `'${v}'`).join(',')}]::text[]`)
});

原理：string_to_array(variants, ' ')把字符串按空格转成数组，ARRAY[...]将参数转为PostgreSQL数组，@>操作符会判断左侧数组是否包含右侧数组的所有元素（顺序不影响）。

方法2：正则匹配完整单词（适合少量参数） #

用PostgreSQL的正则匹配操作符~*，匹配完整的目标值，避免部分匹配（比如one匹配ones），再用AND连接所有条件：

const { Op } = require('sequelize');
const targetVariants = ['one', 'two'];

// 生成每个值的正则匹配条件：\m匹配单词开头，\M匹配单词结尾
const regexConditions = targetVariants.map(v => 
  sequelize.where(sequelize.col('variants'), '~*', `\\m${v}\\M`)
);

model = await Item.findAndCountAll({
  where: {
    [Op.and]: regexConditions
  }
});

方法3：优化表结构（长期高效方案） #

如果该查询是高频操作，建议直接把variants列改为PostgreSQL的text[]数组类型，既符合数据结构，又能提升查询效率：

1. 修改表结构：

ALTER TABLE items ALTER COLUMN variants TYPE text[] USING string_to_array(variants, ' ');

2. 新增GIN索引提升查询性能：

CREATE INDEX idx_items_variants ON items USING GIN(variants);

3. Sequelize查询写法更简洁：

const { Op } = require('sequelize');
const targetVariants = ['one', 'two'];

model = await Item.findAndCountAll({
  where: {
    variants: {
      [Op.contains]: targetVariants
    }
  }
});

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