MongoDB聚合查询：从$lookup嵌套数组提取指定字段

我在执行MongoDB的aggregate聚合查询，通过$lookup关联attendances集合，并用$filter过滤出符合条件的记录后，希望从返回的嵌套attendances数组里只保留指定字段（_id、Date、createdAs）。

当前查询代码 #

const attendanceData = await User.aggregate([
    {
      $match: {
        lastLocationId: Mongoose.Types.ObjectId(typeId),
        isActive: true,
      },
    },
    {
      $project: {
        _id: 1,
        workerId: 1,
        workerFirstName: 1,
        workerSurname: 1,
      },
    },
    {
      $lookup: {
        from: "attendances",
        localField: "_id",
        foreignField: "employeeId",
        as: "attendances",
      },
    },
    {
      $set: {
        attendances: {
          $filter: {
            input: "$attendances",
            cond: {
              $and: [
                {
                  $gte: ["$$this.Date", new Date(fromDate)],
                },
                {
                  $lte: ["$$this.Date", new Date(toDate)],
                },
                {
                  $eq: ["$$this.createdAs", dataType],
                },
                {
                  $eq: ["$$this.status", true],
                },
                {
                  $eq: ["$$this.workerType", workerType],
                },
              ],
            },
          },
        },
      },
    },
    { $skip: 0 },
    { $limit: 10 },
  ]);

当前返回结果 #

{
  "attendanceSheet": [
    {
      "_id": "60dd77c14524e6c116e16aaa",
      "workerFirstName": "FIRST NAME1",
      "workerSurname": "SURNAME1",
      "workerId": "1",
      "attendances": [
        {
          "_id": "6130781085b5055a15c32f2u",
          "workerId": "1",
          "workerFullName": "FIRST NAME",
          "workerType": "Employee",
          "Date": "2022-10-01T00:00:00.000Z",
          "createdAs": "ABSENT"
        },
        {
          "_id": "6130781085b5055a15c32f2u",
          "workerId": "1",
          "workerFullName": "FIRST NAME",
          "workerType": "Employee",
          "Date": "2022-10-02T00:00:00.000Z",
          "createdAs": "ABSENT"
        }
      ]
    },
    {
      "_id": "60dd77c24524e6c116e16c0f",
      "workerFirstName": "FIRST NAME2",
      "workerSurname": "Surname",
      "workerId": "2",
      "attendances": [
        {
          "_id": "6130781a85b5055a15c3455y",
          "workerId": "2",
          "workerFullName": "FIRST NAME2",
          "workerType": "Employee",
          "Date": "2022-10-02T00:00:00.000Z",
          "createdAs": "ABSENT"
        }
      ]
    }
  ]
}

期望结果 #

{
  "attendanceSheet": [
    {
      "_id": "60dd77c14524e6c116e16aaa",
      "workerFirstName": "FIRST NAME1",
      "workerSurname": "Surname",
      "workerId": "1",
      "attendances": [
        {
          "_id": "6130781085b5055a15c32f2u",
          "Date": "2022-10-01T00:00:00.000Z",
          "createdAs": "ABSENT"
        },
        {
          "_id": "6130781085b5055a15c32f2u",
          "Date": "2022-10-02T00:00:00.000Z",
          "createdAs": "ABSENT"
        }
      ]
    },
    {
      "_id": "60dd77c24524e6c116e16c0f",
      "workerFirstName": "FIRST NAME2",
      "workerSurname": "Surname",
      "workerId": "2",
      "attendances": [
        {
          "_id": "6130781a85b5055a15c3455y",
          "Date": "2022-10-02T00:00:00.000Z",
          "createdAs": "ABSENT"
        }
      ]
    }
  ]
}

解决方案 #

在$set阶段中，对过滤后的attendances数组使用$map操作，只保留需要的字段即可。修改后的完整聚合查询代码如下：

const attendanceData = await User.aggregate([
    {
      $match: {
        lastLocationId: Mongoose.Types.ObjectId(typeId),
        isActive: true,
      },
    },
    {
      $project: {
        _id: 1,
        workerId: 1,
        workerFirstName: 1,
        workerSurname: 1,
      },
    },
    {
      $lookup: {
        from: "attendances",
        localField: "_id",
        foreignField: "employeeId",
        as: "attendances",
      },
    },
    {
      $set: {
        attendances: {
          $map: {
            // 先执行原有过滤逻辑
            input: {
              $filter: {
                input: "$attendances",
                cond: {
                  $and: [
                    { $gte: ["$$this.Date", new Date(fromDate)] },
                    { $lte: ["$$this.Date", new Date(toDate)] },
                    { $eq: ["$$this.createdAs", dataType] },
                    { $eq: ["$$this.status", true] },
                    { $eq: ["$$this.workerType", workerType] },
                  ]
                }
              }
            },
            as: "item",
            // 映射提取指定字段
            in: {
              _id: "$$item._id",
              Date: "$$item.Date",
              createdAs: "$$item.createdAs"
            }
          }
        },
      },
    },
    { $skip: 0 },
    { $limit: 10 },
  ]);

通过$map遍历过滤后的数组，只保留你需要的字段，就能得到期望的结果。

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