使用聚合$ lookup和$ mergeObjects

本文介绍了使用聚合$ lookup和$ 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', "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" } ] } ]