我使用以下查询来获取用户和项目集合的组合数据:
db.collection.aggregate([
{
"$group": {
"_id": "$userId",
"projectId": { "$push": "$projectId" }
}
},
{
"$lookup": {
"from": "users",
"let": { "userId": "$_id" },
"pipeline": [
{ "$match": { "$expr": { "$eq": [ "$_id", "$$userId" ] }}},
{ "$project": { "firstName": 1 }}
],
"as": "user"
}
},
{ "$unwind": "$user" },
{
"$lookup": {
"from": "projects",
"let": { "projectId": "$projectId" },
"pipeline": [
{ "$match": { "$expr": { "$in": [ "$_id", "$$projectId" ] }}},
{ "$project": { "projectName": 1 }}
],
"as": "projects"
}
}
])
结果如下:
[
{
"_id": "5c0a29e597e71a0d28b910aa",
"projectId": [
"5c0a2a8897e71a0d28b910ac",
"5c0a4083753a321c6c4ee024"
],
"user": {
"_id": "5c0a29e597e71a0d28b910aa",
"firstName": "Amit"
},
"projects": [
{
"_id": "5c0a2a8897e71a0d28b910ac",
"projectName": "LN-PM"
},
{
"_id": "5c0a4083753a321c6c4ee024",
"projectName": "fallbrook winery"
}
]
},
{
"_id": "5c0a29c697e71a0d28b910a9",
"projectId": [
"5c0a4083753a321c6c4ee024"
],
"user": {
"_id": "5c0a29c697e71a0d28b910a9",
"firstName": "Rajat"
},
"projects": [
{
"_id": "5c0a4083753a321c6c4ee024",
"projectName": "fallbrook winery"
}
]
}
]
现在我有另一个表"Worksheets"并希望在项目数组中包含 hours 字段,这将通过在projects数组中指定projectId从工作表表中计算出来 . 它将在工作表中找到,小时数将增加此_id在工作表表中的次数 . 以下是我的工作表集合:
{
"_id" : ObjectId("5c0a4efa91b5021228681f7a"),
"projectId" : ObjectId("5c0a4083753a321c6c4ee024"),
"hours" : 8,
"userId" : ObjectId("5c0a29c697e71a0d28b910a9"),
"__v" : 0
}
{
"_id" : ObjectId("5c0a4f4191b5021228681f7c"),
"projectId" : ObjectId("5c0a2a8897e71a0d28b910ac"),
"hours" : 6,
"userId" : ObjectId("5c0a29e597e71a0d28b910aa"),
"__v" : 0
}
结果如下所示:
{
"_id": "5c0a29c697e71a0d28b910a9",
"projectId": [
"5c0a4083753a321c6c4ee024"
],
"user": {
"_id": "5c0a29c697e71a0d28b910a9",
"firstName": "Rajat"
},
"projects": [
{
"_id": "5c0a4083753a321c6c4ee024",
"projectName": "fallbrook winery",
"hours":8
}
]
}