Filtering By Grandchild property in mongoose-CodePudding

I'm pretty new to mongoose and I was wondering if I could filter based on a grandchild property. I have looked everywhere and I haven't been able to find a similar question based on what I'm trying to do. Here is the scenario:

Imagine I have a database like this:

db={
  "parents": [
    {
      "_id": ObjectId("5a834e000102030405000000"),
      "child": ObjectId("5a934e000102030405000000")
    },
    {
      "_id": ObjectId("5a834e000102030405000001"),
      "child": ObjectId("5a934e000102030405000001")
    },
    {
      "_id": ObjectId("5a834e000102030405000002"),
      "child": ObjectId("5a934e000102030405000002")
    },
    
  ],
  "children": [
    {
      "_id": ObjectId("5a934e000102030405000000"),
      "grandchild": ObjectId("5a734e000102030405000000")
    },
    {
      "_id": ObjectId("5a934e000102030405000001"),
      "grandchild": ObjectId("5a734e000102030405000001")
    },
    {
      "_id": ObjectId("5a934e000102030405000002"),
      "grandchild": ObjectId("5a734e000102030405000002")
    }
  ],
  "grandchildren": [
    {
      "_id": ObjectId("5a734e000102030405000000"),
      "name": "grandchild1"
    },
    {
      "_id": ObjectId("5a734e000102030405000001"),
      "name": "grandchild2"
    },
    {
      "_id": ObjectId("5a734e000102030405000002"),
      "name": "grandchild3"
    }
  ]
}

I want to return all parents who have a grandchild with the name "grandchild1".

Something similar to

$match: {
      "child.grandchild.name": "grandchild1"
    }

So only this parent will be returned in the results --

[{
  "_id": ObjectId("5a834e000102030405000000"),
  "child": ObjectId("5a934e000102030405000000")
},]

CodePudding user response：

I found the answer before anyone could respond...

https://mongoplayground.net/p/w1kw58PzCyk

db.parents.aggregate([
  {
    $lookup: {
      from: "children",
      localField: "child",
      foreignField: "_id",
      as: "child",
      
    }
  },
  {
    $addFields: {
      child: {
        $arrayElemAt: [
          "$child",
          0
        ]
      }
    }
  },
  {
    "$lookup": {
      from: "grandchildren",
      localField: "child.grandchild",
      foreignField: "_id",
      as: "grandchild",
      
    }
  },
  {
    $addFields: {
      grandchild: {
        $arrayElemAt: [
          "$grandchild",
          0
        ]
      }
    }
  },
  {
    $match: {
      "grandchild.name": "grandchild1"
    }
  }
])