Let's say I have two collections called Recipes and Ingredients and they look like below

Recipes

{"RecipeID" : 000, "recipeName" : "Ramen", "IngredientIds" : [101, 103] }
{"RecipeID" : 001, "recipeName" : "FireNoodle", "IngredientIds" : [102, 103] }

Ingredients

{ "IngredientId" : 101, "MoreData" : { "Details" : "This is details for 101"} }
{ "IngredientId" : 102, "MoreData" : { "Details" : "This is details for 102"} }
{ "IngredientId" : 103, "MoreData" : { "Details" : "This is details for 103"} }

How do I fetch all the data including the data in the ingredients in just one query to MongoDB instead of making two queries? I would like to fetch the data in the following format, let's say I query the data for RecipeID : 000 and I would like to get the following result.

 {
"RecipeID" : 000, 
"recipeName" : "Ramen", 
"IngredientIds" : [
{ "IngredientId" : 101, "MoreData" : { "Details" : "This is details for 101"} }, 
{ "IngredientId" : 103, "MoreData" : { "Details" : "This is details for 103"} }
] }

CodePudding user response：

You need $lookup with pipeline.

db.Recipes.aggregate([
  {
    $match: {
      "RecipeID": "000"
    }
  },
  {
    "$lookup": {
      "from": "Ingredients",
      "let": {
        ingredientIds: "$IngredientIds"
      },
      "pipeline": [
        {
          $match: {
            $expr: {
              $in: [
                "$IngredientId",
                "$$ingredientIds"
              ]
            }
          }
        },
        {
          $project: {
            _id: 0
          }
        }
      ],
      "as": "IngredientIds"
    }
  },
  {
    $project: {
      _id: 0
    }
  }
])

Sample Mongo Playground

CodePudding user response：

db.Recipes.aggregate([
  {
    $match: { "RecipeID": "000" }
  },
  {
    $lookup: {
      from: "Ingredients",
      localField: "IngredientIds",
      foreignField: "IngredientId",
      as: "IngredientIds"
    }
  },
  {
    $unset: [ "_id", "IngredientIds._id" ]
  }
])

mongoplayground

CodePudding user response：

Use $lookup to lookup into array without unwind or any extra operation

    db.recipe.aggregate([
  {
    $match: {
      "RecipeID": 0,
      "recipeName": "Ramen"
    }
  },
  {
    $lookup: {
      from: "ingredents",
      localField: "IngredientIds",
      foreignField: "IngredientId",
      as: "IngredientIds"
    }
  },
  {
    $project: {
      _id: 0,
      RecipeID: "$RecipeID",
      recipeName: "$recipeName",
      IngredientIds: {
        IngredientId: 1,
        MoreData: 1
      }
    }
  }
])

mongodb_playground_link