MongoDB聚合：如何筛选文档SKUPromo数组中日期重叠的对象

需求说明 #

处理MongoDB文档，筛选SKUPromo数组中的对象，仅保留与数组内至少一个其他对象存在日期重叠的项。

输入文档示例 #

{
  "_id": "2531090",
  "SKUPromo": [
    {
      "_id": "1",
      "startDateTime": {
        "$date": "2023-01-05T06:00:00.000Z"
      },
      "endDateTime": {
        "$date": "2023-02-07T05:59:59.000Z"
      },
      "promotionID": "6262",
      "skuId": "2531090"
    },
    {
      "_id": "2",
      "startDateTime": {
        "$date": "2023-06-25T05:00:00.000Z"
      },
      "endDateTime": {
        "$date": "2023-07-16T04:59:59.000Z"
      },
      "promotionID": "10146",
      "skuId": "2531090"
    },
    {
      "_id": "3",
      "startDateTime": {
        "$date": "2023-06-25T05:00:00.000Z"
      },
      "endDateTime": {
        "$date": "2023-07-02T04:59:59.000Z"
      },
      "promotionID": "8749",
      "skuId": "2531090"
    },
    {
      "_id": "4",
      "startDateTime": {
        "$date": "2023-06-04T05:00:00.000Z"
      },
      "endDateTime": {
        "$date": "2023-06-11T04:59:59.000Z"
      },
      "promotionID": "10024",
      "skuId": "2531090"
    },
    {
      "_id": "5",
      "startDateTime": {
        "$date": "2023-01-01T06:00:00.000Z"
      },
      "endDateTime": {
        "$date": "2023-01-07T05:59:59.000Z"
      },
      "promotionID": "6262",
      "skuId": "2531090"
    }
  ]
}

预期输出文档示例 #

{
  "_id": "2531090",
  "SKUPromo": [
    {
      "_id": "1",
      "startDateTime": {
        "$date": "2023-01-05T06:00:00.000Z"
      },
      "endDateTime": {
        "$date": "2023-02-07T05:59:59.000Z"
      },
      "promotionID": "9345",
      "skuId": "2531090"
    },
    {
      "_id": "2",
      "startDateTime": {
        "$date": "2023-06-25T05:00:00.000Z"
      },
      "endDateTime": {
        "$date": "2023-07-16T04:59:59.000Z"
      },
      "promotionID": "10146",
      "skuId": "2531090"
    },
    {
      "_id": "3",
      "startDateTime": {
        "$date": "2023-06-25T05:00:00.000Z"
      },
      "endDateTime": {
        "$date": "2023-07-02T04:59:59.000Z"
      },
      "promotionID": "8749",
      "skuId": "2531090"
    },
    {
      "_id": "5",
      "startDateTime": {
        "$date": "2023-01-01T06:00:00.000Z"
      },
      "endDateTime": {
        "$date": "2023-01-07T05:59:59.000Z"
      },
      "promotionID": "6262",
      "skuId": "2531090"
    }
  ]
}

解决方案 #

使用MongoDB聚合管道实现，核心逻辑是展开数组后对比每个对象与原数组的日期重叠情况，再筛选保留符合条件的项，具体代码如下：

db.collection.aggregate([
  {
    $addFields: {
      originalSKUPromo: "$SKUPromo"
    }
  },
  {
    $unwind: "$SKUPromo"
  },
  {
    $addFields: {
      hasOverlap: {
        $gt: [
          {
            $size: {
              $filter: {
                input: "$originalSKUPromo",
                cond: {
                  $and: [
                    { $ne: ["$$this._id", "$SKUPromo._id"] },
                    { $lte: ["$SKUPromo.startDateTime", "$$this.endDateTime"] },
                    { $gte: ["$SKUPromo.endDateTime", "$$this.startDateTime"] }
                  ]
                }
              }
            }
          },
          0
        ]
      }
    }
  },
  {
    $match: {
      hasOverlap: true
    }
  },
  {
    $group: {
      _id: "$_id",
      SKUPromo: { $push: "$SKUPromo" }
    }
  }
])

日期重叠判断逻辑 #

两个时间段[s1, e1]和[s2, e2]存在重叠的条件为：s1 <= e2 且 e1 >= s2，该逻辑可覆盖包含、部分重叠、完全重合等所有重叠场景。

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