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
}
}
])
CodePudding user response:
db.Recipes.aggregate([
{
$match: { "RecipeID": "000" }
},
{
$lookup: {
from: "Ingredients",
localField: "IngredientIds",
foreignField: "IngredientId",
as: "IngredientIds"
}
},
{
$unset: [ "_id", "IngredientIds._id" ]
}
])
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
}
}
}
])