I am struggling to achieve a nested response to a notes nested based schema. I use document parent field to assign child comments (replies) to parent documents.

I also need to do an internal lookup to get the user data which is also store on the document (this is my challenge)

Notes documents:

{ 
    "_id" : ObjectId("629e0a84e0229e4fba0de395"), 
    "order" : ObjectId("6298b2a563edd8c9cfa47ba2"), 
    "notes" : "Aut pariatur Volupt", 
    "createdBy" : ObjectId("61c2abdff3edde4ab0074ca9"),  
}
{ 
    "_id" : ObjectId("629ea872a89b4d1464451307"), 
    "order" : ObjectId("6298b2a563edd8c9cfa47ba2"), 
    "notes" : "dewdewdwe", 
    "createdBy" : ObjectId("61c2abdff3edde4ab0074ca9"), 
}
{ 
    "_id" : ObjectId("629f578460c133c4aab8c028"), 
    "order" : ObjectId("6298b2a563edd8c9cfa47ba2"), 
    "notes" : "dewdwe", 
    "createdBy" : ObjectId("61c2abdff3edde4ab0074ca9"), 
}
{ 
    "_id" : ObjectId("62a000865f24e1887dfd169e"), 
    "order" : ObjectId("6298b2a563edd8c9cfa47ba2"), 
    "noteParent" : ObjectId("629e0a84e0229e4fba0de395"), 
    "notes" : "Yes this one is ok", 
    "createdBy" : ObjectId("619e13eeabdf5bf20384b74a"), 
}
{ 
    "_id" : ObjectId("62a004d1a3c340725d0c732b"), 
    "order" : ObjectId("6298b2a563edd8c9cfa47ba2"), 
    "noteParent" : ObjectId("629e0a84e0229e4fba0de395"), 
    "notes" : "this one too", 
    "createdBy" : ObjectId("619e13eeabdf5bf20384b74a"), 
}

noteParent is a child of other documents createdBy needs a lookup to the users collection

With the following aggregate I get the correct nested documents.

db.getCollection("ordernotes").aggregate(
    [
        { 
            "$lookup" : { 
                "from" : "users", 
                "localField" : "createdBy", 
                "foreignField" : "_id", 
                "as" : "createdBy"
            }
        }, 
        { 
            "$lookup" : { 
                "from" : "ordernotes", 
                "localField" : "_id", 
                "foreignField" : "noteParent", 
                "as" : "replies"
            }
        }, 
        { 
            "$project" : { 
                "order" : 1.0, 
                "notes" : 1.0, 
                "status" : 1.0, 
                "replies" : 1.0, 
                "createdByModel" : 1.0, 
                "createdBy.firstName" : 1.0, 
                "createdBy.lastName" : 1.0, 
                "createdBy.email" : 1.0, 
            }
        }
    ], 
);

However, how can do a another lookup on replies.createdBy without overriding the replies nested document

{ 
    "_id" : ObjectId("629e0a84e0229e4fba0de395"), 
    "order" : ObjectId("6298b2a563edd8c9cfa47ba2"), 
    "notes" : "Aut pariatur Volupt", 
    "status" : "pending", 
    "createdBy" : [
        {
            "firstName" : "Jim", 
            "lastName" : "Jones", 
            "email" : "[email protected]", 
        }
    ], 
    "replies" : [
        {
            "_id" : ObjectId("62a000865f24e1887dfd169e"), 
            "order" : ObjectId("6298b2a563edd8c9cfa47ba2"), 
            "noteParent" : ObjectId("629e0a84e0229e4fba0de395"), 
            "notes" : "Yes this one is ok", 
            "createdBy" : ObjectId("619e13eeabdf5bf20384b74a"), 
        }, 
        {
            "_id" : ObjectId("62a004d1a3c340725d0c732b"), 
            "order" : ObjectId("6298b2a563edd8c9cfa47ba2"), 
            "noteParent" : ObjectId("629e0a84e0229e4fba0de395"), 
            "notes" : "this one too", 
            "createdBy" : ObjectId("619e13eeabdf5bf20384b74b"), 
        }
    ]
}

Expected output:

    "_id" : ObjectId("629e0a84e0229e4fba0de395"), 
    "order" : ObjectId("6298b2a563edd8c9cfa47ba2"), 
    "notes" : "Aut pariatur Volupt", 
    "status" : "pending", 
    "createdBy" : [
        {
            "firstName" : "Jim", 
            "lastName" : "Jones", 
            "email" : "[email protected]", 
        }
    ], 
    "replies" : [
        {
            "_id" : ObjectId("62a000865f24e1887dfd169e"), 
            "order" : ObjectId("6298b2a563edd8c9cfa47ba2"), 
            "noteParent" : ObjectId("629e0a84e0229e4fba0de395"), 
            "notes" : "Yes this one is ok", 
            "createdBy" : [
            {
                "firstName" : "Jane", 
                "lastName" : "Doe", 
                "email" : "[email protected]", 
            }
            ] 
        }, 
        {
            "_id" : ObjectId("62a004d1a3c340725d0c732b"), 
            "order" : ObjectId("6298b2a563edd8c9cfa47ba2"), 
            "noteParent" : ObjectId("629e0a84e0229e4fba0de395"), 
            "notes" : "this one too", 
            "createdBy" : [
            {
                "firstName" : "Bob", 
                "lastName" : "Carr", 
                "email" : "[email protected]", 
            }
           ] , 
        }
    ]
}

CodePudding user response：

If I understand correctly, you want something like this:

1. $lookup into a new array repliesNotes
2. $map to merge each object on replies with its matching object on repliesNotes

db.notes.aggregate([
  {
    $lookup: {
      from: "users",
      localField: "replies.createdBy",
      foreignField: "_id",
      as: "repliesNotes"
    }
  },
  {
    $set: {
      replies: {
        $map: {
          input: "$replies",
          in: {
            $mergeObjects: [
              "$$this",
              {
                createdBy: {
                  $arrayElemAt: [
                    "$repliesNotes",
                    {$indexOfArray: ["$repliesNotes._id", "$$this.id"]}
                  ]
                }
              }
            ]
          }
        }
      },
      repliesNotes: "$$REMOVE"
    }
  }
])

See how it works on the playground example