$lookup with deeply nested object

Question

I am new to MongoDB and currently working on a recipe App for school which suggests diet plans. Therefore I need to join the "meal" ObjectId in the diet plan of the user (collection "Users") with the ObjectIds in the collections "Meals".

Afterwards I need to join an "ingredient" ObjectID in the "Meals" collection with the ID of the "ingredient" in the "Ingredients" collection. The problem is, that the "ingredient" ObjectID in collection "Meals" is situated in an Object with another integer variable "amount". This object is nested in an array called "ingredients" with many objects such as the one just described.

Below is my Structure:

Users

{
 "_id": ObjectId("5b28cab902f28e18b863bd36"),
 "username: "testUser1",
 "password": "$2a$08$KjddpaSQPjp6aF/gseOhVeddYdqWJCJ4DpFwxfNgsk81G.0TOtN5i",
 "dietPlans": Object
    {
     "dietPlanCurrent":Object
         {
          "monday":Object
               {
               "breakfast":Object
                    {
                     "meal": ObjectId("5b2b9a8bbda339352cc39ec4")
                 },
                 … 
           },
           … 
     },
     …
  },
}

Meals

{
   "_id" : ObjectId("5b2b9a8bbda339352cc39ec4"),
   "name": "Gulasch-breakfast",
   "cuisine": "International",
   "ingredients":[
       {
            "ingredient": ObjectId("5b1ec0f939b55efcd4e28a2d"),
            "amount": 20
       },
       {
            "ingredient": ObjectId("5b1ec42474fc1f58d84264d4"),
            "amount": 20
       },
       {
            "ingredient": ObjectId("5b1ec42474fc1f58d84264d5"),
            "amount": 20
       },
       …   
    ],

   "comments": [
        …
    ]
}

Ingredients

{ 
    {
     "_id": ObjectId("5b1ec0f939b55efcd4e28a2d"),
     "name": "Walnut",
     "calories": 654
      … 
    }
    {
     "_id": ObjectId("5b1ec0f939b55efcd4e28a3d"),
     "name": "Apple",
     "calories": 123
     … 
    }
… 
}

What I am trying to get is:

    {
 "_id": ObjectId("5b28cab902f28e18b863bd36"),
 "username: "testUser1",
 "password": "$2a$08$KjddpaSQPjp6aF/gseOhVeddYdqWJCJ4DpFwxfNgsk81G.0TOtN5i",
 "dietPlans": Object
    {
     "dietPlanCurrent":Object
         {
          "Monday":Object
               {
               "breakfast":Object
                    {
                     "meal": ObjectId("5b2b9a8bbda339352cc39ec4")
                     "matchedIngredients": [
                        {
                         "_id": ObjectId("5b1ec0f939b55efcd4e28a2d"),
                         "name": "Walnut",
                         "calories": 654
                         … 
                        }
                      … 
                      ]

                 },


                 … 
           },


           … 
     },
     …
  },
}

My approach which is not working (only returning empty matchedIngredients Array)

    {
        $match: {
            '_id': mongoose.Types.ObjectId(req.params.userId)
        }
    },
    {
        $lookup:   {
            from: 'meals',
            localField: 'dietPlans.dietPlanCurrent.monday.breakfast.meal',
            foreignField: '_id',
            as: "dietPlans.dietPlanCurrent.monday.breakfast.mealObject"
        }              
    },
    {
        $unwind: {
            path: "$dietPlans.dietPlanCurrent.monday.breakfast.mealObject",
            preserveNullAndEmptyArrays: true
        }
    },
   {
        $unwind: {
               path: "$dietPlans.dietPlanCurrent.monday.breakfast.mealObject.ingredients",
            preserveNullAndEmptyArrays: true
        }
    },
    {
        $lookup:   {
            from: 'ingredients',
            localField: 'dietPlans.dietPlanCurrent.monday.breakfast.mealObject.ingredients.ingredient',
            foreignField: '_id',
            as: "dietPlans.dietPlanCurrent.monday.breakfast.matchedIngredients"
        }              
    }

Help is very much appreciated. I already checked out this approach, but it somehow didn't work:

Approach that didn't work for me

Thank you very much!

Ashh · Accepted Answer · 2018-06-21T16:37:38.060

What you are trying to do is not possible with mongodb version 3.4 but if you upgrade to 3.6 then you can try below aggregation

db.collection.aggregate([
  { "$match": { "_id": mongoose.Types.ObjectId(req.params.userId) } },
  { "$lookup": {
    "from": Meals.collection.name,
    "let": { "meal_id": "$dietPlans.dietPlanCurrent.monday.breakfast.meal" },
    "pipeline": [
      { "$match": { "$expr": { "$eq": [ "$_id", "$$meal_id" ] } } },
      { "$unwind": "$ingredients" },
      { "$lookup": {
        "from": Ingredients.collection.name,
        "let": { "ingredient_id": "$ingredients.ingredient" },
        "pipeline": [
          { "$match": { "$expr": { "$eq": [ "$_id", "$$ingredient_id" ] } } }
        ],
        "as": "matchedIngredients"
      }},
      { "$unwind": "$ingredients.matchedIngredients" },
      { "$group": {
        "_id": "$_id",
        "name": { "$first":"$name" },
        "cuisine": { "$first":"$cuisine" },
        "ingredients": { "$push":"$ingredients" }
      }}
    ],
    "as": "dietPlans.dietPlanCurrent.monday.breakfast.mealObject"
  }},
  { "$unwind": "$dietPlans.dietPlanCurrent.monday.breakfast.mealObject" }
])

please help if you could on related question like this: https://stackoverflow.com/questions/61109179/mongodb-find-deeply-nested-object-with-lookup — newdeveloper, Apr 08 '20 at 20:14
@newdeveloper Sure I will do... It is night here will do in morning — Ashh, Apr 08 '20 at 20:25

$lookup with deeply nested object

1 Answers1