How to populate a nested array of refs in MongoDB?

user_3405057 0
Viewed 39

I have the following documents on a "mealPlans" MongoDB collection:

{
    "_id" : ObjectId("5f4e1c7471b52b63b1ff3c25"),
    "name" : "My Meal Plan",
    "meals" : [ 
        {
            "name" : "Breakfast",
            "foods" : [ 
                {
                    "servingSize" : 100,
                    "food" : ObjectId("5f4a5b3486b27b159befb995")
                }, 
                {
                    "servingSize" : 50,
                    "food" : ObjectId("5f4a5b3486b27b159befb996")
                }
            ]
        }, 
        {
            "name" : "Lunch",
            "foods" : [ 
                {
                    "servingSize" : 100,
                    "food" : ObjectId("5f4a5b3486b27b159befb995")
                }
            ]
        }
    ]
}

And another collection "foods" with the nutritional information of each food, eg:

{
    "_id" : ObjectId("5f4a5b3486b27b159befb995"),
    "name" : "Fenway franks",
    "carbohydrate" : 4,
    "calories" : 280,
    "measurementUnit" : "g",
    "fat" : 26,
    "protein" : 12,
    "servingSize" : 100
}

How could I replace the food reference in the meal plan document with the nutritional information of each food? I have tried the following to get all the needed information but I'm just not having success to group everything back:

db.getCollection('mealPlans').aggregate([
 { "$unwind": "$meals" },
 { "$unwind": "$meals.foods" },
 { "$lookup": {
        "from": "foods",
        "localField": "meals.foods.food",
        "foreignField": "_id",
        "as": "meals.foods.food"
 }}
])

I have been struggling for several hours trying different methods all with different results but no one with the proper results. Any advice will be welcome.

Thanks

aggregation-framework mongodb-query mongodb
1 Answers

You can add this after your all pipelines to reconstruct your foods and meals array,

  • $unwind to deconstruct food array
  • $group to reconstruct foods array
  • $group to reconstruct meals array
  {
    $unwind: {
      path: "$meals.foods.food",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $group: {
      _id: {
        _id: "$_id",
        name: "$meals.name"
      },
      name: { $first: "$name" },
      foods: { $push: "$meals.foods" }
    }
  },
  {
    $group: {
      _id: "$_id._id",
      name: { $first: "$name" },
      meals: {
        $push: {
          name: "$_id.name",
          foods: "$foods"
        }
      }
    }
  }

Playground

user_89871280
Related