使用聚合$ lookup和$ mergeObjects [英] Using aggregate $lookup and $mergeObjects
问题描述
我想加入收藏。
之前，我只使用查找，这样我就可以获得已加入的分隔字段。
但我需要得到类似mysql join的结果。
我注意到这个动作有$ lookup和$ mergeObjects但效果不好。
I want to join collection. before, I used only lookup, so that I could get separated field that is joined. but I need to get result similar mysql join. I noticed there is $lookup and $mergeObjects for this action but not working well.
用户集合模型。
{
"_id": ObjectId("xxxxxxx"), //this is default id from mongoDB
"name": 'admin user',
"email": 'admin@test.com',
"password": 'xxxxxxxx',
"roles": [
{
"id": 0,
"approved": true
},{
"id": 2,
"approved": true
}
]
},{
"_id": ObjectId("xxxxxxx"), //this is default id from mongoDB
"name": 'userOne',
"email": 'user@test.com',
"password": 'xxxxxxxx',
"roles": [
{
"id": 1,
"approved": true
}
]
}
角色集合模型。
{
"_id": ObjectId("xxxxxxx"), //this is default id from mongoDB
"id": '0',
"name": 'administrator'
},{
"_id": ObjectId("xxxxxxx"), //this is default id from mongoDB
"id": '0',
"name": 'employeer'
},{
"_id": ObjectId("xxxxxxx"), //this is default id from mongoDB
"id": '0',
"name": 'freelancer'
}
加入后，我想获得如下结果。
after join, I want to get result like below.
{
"_id": ObjectId("xxxxxxx"), //this is default id from mongoDB
"name": 'admin user',
"email": 'admin@test.com',
"password": 'xxxxxxxx',
"roles": [
{
"id": 0,
"name": "administrator", //join result
"approved": true
},{
"id": 2,
"name": "freelancer", //join result
"approved": true
}
]
},{
"_id": ObjectId("xxxxxxx"), //this is default id from mongoDB
"name": 'userOne',
"email": 'user@test.com',
"password": 'xxxxxxxx',
"roles": [
{
"id": 1,
"name": "employeer", //join result
"approved": true
}
]
}
推荐答案
您可以使用以下 聚合 ，使用mongodb 3.4
You can use below aggregation with mongodb 3.4
您需要 $ unwind 角色阵列优先然后 $ group 再次回滚
You need to $unwind the roles array first and then $group to rollback again
db.users.aggregate([
{ "$unwind": "$roles" },
{ "$lookup": {
"from": "roles",
"localField": "roles.id",
"foreignField": "id",
"as": "roles.role"
}},
{ "$unwind": "$roles.role" },
{ "$addFields": {
"roles": { "$mergeObjects": ["$roles.role", "$roles"] }
}},
{ "$group": {
"_id": "$_id",
"email": { "$first": "$email" },
"password": { "$first": "$password" },
"roles": { "$push": "$roles" }
}},
{ "$project": { "roles.role": 0 }}
])
这是相当的简单的mongodb 3.6 及以上
Which is quite simple with the mongodb 3.6 and above
db.users.aggregate([
{ "$unwind": "$roles" },
{ "$lookup": {
"from": "roles",
"let": { "roleId": "$roles.id", "approved": "$roles.approved" },
"pipeline": [
{ "$match": { "$expr": { "$eq": ["$id", "$$roleId"] }}},
{ "$addFields": { "approved": "$$approved" }}
],
"as": "roles"
}},
{ "$unwind": "$roles" },
{ "$group": {
"_id": "$_id",
"email": { "$first": "$email" },
"password": { "$first": "$password" },
"roles": { "$push": "$roles" }
}}
])
两者都会给你类似的 输出
Both will give you similar Output
[
{
"_id": ObjectId("5a934e000102030405000004"),
"email": "user@test.com",
"password": "xxxxxxxx",
"roles": [
{
"_id": ObjectId("5a934e000102030405000001"),
"approved": true,
"id": 1,
"name": "employeer"
}
]
},
{
"_id": ObjectId("5a934e000102030405000003"),
"email": "admin@test.com",
"password": "xxxxxxxx",
"roles": [
{
"_id": ObjectId("5a934e000102030405000000"),
"approved": true,
"id": 0,
"name": "administrator"
},
{
"_id": ObjectId("5a934e000102030405000002"),
"approved": true,
"id": 2,
"name": "freelancer"
}
]
}
]
这篇关于使用聚合$ lookup和$ mergeObjects的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
