MongoDB聚合查询：分组前获取匹配后总文档数

需要在MongoDB聚合查询的$group阶段前，获取$match过滤后的所有文档总数，以此计算每个学生考勤记录数的占比。尝试使用$count后无法继续执行分组操作，希望在不丢失数据的前提下，先获取该总数并存储到变量中。

原查询代码 #

const data = await AttendanceSchema.aggregate([
      { $match: { subjectID: mongoose.Types.ObjectId(`${req.params.Sid}`) } },

      // 分组前我还需要获取所有文档的总数

      { $group: { _id: "$studentID", count: { $sum: 1 } } },
      {
        $lookup: {
          from: "students",
          localField: "_id",
          foreignField: "_id",
          as: "student",
        },
      },
      {
        $project: {
          "student.createdAt": 0,
          "student.updatedAt": 0,
          "student.__v": 0,
          "student.password": 0,
        },
      },
    ]);

当前返回数据 #

{
    "data": [
        {
            "_id": "635d40803352895afffdc294",
            "count": 3,
            "student": [
                {
                    "_id": "635d40803352895afffdc294",
                    "name": "D R",
                    "email": "d@gmail.com",
                    "number": "9198998888",
                    "rollNumber": 202,
                    "departmentID": "635a8ca21444a47d65d32c1a",
                    "classID": "635a92141a081229013255b4",
                    "position": "Student"
                }
            ]
        },
        {
            "_id": "635eb8898dea5f437789b751",
            "count": 4,
            "student": [
                {
                    "_id": "635eb8898dea5f437789b751",
                    "name": "V R",
                    "email": "v@gmail.com",
                    "number": "9198998899",
                    "rollNumber": 203,
                    "departmentID": "635a8ca21444a47d65d32c1a",
                    "classID": "635a92141a081229013255b4",
                    "position": "Student"
                }
            ]
        }
    ]
}

期望输出 #

{
    "data": [
        {
            "_id": "635d40803352895afffdc294",
            "totalCount": 7, //这是我需要的内容
            "count": 3, 
            "student": [
                {
                    "_id": "635d40803352895afffdc294",
                    "name": "D R",
                    "email": "d@gmail.com",
                    "number": "9198998888",
                    "rollNumber": 202,
                    "departmentID": "635a8ca21444a47d65d32c1a",
                    "classID": "635a92141a081229013255b4",
                    "position": "Student"
                }
            ]
        },
        {
            "_id": "635eb8898dea5f437789b751",
            "totalCount": 7, //这是我需要的内容
            "count": 4,
            "student": [
                {
                    "_id": "635eb8898dea5f437789b751",
                    "name": "V R",
                    "email": "v@gmail.com",
                    "number": "9198998899",
                    "rollNumber": 203,
                    "departmentID": "635a8ca21444a47d65d32c1a",
                    "classID": "635a92141a081229013255b4",
                    "position": "Student"
                }
            ]
        }
    ]
}

方法一：使用$facet并行计算总数和分组数据 #

$facet允许在一个聚合阶段中同时执行多个独立的聚合管道，可同时获取过滤后的总文档数和分组后的考勤数据，最后将总数合并到每个分组结果中。

修改后的聚合查询：

const data = await AttendanceSchema.aggregate([
  { $match: { subjectID: mongoose.Types.ObjectId(`${req.params.Sid}`) } },
  // 并行计算总数和分组数据
  {
    $facet: {
      totalCount: [{ $count: "value" }],
      groupedData: [
        { $group: { _id: "$studentID", count: { $sum: 1 } } },
        {
          $lookup: {
            from: "students",
            localField: "_id",
            foreignField: "_id",
            as: "student",
          },
        },
        {
          $project: {
            "student.createdAt": 0,
            "student.updatedAt": 0,
            "student.__v": 0,
            "student.password": 0,
          },
        },
      ],
    },
  },
  // 提取总数并合并到每个分组结果
  { $unwind: "$totalCount" },
  { $addFields: { totalCount: "$totalCount.value" } },
  { $unwind: "$groupedData" },
  {
    $replaceRoot: {
      newRoot: { $mergeObjects: ["$groupedData", { totalCount: "$totalCount" }] },
    },
  },
]);

方法二：使用$setWindowFields（MongoDB 5.0+支持） #

如果你的MongoDB版本为5.0及以上，$setWindowFields可以直接在分组前计算全局总数，实现更简洁：

const data = await AttendanceSchema.aggregate([
  { $match: { subjectID: mongoose.Types.ObjectId(`${req.params.Sid}`) } },
  // 计算全局总文档数并添加到每个文档
  {
    $setWindowFields: {
      partitionBy: null, // 不分区，全局计算
      output: { totalCount: { $count: {} } },
    },
  },
  // 分组时保留总数（所有文档的totalCount一致，取第一个值即可）
  {
    $group: {
      _id: "$studentID",
      count: { $sum: 1 },
      totalCount: { $first: "$totalCount" },
    },
  },
  {
    $lookup: {
      from: "students",
      localField: "_id",
      foreignField: "_id",
      as: "student",
    },
  },
  {
    $project: {
      "student.createdAt": 0,
      "student.updatedAt": 0,
      "student.__v": 0,
      "student.password": 0,
    },
  },
]);

两种方法都能在不丢失数据的前提下获取过滤后的总文档数，并将其注入每个分组结果中，满足需求。

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