MongoDB中使用$lookup关联用户、考勤及嵌套检测数组的问题

现有User、Attendance、Detection三个MongoDB集合，已通过$lookup实现User与对应Attendance数据的关联，但Attendance对象的detections数组仅存储Detection文档的ObjectId，需要将该数组替换为对应的完整Detection文档。

集合结构 #

User集合文档 #

{
      "_id": "60dd781d4524e6c116e234d2",
      "workerFirstName": "AMIT",
      "workerSurname": "SHAH",
      "workerId": "1001",
      "locationName": "HEAD OFFICE",
      "workerDesignation": "IT",
      "workerDepartment": "IT"
}

Attendance集合文档 #

{
        "_id": "61307cee85b5055a15cf01b7",
        "employeeId":"60dd781d4524e6c116e234d2",
        "Date": "2022-11-01T00:00:00.000Z",
        "duration": null,
        "createdAs": "FULL-DAY",
        "detections": [
          "636095dc00d9abc953d8fd57",
          "636132e6bf6fe52c582853b3"
        ]
      }

Detection集合文档 #

{
  "_id": "636095dc00d9abc953d8fd57",
  "AttendanceId": "61307cee85b5055a15cf01b7"
},
{
  "_id": "636132e6bf6fe52c582853b3",
  "AttendanceId": "61307cee85b5055a15cf01b7"
}

尝试的聚合查询 #

const dailyAttendance = await User.aggregate([
  { $sort: { workerId: 1 } },
  {
    $match: {
      lastLocationId: Mongoose.Types.ObjectId(locationId),
      workerType: workerType,
      isActive: true,
      workerId: {
        $nin: [
          "8080",
          "9999",
          "9998",
          "9997",
          "9996",
          "9995",
          "9994",
        ],
      },
    },
  },
  {
    $project: {
      _id: 1,
      workerId: 1,
      workerFirstName: 1,
      workerSurname: 1,
      workerDepartment: 1,
      workerDesignation: 1,
      locationName: 1,
    },
  },
  {
    $lookup: {
      from: "attendances",
      localField: "_id",
      foreignField: "employeeId",
      pipeline: [
        {
          $match: {
            Date: new Date(date),
          },
        },
        {
          $project: typesOfData,
        },
      ],
      as: "attendances",
    },
  },
  {
    $unwind: {
      path: "$attendances",
      preserveNullAndEmptyArrays: true,
    },
  },
])

当前输出结果 #

{
  "dailyAttendance": [
    {
      "_id": "60dd781d4524e6c116e234d2",
      "workerFirstName": "AMIT",
      "workerSurname": "SHAH",
      "workerId": "1001",
      "locationName": "HEAD OFFICE",
      "workerDesignation": "IT",
      "workerDepartment": "IT",
      "attendances": {
        "_id": "61307cee85b5055a15cf01b7",
        "Date": "2022-11-01T00:00:00.000Z",
        "duration": null,
        "createdAs": "FULL-DAY",
        "detections": [
          "636095dc00d9abc953d8fd57",
          "636132e6bf6fe52c582853b3"
        ]
      }
    },
    {
      "_id": "60dd781c4524e6c116e2336c",
      "workerFirstName": "MADASWAMY",
      "workerSurname": "KARUPPASWAMY",
      "workerId": "1002",
      "locationName": "HEAD OFFICE",
      "workerDesignation": "IT",
      "workerDepartment": "IT",
      "attendances": {
        "_id": "61307ce485b5055a15ceec02",
        "Date": "2022-11-01T00:00:00.000Z",
        "duration": null,
        "createdAs": "FULL-DAY",
        "detections": [
          "636095dc00d9abc953d8fd57",
          "636132e6bf6fe52c582853b3"
        ]
      }
    }
  ]
}

需求：将Attendance对象中detections数组的ObjectId替换为对应的完整Detection文档。

在Attendance的$lookup子管道中添加嵌套的$lookup关联Detection集合，直接将查询结果覆盖原detections字段即可。修改后的聚合查询如下：

const dailyAttendance = await User.aggregate([
  { $sort: { workerId: 1 } },
  {
    $match: {
      lastLocationId: Mongoose.Types.ObjectId(locationId),
      workerType: workerType,
      isActive: true,
      workerId: {
        $nin: ["8080", "9999", "9998", "9997", "9996", "9995", "9994"],
      },
    },
  },
  {
    $project: {
      _id: 1,
      workerId: 1,
      workerFirstName: 1,
      workerSurname: 1,
      workerDepartment: 1,
      workerDesignation: 1,
      locationName: 1,
    },
  },
  {
    $lookup: {
      from: "attendances",
      localField: "_id",
      foreignField: "employeeId",
      pipeline: [
        { $match: { Date: new Date(date) } },
        { $project: typesOfData },
        // 新增：关联Detection集合，填充detections数组
        {
          $lookup: {
            from: "detections",
            localField: "detections",
            foreignField: "_id",
            as: "detections"
          }
        }
      ],
      as: "attendances",
    },
  },
  {
    $unwind: {
      path: "$attendances",
      preserveNullAndEmptyArrays: true,
    },
  },
])

关键说明 #

• 新增的嵌套$lookup会将Attendance中detections数组的ObjectId与Detection集合的_id字段匹配，查询到的完整Detection文档会直接替换原detections数组。
• 需确保typesOfData中包含detections字段，否则子管道的$project会过滤掉该字段，导致后续关联无法执行。

预期输出 #

{
  "dailyAttendance": [
    {
      "_id": "60dd781d4524e6c116e234d2",
      "workerFirstName": "AMIT",
      "workerSurname": "SHAH",
      "workerId": "1001",
      "locationName": "HEAD OFFICE",
      "workerDesignation": "IT",
      "workerDepartment": "IT",
      "attendances": {
        "_id": "61307cee85b5055a15cf01b7",
        "Date": "2022-11-01T00:00:00.000Z",
        "duration": null,
        "createdAs": "FULL-DAY",
        "detections": [
          {
            "_id": "636095dc00d9abc953d8fd57",
            "AttendanceId": "61307cee85b5055a15cf01b7"
          },
          {
            "_id": "636132e6bf6fe52c582853b3",
            "AttendanceId": "61307cee85b5055a15cf01b7"
          }
        ]
      }
    },
    // 其他用户数据格式类似
  ]
}

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