Lookup and aggregate multiple levels of subdocument in Mongodb

Question

I've tried many answers to similar problems using $lookup, $unwind, and $match, but I can't get this to work for my sub-sub-subdocument situation.

I have this collection, Things:

{
    "_id" : ObjectId("5a7241f7912cfc256468cb27"),
    "name" : "Fortress of Solitude",
    "alias" : "fortress_of_solitude",
},
{
    "_id" : ObjectId("5a7247ec548c9ad042f579e2"),
    "name" : "Batcave",
    "alias" : "batcave",
},
{
    "_id" : ObjectId("6a7247bc548c9ad042f579e8"),
    "name" : "Oz",
    "alias" : "oz",
},

and this one-document collection, Venues:

{
    "_id" : ObjectId("5b9acabbbf71f39223f8de6e"),
    "name" : "The Office",
    "floors" : [ 
        {
            "name" : "1st Floor",
            "places" : [ 
                {
                    "name" : "Front Entrance",
                    "alias" : "front_entrance"
                }
            ] 
        }, 
        {
            "name" : "2nd Floor",
            "places" : [ 
                {
                    "name" : "Batcave",
                    "alias" : "batcave"
                },
                {
                    "name" : "Oz",
                    "alias" : "oz"
                }
           ]
        }
    ]
}

I want to return all the Things, but with the Venue's floors.places.name aggregated with each Thing if it exists if the aliases match between Things and Venues. So, I want to return:

{
    "_id" : ObjectId("5a7241f7912cfc256468cb27"),
    "name" : "Fortress of Solitude",
    "alias" : "fortress_of_solitude",
                                 <-- nothing added here because
                                 <-- it's not found in Venues
},
{
    "_id" : ObjectId("5a7247ec548c9ad042f579e2"),
    "name" : "Batcave",
    "alias" : "batcave",
    "floors" : [                        <-- this should be 
        {                               <-- returned 
            "places" : [                <-- because 
                {                       <-- the alias
                    name" : "Batcave"   <-- matches
                }                       <-- in Venues
            ]                           <-- 
        }                               <-- 
    ]                                   <--     
},
{
    "_id" : ObjectId("6a7247bc548c9ad042f579e8"),
    "name" : "Oz",
    "alias" : "oz",
    "floors" : [                        <-- this should be 
        {                               <-- returned 
            "places" : [                <-- because 
                {                       <-- the alias
                    name" : "Oz"        <-- matches
                }                       <-- in Venues
            ]                           <-- 
        }                               <-- 
    ]                                   <--     
}

I've gotten as far as the following query, but it only returns the entire Venues.floors array as an aggregate onto each Thing, which is way too much extraneous data aggregated. I just want to merge each relevant floor.place sub-subsubdocument from Venues into its corresponding Thing if it exists in Venues.

db.getCollection('things').aggregate([
  {$lookup: {from: "venues",localField: "alias",foreignField: "floors.places.alias",as: "matches"}},
  {
    $replaceRoot: { newRoot: { $mergeObjects: [ { $arrayElemAt: [ "$matches", 0 ] }, "$$ROOT" ] } }
  },
  { $project: { matches: 0 } }  
])

I'm struggling with existing answers, which seem to change at MongoDB version 3.2, 3.4, 3.6, or 4.2 to include or not include $unwind, $pipeline, and other terms. Can someone explain how to get a sub-sub-subdocument aggregated like this? Thanks!

Answer 1

Since MongoDB v3.6, we may perform uncorrelated sub-queries which gives us more flexibility to join two collections.

Try this:

db.things.aggregate([
  {
    $lookup: {
      from: "venues",
      let: {
        "alias": "$alias"
      },
      pipeline: [
        {
          $unwind: "$floors"
        },
        {
          $project: {
            _id: 0,
            places: {
              $filter: {
                input: "$floors.places",
                cond: {
                  $eq: [
                    "$$alias",
                    "$$this.alias"
                  ]
                }
              }
            }
          }
        },
        {
          $match: {
            "places.0": {
              $exists: true
            }
          }
        },
        {
          $unset: "places.name"
        }
      ],
      as: "floors"
    }
  }
])

MongoPlayground

Answer 2

You can try this :

db.things.aggregate([
    {
        $lookup:
        {
            from: "venues",
            let: { alias: "$alias" },
            pipeline: [
                { $unwind: { path: "$floors", preserveNullAndEmptyArrays: true } },
                { $match: { $expr: { $in: ['$$alias', '$floors.places.alias'] } } },
                /**  Below stages are only if you've docs like doc 2 in Venues */
                { $addFields: { 'floors.places': { $filter: { input: '$floors.places', cond: { $eq: ['$$this.alias', '$$alias'] } } } } },
                { $group: { _id: '$_id', name: { $first: '$name' }, floors: { $push: '$floors' } } },
                {$project : {'floors.places.alias': 1, _id :0}} // Optional
            ],
            as: "matches"
        }
    }
])

Test : MongoDB-Playground