使用MongoDB $or操作符实现ratingsAverage区间过滤失败排查

问题场景 #

数据库中ratingsAverage字段为Number类型，需实现评分过滤功能：用户选择某个评分值（如4）时，返回该值到值+0.9区间内的结果。尝试用$or操作符实现逻辑，但数据库无数据返回，且执行case "ratingsAverage"分支时抛出CastError。

错误信息 #

CastError: Cast to number failed for value "[ { ratingsAverage: { '$gte': 4, '$lt': 5 } } ]" (type Array) at path "ratingsAverage"

stringValue: "[ { ratingsAverage: { '$gte': 4, '$lt': 5 } } ]",
messageFormat: undefined,
kind: 'number',
value: [ { ratingsAverage: [Object] } ],
path: 'ratingsAverage',
reason: null,
valueType: 'Array'

相关代码 #

function destructSearchParams(searchParams) {
    let sortBy;

    if (searchParams.hasOwnProperty("sortBy")) {
      sortBy = searchParams.sortBy;
      delete searchParams.sortBy;
    }
  
    const filter = {};
    for (const [key, value] of Object.entries(searchParams)) {
      const valuesArray = value.split(',');
      let query;
      
      switch (key) {
        case "roaster":
          query = { $in: valuesArray};
          break;

        case "ratingsAverage":
          const ratings = valuesArray.map(value => {
            const minRating = Math.floor(Number(value));
            const maxRating = minRating + 1;
            return {ratingsAverage: { $gte: minRating, $lt: maxRating }};
          });
            query = {$or: ratings}
            console.log(query);
          break;

        default:
          query = { $regex: valuesArray.join('|')};  // Join with '|' to match any
          break;
      }
        
      filter[key] = query;
    }

    return {filter, sortBy};
}

问题原因 #

1. 查询结构错误：在ratingsAverage分支中，你把$or对象赋值给了filter["ratingsAverage"]，最终生成的查询是{ ratingsAverage: { $or: [ { ratingsAverage: { $gte:4, $lt:5 } } ] } }。MongoDB会把这个$or数组当作ratingsAverage字段的取值去匹配，但该字段是Number类型，直接触发类型转换错误。
2. 逻辑冗余嵌套：每个$or元素里又重复嵌套了ratingsAverage条件，导致查询逻辑完全偏离预期，无法正确匹配区间。

解决方案 #

方案1：支持多条件组合的通用写法 #

将$or条件直接放到filter的根节点，同时兼容其他过滤条件的组合：

function destructSearchParams(searchParams) {
    let sortBy;
    const filter = {};
    const orConditions = [];

    if (searchParams.hasOwnProperty("sortBy")) {
      sortBy = searchParams.sortBy;
      delete searchParams.sortBy;
    }
  
    for (const [key, value] of Object.entries(searchParams)) {
      const valuesArray = value.split(',');
      
      switch (key) {
        case "roaster":
          filter[key] = { $in: valuesArray};
          break;

        case "ratingsAverage":
          valuesArray.forEach(val => {
            const minRating = Math.floor(Number(val));
            orConditions.push({ ratingsAverage: { $gte: minRating, $lt: minRating + 1 } });
          });
          break;

        default:
          filter[key] = { $regex: valuesArray.join('|')};
          break;
      }
    }

    // 合并or条件到filter
    if (orConditions.length > 0) {
      if (Object.keys(filter).length > 0) {
        // 已有其他条件，用$and包裹原条件和or条件
        filter.$and = [filter, { $or: orConditions }];
        // 移除原filter中的非$and字段，避免重复
        Object.keys(filter).forEach(k => k !== '$and' && delete filter[k]);
      } else {
        filter.$or = orConditions;
      }
    }

    return {filter, sortBy};
}

方案2：简化单/多值场景写法 #

针对单个或多个评分值的场景，直接生成正确的查询结构：

function destructSearchParams(searchParams) {
    let sortBy;
    const filter = {};

    if (searchParams.hasOwnProperty("sortBy")) {
      sortBy = searchParams.sortBy;
      delete searchParams.sortBy;
    }
  
    for (const [key, value] of Object.entries(searchParams)) {
      const valuesArray = value.split(',');
      
      if (key === "roaster") {
        filter[key] = { $in: valuesArray};
      } else if (key === "ratingsAverage") {
        if (valuesArray.length === 1) {
          // 单个评分值，直接生成区间条件
          const minRating = Math.floor(Number(valuesArray[0]));
          filter.ratingsAverage = { $gte: minRating, $lt: minRating + 1 };
        } else {
          // 多个评分值，生成$or数组
          filter.$or = valuesArray.map(val => {
            const minRating = Math.floor(Number(val));
            return { ratingsAverage: { $gte: minRating, $lt: minRating + 1 } };
          });
        }
      } else {
        filter[key] = { $regex: valuesArray.join('|')};
      }
    }

    return {filter, sortBy};
}

正确查询结构示例 #

• 单评分值（如4）：{ ratingsAverage: { $gte:4, $lt:5 } }
• 多评分值（如4,5）：{ $or: [ { ratingsAverage: { $gte:4, $lt:5 } }, { ratingsAverage: { $gte:5, $lt:6 } } ] }
• 结合其他条件（如roaster=xxx）：{ roaster: { $in: ["xxx"] }, $or: [ { ratingsAverage: { $gte:4, $lt:5 } } ] }

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