我想加入收藏。 之前,我只使用查找,这样我就可以获得已加入的分隔字段。 但我需要得到类似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', "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', "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', "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', "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", "password": "xxxxxxxx", "roles": [ { "_id": ObjectId("5a934e000102030405000001"), "approved": true, "id": 1, "name": "employeer" } ] }, { "_id": ObjectId("5a934e000102030405000003"), "email": "admin@test", "password": "xxxxxxxx", "roles": [ { "_id": ObjectId("5a934e000102030405000000"), "approved": true, "id": 0, "name": "administrator" }, { "_id": ObjectId("5a934e000102030405000002"), "approved": true, "id": 2, "name": "freelancer" } ] } ]更多推荐
使用聚合$ lookup和$ mergeObjects
发布评论