MongoDB: return field in child documents that is only present in

MongoDB: return field in child documents that is only present in the parent?

I have this Mongo DB Query that is returning in format I expect:

db.getCollection("myCollection").aggregate([
  {
    $lookup: {
      from: "myCollection",
      localField: "parent_id",
      foreignField: "parent_id",
      as: "children"
    }
  },
  { $unwind: "$children" },
  { $replaceWith: "$children" }
])

However, I also want the parent_desc field that is only present in the parent docs to be returned in the child docs in my response

example child doc:

{
id: doc_123_2
parent_id: 123
active: true
}

example parent doc:

{
_id: doc_123
parent_id: 123
active: true
parent_desc: sample parent desc
}

How can I modify my query to do so?

Answers

To include the parent_desc field from the parent documents in the child documents in your response, you can use another $lookup stage to join the parent documents again based on the parent_id. Here's how you can modify your query:

db.getCollection("myCollection").aggregate([
  {
    $lookup: {
      from: "myCollection",
      localField: "parent_id",
      foreignField: "parent_id",
      as: "children"
    }
  },
  { $unwind: "$children" },
  {
    $lookup: {
      from: "myCollection",
      localField: "children.parent_id",
      foreignField: "parent_id",
      as: "parent"
    }
  },
  { $unwind: "$parent" },
  {
    $addFields: {
      "children.parent_desc": "$parent.parent_desc"
    }
  },
  { $replaceWith: "$children" }
])

In this modified query:

1. The first $lookup stage remains the same, which joins the child documents to their respective parent documents.
2. After unwinding the children array, a second $lookup stage is used to join the child documents to their respective parent documents again, based on the parent_id of the children.
3. After unwinding the parent array, the $addFields stage is used to add the parent_desc field from the parent documents to the child documents.
4. Finally, the $replaceWith stage is used to replace each document with its corresponding child document.

With this modification, each child document in the result will contain the parent_desc field from its parent document.