mongodb - 如何在mongodb中加入多个表
问题描述
由于它们的耦合,我目前有 5 个表需要加入。使用 $lookup 我可以将 Order 表与 Plan 表连接起来并得到我需要的东西,但是我该如何处理其他表呢?
这是每个表,以及它需要连接的 Id/表
Plan - _id, unassignedOrderIds(array)
DriverPlan - _id, planId, orderIds(array), driverId, vehicleId
Driver - _id, vehicleId
Vehicle - _id
Orders - _id
最后,我正在寻找 mongoDb 返回一个已加载 UnassignedOrders 和 DriverPlans 的 Plan 对象。紧随其后的是加载了订单、司机和车辆的 DriverPlans。
这是我到目前为止所拥有的:
db.Plan.aggregate([
// Unwind the source
{ "$unwind": "$UnassignedOrderIds" },
// Do the lookup matching
{ "$lookup": {
"from": "Order",
"localField": "UnassignedOrderIds",
"foreignField": "_id",
"as": "UnassignedOrders"
}},
// Unwind the result arrays ( likely one or none )
{ "$unwind": "$UnassignedOrders" },
// Group back to arrays
{ "$group": {
"_id": "$_id",
"Order": { "$push": "$Order" },
"UnassignedOrders": { "$push": "$UnassignedOrders" }
}}
])
样本文件:
//Plan
{
"_id" : ObjectId("5c1d244de707b20cece645f1"),
"UnassignedOrderIds" : [
ObjectId("5c1d247fe707b20cece6462e"),
ObjectId("5c1d035de707b20cece63104")
]
}
//DriverPlan
[{
"_id" : ObjectId("123d247fe707b20cece6462e"),
"PlanId" : ObjectId("5c1d244de707b20cece645f1"),
"DriverId" : ObjectId("1c1d247fe707b20cece64622"),
"VehicleId" : ObjectId("3c1d247fe707b20cece64633"),
"OrderIds": [
ObjectId("5c1d247fe707b20cece64621"),
ObjectId("5c1d247fe707b20cece64624")
]
},{
"_id" : ObjectId("123d247fe707b20cece64655"),
"PlanId" : ObjectId("5c1d244de707b20cece645f1"),
"DriverId" : ObjectId("2c1d035de707b20cece63104"),
"VehicleId" : null,
"OrderIds": [
ObjectId("5c1d247fe707b20cece64625")
]
}]
//Orders
[{
"_id" : ObjectId("5c1d247fe707b20cece6462e"),
"name" "Order1"
},{
"_id" : ObjectId("5c1d035de707b20cece63104"),
"name" "Order2"
},{
"_id" : ObjectId("5c1d247fe707b20cece64621"),
"name" "Order3"
},{
"_id" : ObjectId("5c1d247fe707b20cece64624"),
"name" "Order4"
},{
"_id" : ObjectId("5c1d247fe707b20cece64625"),
"name" "Order5"
}]
//Driver
[{
"_id" : ObjectId("1c1d247fe707b20cece64622"),
"vehicleId" : ObjectId("3c1d247fe707b20cece6462e"),
"name" "Driver1"
},{
"_id" : ObjectId("2c1d035de707b20cece63104"),
"vehicleId" : null,
"name" "Driver2"
},{
"_id" : ObjectId("3c1d247fe707b20cece64621"),
"vehicleId" : ObjectId("3c1d035de707b20cece63104"),
"name" "Driver3"
}]
//Vehicle
[{
"_id" : ObjectId("3c1d247fe707b20cece6462e"),
"name" "Vehicle1"
},{
"_id" : ObjectId("3c1d035de707b20cece63104"),
"name" "Vehicle2"
},{
"_id" : ObjectId("3c1d247fe707b20cece64633"),
"name" "Vehicle3"
}]
预期的输出是 json 对象,如下所示
//Plan with children loaded
{
"_id" : ObjectId("5c1d244de707b20cece645f1"),
"UnassignedOrderIds" : [
ObjectId("5c1d247fe707b20cece6462e"),
ObjectId("5c1d035de707b20cece63104")
],
"UnassignedOrders": [{
"_id" : ObjectId("5c1d247fe707b20cece6462e"),
"name" "Order1"
},{
"_id" : ObjectId("5c1d035de707b20cece63104"),
"name" "Order2"
}],
"DriverPlans" :
[{
"_id" : ObjectId("123d247fe707b20cece6462e"),
"PlanId" : ObjectId("5c1d244de707b20cece645f1"),
"DriverId" : ObjectId("1c1d247fe707b20cece64622"),
"Driver": {
"_id" : ObjectId("1c1d247fe707b20cece64622"),
"vehicleId" : ObjectId("3c1d247fe707b20cece6462e"),
"name" "Driver1"
},
"VehicleId" : ObjectId("3c1d247fe707b20cece64633"),
"Vehicle" : {
"_id" : ObjectId("3c1d247fe707b20cece64633"),
"name" "Vehicle3"
},
"OrderIds": [
ObjectId("5c1d247fe707b20cece64621"),
ObjectId("5c1d247fe707b20cece64624")
],
"Orders" : [
{
"_id" : ObjectId("5c1d247fe707b20cece64621"),
"name" "Order3"
},{
"_id" : ObjectId("5c1d247fe707b20cece64624"),
"name" "Order4"
}]
},{
"_id" : ObjectId("123d247fe707b20cece64655"),
"PlanId" : ObjectId("5c1d244de707b20cece645f1"),
"DriverId" : ObjectId("2c1d035de707b20cece63104"),
"Driver" : {
"_id" : ObjectId("2c1d035de707b20cece63104"),
"vehicleId" : null,
"name" "Driver2"
}
"VehicleId" : null,
"Vehicle" : null,
"OrderIds": [
ObjectId("5c1d247fe707b20cece64625")
],
"Orders": [{
"_id" : ObjectId("5c1d247fe707b20cece64625"),
"name" "Order5"
}
]
}]
}
解决方案
您可以使用以下聚合
db.Plan.aggregate([
{ "$lookup": {
"from": Order.collection.name,
"let": { "unassignedOrderIds": "$UnassignedOrderIds" },
"pipeline": [
{ "$match": { "$expr": { "$in": ["$_id", "$$unassignedOrderIds"] } } }
],
"as": "UnassignedOrderIds"
}},
{ "$lookup": {
"from": DriverPlan.collection.name,
"let": { "planId": "$_id" },
"pipeline": [
{ "$match": { "$expr": { "$eq": ["$PlanId", "$$planId"] } } },
{ "$lookup": {
"from": Driver.collection.name,
"let": { "driveId": "$DriverId" },
"pipeline": [
{ "$match": { "$expr": { "$eq": ["$_id", "$$driveId"] } } }
],
"as": "Driver"
}},
{ "$lookup": {
"from": Vehicle.collection.name,
"let": { "vehicleId": "$VehicleId" },
"pipeline": [
{ "$match": { "$expr": { "$eq": ["$_id", "$$vehicleId"] } } }
],
"as": "Vehicle"
}},
{ "$lookup": {
"from": Order.collection.name,
"let": { "orderIds": "$OrderIds" },
"pipeline": [
{ "$match": { "$expr": { "$in": ["$_id", "$$orderIds"] } } }
],
"as": "Orders"
}}
],
"as": "DriverPlans"
}}
])
使用 mongodb 3.4 及以下$lookup
语法
db.Plan.aggregate([
{ "$lookup": {
"from": Order.collection.name,
"localField": "UnassignedOrderIds",
"foreignField": "_id",
"as": "UnassignedOrderIds"
}},
{ "$lookup": {
"from": DriverPlan.collection.name,
"localField": "_id",
"foreignField": "PlanId",
"as": "DriverPlans"
}},
{ "$unwind": "$DriverPlans" },
{ "$lookup": {
"from": Driver.collection.name,
"localField": "DriverPlans.DriverId",
"foreignField": "_id",
"as": "DriverPlans.Driver"
}},
{ "$unwind": "$DriverPlans.Driver" },
{ "$lookup": {
"from": Vehicle.collection.name,
"localField": "DriverPlans.VehicleId",
"foreignField": "_id",
"as": "DriverPlans.Vehicle"
}},
{ "$unwind": "$DriverPlans.Vehicle" },
{ "$lookup": {
"from": Order.collection.name,
"localField": "DriverPlans.OrderIds",
"foreignField": "_id",
"as": "DriverPlans.Orders"
}},
{ "$group": {
"_id": "$_id",
"DriverPlans": { "$push": "$DriverPlans" }
}}
])
推荐阅读
- google-app-engine - Codeship 自定义脚本部署到 GAE:“python2:找不到命令”
- c++ - 如何从返回有符号整数的函数中返回错误
- symfony-4.2 - 无法安装 Symfony 4.2.10 版本
- r - 如何在带有模块的 navbarPage 中获取选定的 Tab-ID
- c# - 如何将圆 (vtkActor) 转换为点云数据 vtkPoint
- c# - 如何将空日期时间传递给具有 [require] 属性的 mvc 表单?
- javascript - 如何使用单个 js 制作或加载聊天应用程序 UI?
- python - 如何提取已作为参数传递给 Python 的 lambda 函数的函数名称?
- amazon-s3 - aws 与本地目录中存在的服务器文件夹同步
- react-native - 如何将图像的地址放在对象上?平面列表