如何使用Mongoose实现多操作符多值的动态组合筛选查询

核心思路：通过操作符映射表解耦前端传参和MongoDB原生查询语法，遍历筛选数组动态生成查询条件，替代多分支硬编码判断，后续新增操作符或者字段只需要修改映射配置即可，维护成本更低。

第一步：定义字段与操作符映射配置 #

先把前端传的筛选字段、自定义操作符和MongoDB原生查询语法做一一映射，同时统一维护前端字段和数据库实际字段的对应关系：

// 操作符映射：key为筛选字段，value为该字段支持的操作符对应的Mongo查询生成函数
const operatorMap = {
  family: {
    contains: (val) => ({ $regex: val, $options: 'i' }),
    doesnt_contain: (val) => ({ $not: { $regex: val, $options: 'i' } }),
    starts_with: (val) => ({ $regex: `^${val}`, $options: 'i' }),
    ends_with: (val) => ({ $regex: `${val}$`, $options: 'i' }),
    is_empty: () => ({ $in: [null, ''] }),
    is_not_empty: () => ({ $nin: [null, ''] })
  },
  no_of_products: {
    '=': (val) => Number(val),
    '!=': (val) => ({ $ne: Number(val) }),
    '>': (val) => ({ $gt: Number(val) }),
    '>=': (val) => ({ $gte: Number(val) }),
    '<': (val) => ({ $lt: Number(val) }),
    '<=': (val) => ({ $lte: Number(val) })
  },
  state: {
    active: () => 'Active',
    inactive: () => 'Inactive'
  },
  no_of_attributes: {
    // 若三个统计维度为独立预存字段，直接替换为对应字段匹配逻辑即可
    total: () => ({ $size: '$attributes' }),
    mandatory: () => 'mandatory',
    optional: () => 'optional'
  },
  last_updated: {
    custom: (from, to) => ({ $gte: new Date(from), $lte: new Date(to) }),
    this_week: () => {
      const now = new Date();
      const weekStart = new Date(now.setDate(now.getDate() - (now.getDay() || 7) + 1));
      weekStart.setHours(0,0,0,0);
      return { $gte: weekStart };
    },
    last_week: () => {
      const now = new Date();
      const currWeekStart = new Date(now.setDate(now.getDate() - (now.getDay() || 7) + 1));
      currWeekStart.setHours(0,0,0,0);
      const lastWeekStart = new Date(currWeekStart.getTime() - 7*24*60*60*1000);
      return { $gte: lastWeekStart, $lt: currWeekStart };
    },
    last_2_week: () => {
      const now = new Date();
      const currWeekStart = new Date(now.setDate(now.getDate() - (now.getDay() || 7) + 1));
      currWeekStart.setHours(0,0,0,0);
      const twoWeekStart = new Date(currWeekStart.getTime() - 14*24*60*60*1000);
      return { $gte: twoWeekStart, $lt: currWeekStart };
    },
    this_month: () => {
      const now = new Date();
      return { $gte: new Date(now.getFullYear(), now.getMonth(), 1) };
    },
    last_month: () => {
      const now = new Date();
      const monthStart = new Date(now.getFullYear(), now.getMonth(), 1);
      const lastMonthStart = new Date(now.getFullYear(), now.getMonth()-1, 1);
      return { $gte: lastMonthStart, $lt: monthStart };
    },
    last_2_month: () => {
      const now = new Date();
      const monthStart = new Date(now.getFullYear(), now.getMonth(), 1);
      const twoMonthStart = new Date(now.getFullYear(), now.getMonth()-2, 1);
      return { $gte: twoMonthStart, $lt: monthStart };
    }
  }
}

// 字段映射：前端传入的filter_by对应数据库实际存储字段名
const fieldMap = {
  family: 'name',
  no_of_products: 'no_of_products',
  state: 'state',
  no_of_attributes: 'no_of_attributes',
  last_updated: 'updatedAt'
}

第二步：重构控制器查询逻辑 #

直接替换原有三个分支的判断逻辑，统一合并基础条件、搜索条件、筛选条件，减少重复代码：

// 初始化固定基础查询条件
const baseQuery = {
  client_id: client_id,
  status: { $ne: 'Deleted' }
};
const filterConditions = [];

// 处理关键词搜索
if (search) {
  filterConditions.push({ name: { $regex: search, $options: 'i' } });
}

// 处理多组合筛选
if (filter?.length) {
  for (const item of filter) {
    // 跳过空的无效筛选项
    if (!item.filter_by || !item.operator) continue;
    const dbField = fieldMap[item.filter_by];
    const operatorHandler = operatorMap[item.filter_by]?.[item.operator];
    // 非法字段/操作符直接跳过，和Joi校验形成双重防护
    if (!dbField || !operatorHandler) continue;

    const fieldQueryVal = operatorHandler(item.from_value, item.to_value);
    filterConditions.push({
      [dbField]: typeof fieldQueryVal === 'object' ? fieldQueryVal : fieldQueryVal
    });
  }
}

// 合并所有条件生成最终查询语句
const finalQuery = filterConditions.length 
  ? { $and: [baseQuery, ...filterConditions] }
  : baseQuery;

// 执行查询
dbFamilies = await Family.find(finalQuery)
  .populate([{path:'created_by'},{path:'updated_by'}])
  .sort("name ASC")
  .limit(limit)
  .skip(offset)
  .lean();

优化注意事项 #

• 数值类型字段（比如no_of_products）必须做类型转换，前端传入的from_value默认是字符串，直接查询会触发字符串匹配逻辑，导致大小比较结果错误
• 正则查询在数据量超过10w条时性能较差，建议给name这类字符串字段加文本索引，替换$regex为$text查询提升性能；前缀匹配（starts_with）场景加普通B树索引即可命中
• 时间范围计算默认使用服务端时区，如果业务需要匹配用户时区，需要先把前端传入的时间转为用户对应时区的时间对象再做范围切割
• 如果no_of_attributes的三个统计维度不是预存字段，需要关联属性表实时统计，把这部分筛选逻辑移到aggregate聚合管道中，通过$lookup关联属性表后做统计匹配即可
• 现有Joi校验规则把所有操作符放在同一个校验池里，建议后续优化为按filter_by校验对应支持的operator，从入参层拦截“给family字段传大于号”这类非法组合

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