MongoDB Deep Link and Filter Children at the same time

Question

My goal is to return a fully linked object that matches based on the root name of the object I feed in, and also filters the "Sub Sub Objects" at the same time.

So given the below example schema:

1. Root Object Collection

[
  {
     "_id": ObjectId("AAA1"),
     "name": "rootObj1",
     "subObjects": [ ObjectId("BBB1"), ObjectId("BBB2") ]
  },
  {
     "_id": ObjectId("AAA2"),
     "name": "rootObj2",
     "subObjects": []
  }
]

2. Sub Object Collection

[
  {
     "_id": ObjectId("BBB1"),
     "name": "subObjectName1",
     "subSubObjects": [ ObjectId("CCC1"), ObjectId("CCC2") ]
  },
  {
     "_id": ObjectId("BBB2"),
     "name": "subObjectName2",
     "subSubObjects": [ ObjectId("CCC2") ]
  }
]

3. Sub Sub Object Collection

[
  {
     "_id": ObjectId("CCC1"),
     "name": "nameToFind"
  },
  {
     "_id": ObjectId("CCC2"),
     "name": "someOtherName"
  }
]

My goal is to return something like the below if I passed in that I want 'rootObj1' and I only want the subSubObjects that have the name 'nameToFind' (or something similar to the below, i don't care if the _id are still in the objects, just the idea that things are linked together)

Goal Result

{
  "name": "rootObj1",
  "subObjects": [
    {
      "name": "subObjectName1",
      "subSubObjects": [
        {"name": "nameToFind"}
      ]
    },
    {
      "name": "subObjectName2",
      "subSubObjects": []
    }
  ]
}

I found a lot of helpful posts showing off $lookup, $unwind, $lookup (using the pipeline feature), and then using group/project or something to assemble it. And while I am able to use lookup to find the values I end up with something like the below and have trouble assembling it.

So I was trying to run something like this:

rootCollection.aggregate([
    { "$match": {
        "name" : "rootObject1",
    }},
    { "$lookup": {
              "from": "subObject",
              "localField": "subObjects",
              "foreignField": "_id",
              "as": "subObjects"
            }
    },
    { "$lookup": {
                  "from": "subSubObjects",
                  "localField": "subObjects.subSubObjects",
                  "foreignField": "_id",
                  "as": "subSubObjects"
                }
    },
])

Which ends up as something like the below, but I don't really understand how to filter the subSubObjects and then somehow merge it all back together into one structure.

[
  {
    "_id": {"$oid": "AAA1"},
    "name": "rootObject1",
    "subObjects": [ {"$oid": "BBB1")}, {"$oid": "BBB2")} ]
  },
  "subObjects": [
     {
        "_id": {"$oid": "BBB1"},
        "name": "subObjectName1",
        "subSubObjects": [ {"$oid": "CCC1")}, {"$oid": "CCC2")}) ]
     },
     {
        "_id": {"$oid": "BBB2"},
        "name": "subObjectName2",
        "subSubObjects": [ {"$oid": "CCC1")}, {"$oid": "CCC2")}) ]
     },
  ],
  "subSubObjects": [
     {
        "_id": {"$oid": "CCC1"},
        "name": "nameToFind",
     },
     {
        "_id": {"$oid": "CCC2"},
        "name": "someOtherName",
     },
  ]
]

Does this answer your question? [MongoDB nested lookup with 3 levels](https://stackoverflow.com/questions/36019713/mongodb-nested-lookup-with-3-levels) — turivishal, Jul 07 '21 at 14:15
Kind of? It definitely helps. I feel like I need to abuse the group operator because I'm setting the "_id" to something like { "rootObject": { "subObjects": "$subObjects" }} etc. To get it to all collect. Since I'm not really trying to sum anything together. My main concern is as the complexity goes up with my true data structure, this will become hard to manage. — canpan14, Jul 07 '21 at 14:37
My main remaining question is how would I do a filter later on when I got down to the sub sub objects, since neither $group or $lookup do filters. — canpan14, Jul 07 '21 at 14:42
Look at this [answer](https://stackoverflow.com/a/52574565/8987128) from duplicate question, and see the working [playground](https://mongoplayground.net/p/brDpsqNgBt5) for your question, it is not required $group stage when you use lookup with pipeline. — turivishal, Jul 07 '21 at 14:44
I had no idea you could do lookups within the pipeline field like that. I was trying to do a bunch of lookups in a row, and then magically group them later. — canpan14, Jul 07 '21 at 14:51

score 1 · Accepted Answer · answered Jul 07 '21 at 15:41

There is a similar question, and I would recommend this answer from that question, but there is slight difference in field's type and structure,

you can use $lookup with aggregation pipeline,

$match your conditions
$lookup with pipeline in collection 2 and pass subObjects as let
$match, check $in condition using expression
$lookup simple join with collection 3, pass subSubObjects as localField, it will check $in condition by default because subSubObjects is an array.

db.col1.aggregate([
  { "$match": { "name": "rootObj1" } },
  {
    $lookup: {
      from: "col2",
      let: { subObjects: "$subObjects" },
      pipeline: [
        {
          $match: { $expr: { $in: ["$_id", "$$subObjects"] } }
        },
        {
          $lookup: {
            from: "col3",
            localField: "subSubObjects",
            foreignField: "_id",
            as: "subSubObjects"
          }
        }
      ],
      as: "subObjects"
    }
  }
])

Playground

Thanks again! I assume if I want to filter the subSubObjects that return in the final result, I need to add another pipeline matcher after the lookup? — canpan14, Jul 07 '21 at 15:44
yes or you can use again $lookup with pipeline, and put $match stage instead of simple lookup. — turivishal, Jul 07 '21 at 15:47

MongoDB Deep Link and Filter Children at the same time

1 Answers1