javascript - MongoDB 填充聚合管道分页中的缺失日期

Question

我有这个管道:

    let pipeline = [

      {
        $group: {
          _id: "$date",
          tasks: { $push: "$$ROOT" },
        },
      },
      {
        $sort: { _id: -1 },
      },
      { 
        $skip: skip //4,8,12,16...etc
      },
      { 
        $limit: 4 
      }
    ];

    const aggregationData = await ScheduleTaskModel.aggregate(pipeline);

我按日期对所有“任务”进行分组并得到结果:

[
    {
        "_id": "2022-10-21T00:00:00.000Z",
        "tasks": [...tasks with this date]
    },
    {
        "_id": "2022-10-20T00:00:00.000Z",
        "tasks": [...tasks with this date]
    },
    {
        "_id": "2022-10-18T00:00:00.000Z",
        "tasks": [...tasks with this date]
    },
    {
        "_id": "2022-10-16T00:00:00.000Z",
        "tasks": [...tasks with this date]
    }
]

如您所见，我在日期之间缺少日期，这很好，我可以使用简单的 javascript 操作结果，创建一个数组，其中包含高日期和低日期之间的所有日期，并绑定(bind)空任务，然后填充日期也出现在结果中。

问题在于当我想使用 $skip 进行“分页”时，如果例如跳到下 4 组，我无法判断下一个日期是否有任何文档，如果它没有，我最终得到如下内容:

//FIRST RESULT WITH FILLED MISSING DATES
[
    {
        "_id": "2022-10-21T00:00:00.000Z",
        "tasks": [...tasks with this date]
    },
    {
        "_id": "2022-10-20T00:00:00.000Z",
        "tasks": [...tasks with this date]
    },
    {
        "_id": "2022-10-19T00:00:00.000Z",
        "tasks": [] //filled manually
    },
    {
        "_id": "2022-10-18T00:00:00.000Z",
        "tasks": [...tasks with this date]
    },
    {
        "_id": "2022-10-17T00:00:00.000Z",
        "tasks": [] //filled manually
    },
    {
        "_id": "2022-10-16T00:00:00.000Z",
        "tasks": [...tasks with this date]
    }
]

//LOST DAYS IN BETWEEN SKIPS

//SECOND RESULT WITH FILLED MISSING DATES
[
    {
        "_id": "2022-10-14T00:00:00.000Z",
        "tasks": [...tasks with this date]
    },
    {
        "_id": "2022-10-13T00:00:00.000Z",
        "tasks": [...tasks with this date]
    },
    {
        "_id": "2022-10-12T00:00:00.000Z",
        "tasks": [] //filled manually
    },
    {
        "_id": "2022-10-11T00:00:00.000Z",
        "tasks": [...tasks with this date]
    },
    {
        "_id": "2022-10-10T00:00:00.000Z",
        "tasks": [] //filled manually
    },
    {
        "_id": "2022-10-09T00:00:00.000Z",
        "tasks": [...tasks with this date]
    }
]

我仍然努力克服这个问题，不幸的是 $densify 是不可能的，因为我在引入之前使用了 mongo 版本

Answer 1

如果您使用的是 Mongo 版本 5.1+，则可以使用新的 $densify stage，它完全按照你的要求做，就像这样:

db.collection.aggregate([
  {
    $group: {
      _id: "$date",
      tasks: {
        $push: "$$ROOT"
      }
    }
  },
  {
    $densify: {
      field: "_id",
      range: {
        step: 1,
        unit: "day",
        bounds: "full"
      }
    }
  },
  {
    $addFields: {
      tasks: {
        $ifNull: [
          "$tasks",
          []
        ]
      }
    }
  },
  {
    $sort: {
      _id: -1
    },
    
  },
  {
     $skip: n
  },
  {
    $limit: 4
  }
])

Mongo Playground

对于较小的 Mongo 版本，这变得更加困难，虽然在技术上是可行的，但我建议不要这样做，这是一个玩具示例，说明我如何使用 Mongo 4.2 版语法实现它，这在早期版本上是不可能实现的(除非你不愿意将 _id 字段转换为日期，因为返回结果“date”作为数字，然后您可以删除 $toDate 转换)。

使用 $dateAdd 和 $dateDiff 等日期运算符可以使此管道语法变得更加清晰，但这些需要 5.0+ 版本

问题是您必须对整个结果集进行分组，以便对其进行迭代，并尽可能使用 $reduce 和 $map 手动“填充”它想象一下这是非常低效的:

db.collection.aggregate([
  {
    $group: {
      _id: "$date",
      tasks: {
        $push: "$$ROOT"
      }
    }
  },
  {
    $sort: {
      _id: 1
    }
  },
  {
    $group: {
      _id: null,
      roots: {
        $push: "$$ROOT"
      }
    }
  },
  {
    $addFields: {
      roots: {
        $reduce: {
          input: "$roots",
          initialValue: {
            values: [],
            lastDate: null
          },
          in: {
            lastDate: "$$this._id",
            values: {
              $concatArrays: [
                "$$value.values",
                {
                  $map: {
                    input: {
                      $range: [
                        0,
                        {
                          $round: {
                            $divide: [
                              {
                                "$toDouble": {
                                  $subtract: [
                                    "$$this._id",
                                    {
                                      $ifNull: [
                                        "$$value.lastDate",
                                        {
                                          $subtract: [
                                            "$$this._id",
                                            86400000
                                          ]
                                        }
                                      ]
                                    }
                                  ]
                                }
                              },
                              86400000
                            ]
                          }
                        }
                      ]
                    },
                    as: "dayDiff",
                    in: {
                      $cond: [
                        {
                          $eq: [
                            "$$dayDiff",
                            0
                          ]
                        },
                        "$$this",
                        {
                          tasks: [],
                          _id: {
                            $toDate: {
                              $add: [
                                "$$this._id",
                                {
                                  $multiply: [
                                    {
                                      "$multiply": [
                                        86400000,
                                        "$$dayDiff"
                                      ]
                                    },
                                    -1
                                  ]
                                }
                              ]
                            }
                          }
                        }
                      ]
                    }
                  }
                }
              ]
            }
          }
        }
      }
    }
  },
  {
    $unwind: "$roots.values"
  },
  {
    $replaceRoot: {
      newRoot: "$roots.values"
    }
  },
  {
    $sort: {
      _id: -1
    }
  }
])

Mongo Playground