mongodb - MongoDB双嵌套数组保留展开

Question

我有一个双嵌套文档，我需要用另一个集合 $lookup 它。为此，我展开文档，然后执行 $lookup 并在这里敲击。我不能“解开”它。

我在这里感到震惊：

mongoose.aggregate([
    { "$match": { "weekNumber": weekNumber } },
    { "$unwind": "$locations" },
    { "$addFields": { "shifts": "$locations.shifts"}},
    { "$unwind": "$shifts" },
    { "$lookup": {
            "let": { "userObjId": { "$toObjectId": "$shifts.shiftTypeId" } },
            "from": "shiftTypes",
            "pipeline": [
                { "$match" : { "$expr": { "$eq" : [ "$_id", "$$userObjId"] } } }
            ],
            "as": "shiftType"
        }
    },
    { "$addFields": {
            "shifts.name": "$shiftType.name",
            "shifts.color": "$shiftType.color"
        }
    },
    { "$project": {
            "shiftType": 0,
            "locations.shifts": 0
        }
    }
])

目前我有所有必要字段的展开对象，但不能将它们“打包”在一起。

我有一个收藏 - 计划 - 包含以下内容：

[
  {
    _id: ObjectId(),
    "weekNumber": 30,
    "locations": [
      {
        "location": "locationName1",
        "shifts": [
          {
            "shiftTypeId": "shiftType001",
          },
          {
            "shiftTypeId": "shiftType002",
          },
          {
            "shiftTypeId": "shiftType001",
          }
        ]
      },
      {
        "location": "locationName2",
        "shifts": [
          {
            "shiftTypeId": "shiftType001",
          },
          {
            "shiftTypeId": "shiftType002",
          },
          {
            "shiftTypeId": "shiftType001",
          }
        ]
      },
      {
        "location": "locationName3",
        "shifts": [
          {
            "shiftTypeId": "shiftType001",
          },
          {
            "shiftTypeId": "shiftType002",
          },
          {
            "shiftTypeId": "shiftType001",
          }
        ]
      }
    ]
  }
]

我还有另一个收藏 - shiftTypes - 比如

[
  {
    _id: ObjectId("shiftType001"),
    "name": "shift001",
    "color": "red"
  },
  {
    _id: ObjectId("shiftType002"),
    "name": "shift002",
    "color": "blue"
  }
]

我想要实现的是：

[
  {
    _id: ObjectId(),
    "weekNumber": 30,
    "locations": [
      {
        "location": "locationName1",
        "shifts": [
          {
            "shiftTypeId": "shiftType001",
            "name": "shift001",
            "color": "red"
          },
          {
            "shiftTypeId": "shiftType002",
            "name": "shift002",
            "color": "blue"
          },
          {
            "shiftTypeId": "shiftType001",
            "name": "shift001",
            "color": "red"
          }
        ]
      },
      {
        "location": "locationName2",
        "shifts": [
          {
            "shiftTypeId": "shiftType002",
            "name": "shift002",
            "color": "blue"
          },
          {
            "shiftTypeId": "shiftType002",
            "name": "shift002",
            "color": "blue"
          },
          {
            "shiftTypeId": "shiftType001",
            "name": "shift001",
            "color": "red"
          }
        ]
      },
      {
        "location": "locationName3",
        "shifts": [
          {
            "shiftTypeId": "shiftType001",
            "name": "shift001",
            "color": "red"
          },
          {
            "shiftTypeId": "shiftType001",
            "name": "shift001",
            "color": "red"
          },
          {
            "shiftTypeId": "shiftType001",
            "name": "shift001",
            "color": "red"
          }
        ]
      }
    ]
  }
]

提前感谢您的帮助

标签： mongodbaggregate

Answer 1

这正是我想要的；

db.mannings.aggregate([
    // where weekNumber = 30
    { "$match": {"weekNumber": 30}},

    // unwind locations
    { "$unwind": "$locations" },

    // unwind shifts - but keep it even if it is empty
    { "$unwind": { "path": "$locations.shifts", "preserveNullAndEmptyArrays": true } },

    // get the shiftType details from the other collection
    { "$lookup": {
            "let": { "userObjId": { "$toObjectId": "$locations.shifts.shiftTypeId" } },
            "from": "shiftTypes",
            "pipeline": [
                { "$match" : { "$expr": { "$eq" : [ "$_id", "$$userObjId"] } } }
            ],
            "as": "shiftType"
        }
    },

    // convert the first element of $lookup result (array) to object
    { "$addFields": {
            "shiftType": { "$arrayElemAt": [ "$shiftType", 0] }
        }
    },

    // add the name and color parameters to the locations.shifts object
    { "$addFields": {
            "locations.shifts.name": "$shiftType.name",
            "locations.shifts.color": "$shiftType.color"
        }
    },

    // 'remove' the $lookup result as we dont need it anymore
    { "$project": {
            "shiftType": 0
        }
    },

    // group up the unwinded locations
    { "$group": {
            "_id": "$_id",
            "locations": { "$addToSet": "$locations" }
        }
    }
]);

编辑：

有点变化——通过这个分组，我得到了位置细节_id和变化shifts

    { "$group": {
            "_id": {"_id": "$locations._id", "location": "$locations.location", "numberOfHeads": "$locations.numberOfHeads"},
            "shifts": { "$addToSet": "$locations.shifts" }
        }
    }