arrays - MongoDB客户端加入多个集合
问题描述
我的收藏
预订- id,unitId
单位- id,title,courseId
课程- id,title
我想获得带有单元和课程名称的预订数组,如下所示
Boking_id Unit_title course_title
xxxx ABC XYZ
我当前的代码
const bookings = await db.getDB().collection('bookings').aggregate([
{
$lookup:
{
from: 'courseUnit',
localField: 'courseUnitId',
foreignField: '_id',
as: 'uu'
}
},
{
$unwind:"$uu"
}
]).toArray();
解决方案
您正在寻找的解决方案是通过$lookup
在聚合管道中使用多个时间管道来解决的。例如:
db.Bookings.aggregate([
{
"$lookup" :
{"from": "Unit", "localField": "unitId", "foreignField": "_id", as : "UnitDetails" }
},
{
"$unwind" : "$UnitDetails"
},
{
$lookup:
{ "from": "Course", "localField": "UnitDetails.courseId", "foreignField": "_id", as: "CouserDetails" }
},
{
"$unwind" : "$CouserDetails"
},
{
$project: {_id:0, "Boking_id" : "$_id", "Unit_title": "$UnitDetails.title", "course_title": "$CouserDetails.title" }
}
]).pretty()
上述查询的输出如下:
{
"Boking_id" : ObjectId("5dff07e4187da4c2b5ffc59f"),
"Unit_title" : "unit title for course title 3",
"course_title" : "Title of the Course collection 3"
}
我在所有三个集合中的初始集合数据集如下:
> db.Bookings.find()
{ "_id" : ObjectId("5dff07e4187da4c2b5ffc59f"), "unitId" : ObjectId("5dff06d6187da4c2b5ffc59e") }
> db.Unit.find()
{ "_id" : ObjectId("5dff06d6187da4c2b5ffc59e"), "title" : "unit title for course title 3", "courseId" : ObjectId("5dff010d187da4c2b5ffc59b") }
> db.Course.find()
{ "_id" : ObjectId("5dff0108187da4c2b5ffc599"), "title" : "Title of the Course collection" }
{ "_id" : ObjectId("5dff010b187da4c2b5ffc59a"), "title" : "Title of the Course collection 2" }
{ "_id" : ObjectId("5dff010d187da4c2b5ffc59b"), "title" : "Title of the Course collection 3" }
{ "_id" : ObjectId("5dff010f187da4c2b5ffc59c"), "title" : "Title of the Course collection 4" }
>
有关详细信息,请参阅以下参考资料:
如果 Unit & Course 集合中的 unitId 和 courseId 的值存储为简单字符串,那么您必须使用$toObjectId
. 我创建了另一组集合并使用$toObjectId
如下:
> db.Bookings2.find()
{ "_id" : ObjectId("5dff6c2a187da4c2b5ffc5a3"), "unitId" : "5dff66c9187da4c2b5ffc5a2" }
> db.Unit2.find()
{ "_id" : ObjectId("5dff66c9187da4c2b5ffc5a2"), "title" : "Unit title 1", "courseId" : "5dff6694187da4c2b5ffc5a0" }
> db.Course2.find()
{ "_id" : ObjectId("5dff6694187da4c2b5ffc5a0"), "title" : "Title of the Course collection 1" }
{ "_id" : ObjectId("5dff6694187da4c2b5ffc5a1"), "title" : "Title of the Course collection 2" }
>
> db.Bookings2.aggregate([ {$project: {"unitObjectId" : {$toObjectId: "$unitId"}} }, {$lookup: {"from": "Unit2", "localField": "unitObjectId", "foreignField": "_id", as: "UnitDetails" } }, {$unwind: "$UnitDetails"}, {$project: {"courseObjectId": {$toObjectId: "$UnitDetails.courseId" }, "unitTitle": "$UnitDetails.title" } }, {$lookup: {"from": "Course2", "localField": "courseObjectId", "foreignField": "_id", as: "Course2Details" } }, {$unwind: "$Course2Details"}, {$project: {_id: 0, "Boking_id": "$_id", "Unit_title": "$unitTitle", "course_title": "$Course2Details.title" } } ]).pretty()
{
"Boking_id" : ObjectId("5dff6c2a187da4c2b5ffc5a3"),
"Unit_title" : "Unit title 1",
"course_title" : "Title of the Course collection 1"
}
>
推荐阅读
- apache-spark - 聚类算法的建议?
- reactjs - 函数在钩子中运行一次
- angular - Angular 构建找不到具有相对路径的模块(在 azure dev ops 上)
- python - 如何在 Python 3 中计算特定质心(k 均值聚类)的协方差矩阵?
- python - DataFrame 到 sql 语句?
- python - 如何检测 Python 线程是否被杀死?
- php - 我的 php 不能正常工作,我可以找到问题
- javascript - 如何将日期数组压缩到他们的月份?
- sql - 调整重叠日期并应用其他规则
- ruby - 如何在 SublimeREPL 中修复“可能会发现'pry error'”