MongoDB聚合$and嵌套$or查询与日期差排序问题求解

你的聚合运行失败和$and操作符本身没有关系，是两处写法错误导致的：

• 匹配逻辑拆分错误：你把不同城市的visit_date规则、payment_cleared_at规则拆成了两个独立的$or判断块，这种写法会匹配到「城市是墨尔本、visit_date符合墨尔本时间窗，但payment_cleared_at符合珀斯时间窗」这类不符合预期的脏数据，本质是没有把同一城市的两个日期范围条件绑定到同一条匹配规则上。
• 投影阶段丢字段：$project阶段你只返回了计算出的time_diff，后续$sort依赖的visit_date字段被过滤掉了，排序时找不到对应字段自然无法正常执行。
• 额外提醒：如果集合里的两个日期字段存的是字符串而非MongoDB原生Date类型，范围匹配、差值计算都会出现精度错误，建议先把字段转为Date类型再做时间相关运算。

把同城市的两个日期条件合并到同一个$or分支，同时在投影阶段保留排序需要的所有字段即可：

db.collection.aggregate([
  {
    $match: {
      $or: [
        // 墨尔本的两个日期范围条件绑定到同一条规则
        {
          city_town: "Melbourne",
          visit_date: {
            $gte: ISODate("2022-06-22T14:00:00.000Z"),
            $lte: ISODate("2022-06-23T13:59:59.999Z")
          },
          payment_cleared_at: {
            $gte: ISODate("2022-06-14T14:00:00.000Z"),
            $lte: ISODate("2022-06-15T13:59:59.999Z")
          }
        },
        // 珀斯的两个日期范围条件绑定到同一条规则
        {
          city_town: "Perth",
          visit_date: {
            $gte: ISODate("2022-06-22T14:30:00.000Z"),
            $lte: ISODate("2022-06-23T14:29:59.999Z")
          },
          payment_cleared_at: {
            $gte: ISODate("2022-06-14T14:30:00.000Z"),
            $lte: ISODate("2022-06-15T14:29:59.999Z")
          }
        }
      ]
    }
  },
  {
    $project: {
      // 保留排序和结果需要的字段，不要遗漏visit_date
      city_town: 1,
      visit_date: 1,
      payment_cleared_at: 1,
      time_diff: {
        $subtract: ["$visit_date", "$payment_cleared_at"]
      }
    }
  },
  {
    // 排序字段已全部保留，可正常执行排序
    $sort: {
      visit_date: 1,
      time_diff: -1
    }
  },
  {
    $skip: 10
  },
  {
    $limit: 10
  }
])

• 所有时间条件值统一用ISODate()包裹，避免字符串日期按字典序匹配导致的结果错误。
• 百万级以上数据量场景下，建议创建{city_town: 1, visit_date: 1, payment_cleared_at: 1}的复合索引，$match阶段可以直接走索引完成过滤，查询速度能提升数倍。
• 如果业务不需要返回原始payment_cleared_at字段，可以在$project阶段将该字段设为0隐藏，减少结果集传输大小。

内容的提问来源于stack exchange，提问作者Rifat Bin Reza