How to return specific document from different collection by a specific field inside aggregation-CodePudding

I want to populate just those documents from users collection where users._id is equal to levelOne.assignedPeople ids. This will arrive as an array.

I am using this query,

db.parentepics.aggregate([
{
    $match: {
      ideaId: "616bffd81a9c715679374b19",
      isDeleted: false,
      isHidden: false,
      isRemovedByOwner: false,
    },
},
{
    $lookup: {
      from: "childtasklevelones",
      localField: "_id",
      foreignField: "parentEpicId",
      as: "level_1",
    },
  },
  {
    $match: {
      "level_1.assignedPeople": ObjectId('616c00001a9c715679374b1b'),
    },
  },
  {
    $project: {
      _id: 1,
      epicName: 1,
      epicDescription: 1,
      epicPriority: 1,
      startsFrom: 1,
      endsAt: 1,
      levelOne: "$level_1",
    },
  }
])

Which produces some output looks like this,

{
    "_id" : ObjectId("616c099e1a9c715679374b7c"),
    "epicName" : "Marketing",
    "epicDescription" : "Marketing data",
    "epicPriority" : "1",
    "startsFrom" : ISODate("2021-10-15T14:36:31.387 05:30"),
    "endsAt" : ISODate("2021-10-30T14:36:31.387 05:30"),
    "levelOne" : [
        {
            "_id" : ObjectId("616db350123f016231912954"),
            "currentStatus" : "pending",
            "assignedPeople" : [
                ObjectId("616c00001a9c715679374b1b")
            ],
            "isRemovedByOwner" : false,
            "highlightColorPreference" : "yellow",
            "isHidden" : false,
            "isDeleted" : false,
            "taskName" : "Marketing",
            "taskDescription" : "Marketing data",
            "taskPriority" : "1",
            "startsFrom" : ISODate("2021-10-15T14:36:31.387 05:30"),
            "endsAt" : ISODate("2021-10-30T14:36:31.387 05:30"),
            "createdBy" : ObjectId("616bffd81a9c715679374b18"),
            "parentEpicId" : ObjectId("616c099e1a9c715679374b7c"),
            "taskNameToLower" : "marketing",
            "createdAt" : ISODate("2021-10-18T23:18:00.464 05:30"),
            "updatedAt" : ISODate("2021-10-18T23:18:00.464 05:30"),
            "__v" : 0
        }
    ]
}

Now I just need to populate users from users collection where users._id is equal to levelOne.assignedPeople.

I sort, I need the array of users in place of these levelOne.assignedPeople ids.

Please help me to modify the query.

Thanks

CodePudding user response：

You just need to add an extra lookup stage. In this case, because level_1.assignedPeople is already an array, we can just use the lookup stage to replace the array of User Object IDs with an array of User Objects.

Example:

db.parentepics.aggregate([
    { $match: {
        ideaId: "616bffd81a9c715679374b19",
        isDeleted: false,
        isHidden: false,
        isRemovedByOwner: false,
    } },
    { $lookup: {
        from: "childtasklevelones",
        localField: "_id",
        foreignField: "parentEpicId",
        as: "level_1",
    } },
    { $match: {
        "level_1.assignedPeople": ObjectId('616c00001a9c715679374b1b'),
    } },
    { $lookup: {
        from: "users",
        localField: "level_1.assignedPeople",
        foreignField: "_id",
        as: "level_1.assignedPeople",
    } },
    { $project: {
        _id: 1,
        epicName: 1,
        epicDescription: 1,
        epicPriority: 1,
        startsFrom: 1,
        endsAt: 1,
        levelOne: "$level_1",
    } }
])