MongoDB聚合查询：找出3天内过期且库存剩余>0的产品

我需要找出3天内即将过期且库存剩余（remaining）大于0的产品，目前使用的MongoDB聚合管道如下：

db.products.aggregate([
  {
    $lookup: {
      from: "stocks",
      let: {
        stocks: "$stocks"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $in: [
                "$_id",
                "$$stocks"
              ]
            }
          }
        },
        {
          $project: {
            _id: 0,
            remaining: 1,
            expireDate: 1
          }
        }
      ],
      as: "stock"
    }
  },
  {
    $set: {
      remaining: "$stock.remaining",
      expireDate: "$stock.expireDate"
    }
  },
  {
    $match: {
      expireDate: {
        $lt: ISODate("2020-12-06")
      },
      remaining: {
        $gt: 0
      }
    }
  }
])

查询依赖的数据集（expireDate字段存储在stocks集合） #

db={
  products: [
    {
      "_id": ObjectId("63515ad7d5f84ecbaac38f1e"),
      "code": "1",
      "name": "Coca-Cola",
      "pricePerItem": NumberInt(2),
      "stocks": [
        ObjectId("63515ad7d5f84ecbaac38f20"),
        ObjectId("63515b04d5f84ecbaac38f2e")
      ]
    },
    {
      "_id": ObjectId("63515aeed5f84ecbaac38f24"),
      "code": "2",
      "name": "Fanta",
      "pricePerItem": NumberInt(4),
      "stocks": [
        ObjectId("63515aeed5f84ecbaac38f26")
      ]
    }
  ],
  stocks: [
    {
      "_id": ObjectId("63515ad7d5f84ecbaac38f20"),
      "count": NumberInt(10),
      "remaining": NumberInt(0),
      "costPerItem": NumberInt(1),
      "expireDate": ISODate("2020-12-05")
    },
    {
      "_id": ObjectId("63515aeed5f84ecbaac38f26"),
      "count": NumberInt(102),
      "remaining": NumberInt(10),
      "costPerItem": NumberInt(3),
      "expireDate": ISODate("2020-12-10")
    },
    {
      "_id": ObjectId("63515b04d5f84ecbaac38f2e"),
      "count": 1,
      "remaining": 1,
      "costPerItem": NumberInt(20),
      "expireDate": ISODate("2020-12-20")
    }
  ]
}

现有查询的问题 #

现有聚合管道存在逻辑错误：

1. $set阶段将stock数组中的remaining和expireDate提取为数组字段，后续$match阶段直接用数组与日期/数值比较，不符合预期筛选逻辑（比如数组形式的日期与单个日期比较时，MongoDB的判断规则并非筛选数组中符合条件的元素）。
2. 未在关联库存时先筛选符合条件的记录，导致后续处理冗余数据。

修正后的聚合管道 #

db.products.aggregate([
  {
    $lookup: {
      from: "stocks",
      let: { stockIds: "$stocks" },
      pipeline: [
        {
          $match: {
            $expr: { $in: ["$_id", "$$stockIds"] },
            remaining: { $gt: 0 },
            expireDate: { $lt: ISODate("2020-12-06") }
          }
        },
        { $project: { _id: 0, remaining: 1, expireDate: 1 } }
      ],
      as: "qualified_stocks"
    }
  },
  {
    $match: {
      qualified_stocks: { $ne: [] }
    }
  }
])

修正说明 #

1. 在$lookup的子管道中直接筛选出剩余库存>0且过期日期在目标范围内的库存记录，减少后续处理的数据量。
2. 通过$match: { qualified_stocks: { $ne: [] } }过滤掉没有符合条件库存的产品。
3. 保留符合条件的库存明细在qualified_stocks字段中，方便查看具体库存信息。

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