MongoDB：如何在$lookup后仅保留与父文档ID匹配的数据

每个学生文档的counts字段中，仅保留与该文档_id匹配的考勤统计数据。

学生Schema（Student Schema） #

const StudentSchema = new mongoose.Schema(
  {
    name: {
      type: String,
      required: [true, "Please Provide Name"],
      maxlength: 100,
      minlength: 2,
    },
    email: {
      type: String,
      required: [true, "Please Provide Email"],
      match: [
        /^(([^<>()[\\]\\.,;:\s@"]+(\.[^<>()[\\]\\.,;:\s@"]+)*)|(".+"))@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\])|(([a-zA-Z\-0-9]+\.)+[a-zA-Z]{2,}))$/,
        "Please Provide a Valid Email",
      ],
      unique: true,
    },
    number: {
      type: String,
      required: [true, "Please Provide Number"],
      match: [
        /^(?:(?:\+|0{0,2})91(\s*[\-]\s*)?|[0]?)?[789]\d{9}$/,
        "Please Provide a Valid Number",
      ],
      unique: true,
    },
    rollNumber: {
      type: Number,
      required: [true, "Please Provide Roll Number"],
      maxlength: 5,
    },
    departmentID: {
      type: mongoose.Types.ObjectId,
      ref: "Department",
      required: [true, "Please Provide departmentID"],
    },
    classID: {
      type: mongoose.Types.ObjectId,
      ref: "Class",
      required: [true, "Please Provide classID"],
    },
    position: {
      type: String,
      required: [true, "Please Provide Position"],
      enum: ["Student"],
      default: "Student",
    },
    password: {
      type: String,
      required: [true, "Please Provide Password"],
      minlength: 6,
    },
  },
  { timestamps: true }
);

考勤Schema（Attendance Schema） #

const AttendanceSchema = new Schema(
  {
    date: {
      type: String,
      required: [true, "Please Provide Date"],
      maxlength: 15,
      minlength: 5,
    },
    subjectID: {
      type: mongoose.Types.ObjectId,
      ref: "Subject",
      required: [true, "Please Provide Subject"],
    },
    studentID: {
      type: mongoose.Types.ObjectId,
      ref: "Student",
      required: [true, "Please Provide Student"],
    },
    teacherID: {
      type: mongoose.Types.ObjectId,
      ref: "Faculty",
      required: [true, "Please Provide Teacher"],
    },
    classID: {
      type: mongoose.Types.ObjectId,
      ref: "Class",
      required: [true, "Please Provide Class"],
    },
    departmentID: {
      type: mongoose.Types.ObjectId,
      ref: "Department",
      required: [true, "Please Provide Department"],
    },
  },
  { timestamps: true }
);

当前聚合查询 #

const data = await StudentSchema.aggregate([
      { $match: { classID: mongoose.Types.ObjectId(`${req.params.id}`) } },
      {
        $lookup: {
          from: "attendances",
          pipeline: [
            {
              $match: {
                subjectID: mongoose.Types.ObjectId(`${req.params.Sid}`),
              },
            },
            { $group: { _id: "$studentID", count: { $sum: 1 } } },
          ],
          as: "counts",
        },
      },
    ]);

当前输出 #

{
    "data": [
        {
            "_id": "63677d2960fa65e95aef5e95",
            "name": "Lavannya Urkande",
            "email": "lavannya@gmail.com",
            "number": "9130354519",
            "rollNumber": 201,
            "departmentID": "6365531fdc02a121ffeed944",
            "classID": "636554e8dc02a121ffeed982",
            "position": "Student",
            "password": "$2a$10$mqysVgtIGrYbvMGtHE2vbu0z5g05BlwJizcc.CfWMld78VPrnvcrO",
            "createdAt": "2022-11-06T09:23:53.803Z",
            "updatedAt": "2022-11-06T09:23:53.803Z",
            "__v": 0,
            "counts": [
                {
                    "_id": "6367819d60fa65e95aef5ea7",
                    "count": 2
                },
                {
                    "_id": "63677d2960fa65e95aef5e95",
                    "count": 3
                }
            ]
        },
        {
            "_id": "6367819d60fa65e95aef5ea7",
            "name": "Sohan Shinde",
            "email": "soham@gmail.com",
            "number": "9130354510",
            "rollNumber": 202,
            "departmentID": "6365531fdc02a121ffeed944",
            "classID": "636554e8dc02a121ffeed982",
            "position": "Student",
            "password": "$2a$10$DuXjtayCPgGwkNnpog5IYeEEkY56igtlA/m6vobT44wmlSLcXp1eK",
            "createdAt": "2022-11-06T09:42:53.861Z",
            "updatedAt": "2022-11-06T09:42:53.861Z",
            "__v": 0,
            "counts": [
                {
                    "_id": "6367819d60fa65e95aef5ea7",
                    "count": 2
                },
                {
                    "_id": "63677d2960fa65e95aef5e95",
                    "count": 3
                }
            ]
        }
    ]
}

期望输出 #

{
    "data": [
        {
            "_id": "63677d2960fa65e95aef5e95",
            "name": "Lavannya Urkande",
            "email": "lavannya@gmail.com",
            "number": "9130354519",
            "rollNumber": 201,
            "departmentID": "6365531fdc02a121ffeed944",
            "classID": "636554e8dc02a121ffeed982",
            "position": "Student",
            "password": "$2a$10$mqysVgtIGrYbvMGtHE2vbu0z5g05BlwJizcc.CfWMld78VPrnvcrO",
            "createdAt": "2022-11-06T09:23:53.803Z",
            "updatedAt": "2022-11-06T09:23:53.803Z",
            "__v": 0,
            "counts": [
                {
                    "_id": "63677d2960fa65e95aef5e95",
                    "count": 3
                }
            ]
        },
        {
            "_id": "6367819d60fa65e95aef5ea7",
            "name": "Sohan Shinde",
            "email": "soham@gmail.com",
            "number": "9130354510",
            "rollNumber": 202,
            "departmentID": "6365531fdc02a121ffeed944",
            "classID": "636554e8dc02a121ffeed982",
            "position": "Student",
            "password": "$2a$10$DuXjtayCPgGwkNnpog5IYeEEkY56igtlA/m6vobT44wmlSLcXp1eK",
            "createdAt": "2022-11-06T09:42:53.861Z",
            "updatedAt": "2022-11-06T09:42:53.861Z",
            "__v": 0,
            "counts": [
                {
                    "_id": "6367819d60fa65e95aef5ea7",
                    "count": 2
                }
            ]
        }
    ]
}

解决方案 #

在现有聚合查询的基础上，添加$addFields阶段，使用$filter操作符筛选counts数组，仅保留与当前学生_id匹配的统计项：

const data = await StudentSchema.aggregate([
  { $match: { classID: mongoose.Types.ObjectId(`${req.params.id}`) } },
  {
    $lookup: {
      from: "attendances",
      pipeline: [
        {
          $match: {
            subjectID: mongoose.Types.ObjectId(`${req.params.Sid}`),
          },
        },
        { $group: { _id: "$studentID", count: { $sum: 1 } } },
      ],
      as: "counts",
    },
  },
  {
    $addFields: {
      counts: {
        $filter: {
          input: "$counts",
          cond: { $eq: ["$$this._id", "$_id"] }
        }
      }
    }
  }
]);

说明 #

• $filter会遍历counts数组，input指定要过滤的数组，cond是过滤条件：判断数组中每个元素的_id是否等于当前学生文档的_id。
• 该阶段会替换原有的counts字段，只保留符合条件的统计数据，最终得到每个学生自己的考勤计数。

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