MongoDB对同一集合的多次查找 [英] MongoDB multiple lookup on same collection
本文介绍了MongoDB对同一集合的多次查找的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个子集合，它有 3 个不同的字段链接到同一个主集合.如果我将它们合并到第 3 个根元素中，第一个覆盖第一个 2，这是有道理的，因为所有三个 3 查找都指向同一个集合.如果我不合并这些集合，我需要遍历结果集并在客户端一一填充模型字段，这是我想避免的.
有没有办法，我可以合并所有 3 个查找结果，并以某种方式可以更改字段名称，因此一个不会覆盖另一个.
以下是我的代码:-
module.exports.getAssignmentByClient = function(req, res) {var query = url.parse(req.url,true).query;Assignment.aggregate([{$匹配:{客户 ID:4，} ,},{$查找:{来自:员工"，localField: "assignedMember1",外国字段:员工编号"，如:成员1"},},{$查找:{来自: "员工",localField: "assignedMember2",外国字段:员工编号"，如:成员2"},},{$查找:{来自:员工"，localField: "指定的领导者",外国字段:员工编号"，如:领袖"},},{$replaceRoot:{新根:{$合并对象:[{$arrayElemAt: [ "$member1", 0 ]},{$arrayElemAt: [ "$member2", 0 ]},{$arrayElemAt: [ "$leader", 0 ]},$$ROOT"]}}},{$项目:{成员 1: 0,成员 2: 0,领导者:0}}],功能(错误，响应){如果(错误)抛出错误；如果(！响应){res.status(200).json({ success: false, message: '出现问题.请联系管理员.'});}别的 {res.status(200).json({成功:真，消息:'成功'，数据:响应});}});};未合并的结果集:
<代码>{成功":真的，"message": "成功",数据": [{"_id": "5b5d083cf7edf20be8999746",assignmentId":25，任务ID":1，指定的领导者":2，"assignedMember1": 1,"assignedMember2": 1,"优先级": "M","startDate": "2018-01-08T05:00:00.000Z","endDate": "2018-09-08T04:00:00.000Z","状态": "R","计费模式": "F",固定金额":12，每小时费率":0，小时":0，"hourlyAmount": 0,"clientId": 4,"__v": 0,成员 1":[{"_id": "5b5906a66a6c614f8cb9bd99",员工编号":1，"员工姓名": "A","名称": "E",电话":9876，"address1": "美国",地址2": ""，"email": "a@a.com","经理": "2",__v":0}],成员 2":[{"_id": "5b5906a66a6c614f8cb9bd99",员工编号":1，"员工姓名": "B","名称": "E",电话":9876，"address1": "美国",地址2": ""，"email": "b@b.com","经理": "2",__v":0}],领导": [{"_id": "5b5906cb6a6c614f8cb9bd9a",员工编号":2，"员工姓名": "C","名称": "M",电话":2488263783，"address1": "U",地址2": ""，"email": "c@c.com",经理": ""，__v":0}]}]}ResultSet with merge:- [The Last lookup with leader replace member 1 and member 2]
<代码>{成功":真的，"message": "成功",数据": [{"_id": "5b5d083cf7edf20be8999746",assignmentId":25，任务ID":1，指定的领导者":2，"assignedMember1": 1,"assignedMember2": 1,"优先级": "M","startDate": "2018-01-08T05:00:00.000Z","endDate": "2018-09-08T04:00:00.000Z","状态": "R","计费模式": "F",固定金额":12，每小时费率":0，小时":0，"hourlyAmount": 0,"clientId": 4,"__v": 0,员工编号":2，"员工姓名": "C","名称": "M",电话":2488263783，"address1": "U",地址2": ""，"email": "c@c.com",经理": ""，}]}更新代码:-
module.exports.getAssignmentByClient = function(req, res) {var query = url.parse(req.url,true).query;Assignment.aggregate([{$匹配:{客户 ID:4，} ,},{$查找:{来自:员工"，localField: "assignedMember1",外国字段:员工编号"，如:成员1"},},{$查找:{来自: "员工",localField: "assignedMember2",外国字段:员工编号"，如:成员2"},},{$查找:{来自:员工"，localField: "指定的领导者",外国字段:员工编号"，如:领袖"},},{$replaceRoot":{新根":{$减少":{"input":[{"k":"member1","v":"$member1"},{"k":"member2","v":"$member2"},{"k":"leader","v":"$leader"}],"initialValue":"$$ROOT",在":{"$mergeObjects":[{$let":{"vars":{"v":{"$arrayElemAt":["$$this.v",0]}},"in":{"$arrayToObject":{$地图":{"输入":{"$objectToArray":"$$v"},"as":"val",在":{"k":{"$concat":["$$this.k","-","$$val.k"]},"v":"$$val.v"}}}}}},$$价值"]}}}}},{$项目":{成员 1":0，成员2":0，领袖":0}}],功能(错误，响应){如果(错误)抛出错误；如果(！响应){res.status(200).json({ success: false, message: '出现问题.请联系管理员.'});}别的 {res.status(200).json({成功:真，消息:'成功'，数据:响应});}});};
带有更新代码的 JSON 结果:-
<代码>{成功":真的，"message": "成功",数据": [{"leader-_id": "5b5906cb6a6c614f8cb9bd9a","leader-staffId": 2,"leader-staffName": "A","leader-designation": "M",领导电话":2488263783，"leader-address1": "美国","leader-address2": "","leader-email": "a@a.com","领导-经理": "",领导者-__v":0，"member2-_id": "5b5906a66a6c614f8cb9bd99","member2-staffId": 1,"member2-staffName": "B","member2-designation": "E","member2-phone": 9876,"member2-address1": "美国","member2-address2": "","member2-email": "b@b.com","member2-manager": "2","member2-__v": 0,"member1-_id": "5b5906a66a6c614f8cb9bd99","member1-staffId": 1,"member1-staffName": "C","member1-designation": "E","member1-phone": 9876,"member1-address1": "美国","member1-address2": "","member1-email": "c@c.com","member1-manager": "2","member1-__v": 0,"_id": "5b5d083cf7edf20be8999746",assignmentId":25，任务ID":1，指定的领导者":2，"assignedMember1": 1,"assignedMember2": 1,"优先级": "M","startDate": "2018-01-08T05:00:00.000Z","endDate": "2018-09-08T04:00:00.000Z","状态": "R","计费模式": "F",固定金额":12，每小时费率":0，小时":0，"hourlyAmount": 0,"clientId": 4,__v":0},{"_id": "5b5e0f9ced856f17187c8888",assignmentId":28，任务ID":2，指定的领导者":2"，"assignedMember1": "1","assignedMember2": "1","优先级": "H","startDate": "2018-01-08T05:00:00.000Z","endDate": "2018-07-08T04:00:00.000Z","状态": "C","计费模式": "F",固定金额":900，每小时费率":0，小时":0，"hourlyAmount": 0,"clientId": 4,__v":0}]}
嵌套集合字段引用 *(我对 assignmentMaster 进行了嵌套查找，然后我想将此集合字段作为匹配字段引用到其他一些集合中，但它没有返回所需的结果.好像我是无法正确引用 assignmentMaster 字段.)
module.exports.getAssignmentByClient = function(req, res) {var query = url.parse(req.url,true).query;var clientId = parseInt(query.clientId);AssignmentDetail.aggregate([{$匹配:{客户 ID:客户 ID，} ,},{$查找:{来自:作业主管"，localField: "assignmentId",外国字段:assignmentId"，如:assignmentMasterData"},},{$查找:{来自:列表"，让:{状态":$assignmentMasterData"}，管道:[{$匹配:{ $expr: { $and: [{ $eq:[ "$listName", "AssignmentStatus" ]},{ $eq:[ "$listItem", "$$status.status" ]}]}}},],如:分配状态"}},{$replaceRoot":{新根":{$减少":{"input":[{"k":"assignmentStatus","v":"$assignmentStatus"},{"k":"assignmentMasterData","v":"$assignmentMasterData"}],"initialValue":"$$ROOT",在":{"$mergeObjects":[{$let":{"vars":{"v":{"$arrayElemAt":["$$this.v",0]}},"in":{"$arrayToObject":{$地图":{"输入":{"$objectToArray":"$$v"},"as":"val",在":{"k":{"$concat":["$$this.k","-","$$val.k"]},"v":"$$val.v"}}}}}},$$价值"]}}}}},{$项目":{分配状态":0，作业主数据":0}}],功能(错误，响应){如果(错误)抛出错误；如果(！响应){res.status(200).json({ success: false, message: '出现问题.请联系管理员.'});}别的 {res.status(200).json({成功:真，消息:'成功'，数据:响应});}});};
解决方案
您可以使用以下聚合查询为每个查找的所有键添加别名前缀.
使用$concat使用 $ 为每个查找的列添加查找别名的前缀地图、$objectToArray &$arrayToObject 运算符.>
如下所示的 member1 示例.类似地添加所有其他查找别名.
类似的东西
{"$replaceRoot":{新根":{"$mergeObjects":[{$let":{"vars":{"v":{"$arrayElemAt":["$member1",0]}},"in":{"$arrayToObject":{$地图":{"输入":{"$objectToArray":"$$v"},"as":"val",在":{"k":{"$concat":["member1","-","$$val.k"]},"v":"$$val.v"}}}}}},--- 其他查找别名在这里]}}}如果您不想为所有别名重复相同的代码，您可以创建一个文档数组并$reduce 来创建合并的文档.
类似的东西
{"$replaceRoot":{新根":{$减少":{"input":[{"k":"member1","v":"$member1"},{"k":"member2","v":"$member2"},{"k":"leader","v":"$leader"}],"initialValue":"$$ROOT",在":{"$mergeObjects":[{$let":{"vars":{"v":{"$arrayElemAt":["$$this.v",0]}},"in":{"$arrayToObject":{$地图":{"输入":{"$objectToArray":"$$v"},"as":"val",在":{"k":{"$concat":["$$this.k","-","$$val.k"]},"v":"$$val.v"}}}}}},$$价值"]}}}}}I have a child collection which has 3 different fields linked to the same master collection. If I merge them into root element 3rd one overwrite first 2, which make sense as all three 3 lookup refers to the same collection. If I do not merge these collections, I need to iterate through the result set and populate model field one by one on the client side, which I want to avoid.
Is there a way, I could merge all 3 lookup result and somehow could change the field name, so one did not override to other.
Below is my code:-
module.exports.getAssignmentByClient = function(req, res) {
var query = url.parse(req.url,true).query;
Assignment.aggregate([
{
$match :
{
clientId: 4,
} ,
},
{
$lookup:
{
from: "staffs",
localField: "assignedMember1",
foreignField: "staffId",
as: "member1"
},
},
{
$lookup:
{
from: "staffs",
localField: "assignedMember2",
foreignField: "staffId",
as: "member2"
},
},
{
$lookup:
{
from: "staffs",
localField: "assignedLeader",
foreignField: "staffId",
as: "leader"
},
},
{
$replaceRoot:
{
newRoot:
{
$mergeObjects: [
{$arrayElemAt: [ "$member1", 0 ]},
{$arrayElemAt: [ "$member2", 0 ]},
{$arrayElemAt: [ "$leader", 0 ]},
"$$ROOT" ]
}
}
},
{
$project:
{
member1: 0,
member2: 0,
leader: 0
}
}
],
function (err, response) {
if (err) throw err;
if (!response) {
res.status(200).json({ success: false, message: 'Somthing went wrong. Please contact admin.'});
}
else {
res.status(200).json({ success: true, message: 'Successfull', data: response});
}
});
};
Result set without merge:
{
"success": true,
"message": "Successfull",
"data": [
{
"_id": "5b5d083cf7edf20be8999746",
"assignmentId": 25,
"taskId": 1,
"assignedLeader": 2,
"assignedMember1": 1,
"assignedMember2": 1,
"priority": "M",
"startDate": "2018-01-08T05:00:00.000Z",
"endDate": "2018-09-08T04:00:00.000Z",
"status": "R",
"billingMode": "F",
"fixedAmount": 12,
"hourlyRate": 0,
"hours": 0,
"hourlyAmount": 0,
"clientId": 4,
"__v": 0,
"member1": [
{
"_id": "5b5906a66a6c614f8cb9bd99",
"staffId": 1,
"staffName": "A",
"designation": "E",
"phone": 9876,
"address1": "US",
"address2": "",
"email": "a@a.com",
"manager": "2",
"__v": 0
}
],
"member2": [
{
"_id": "5b5906a66a6c614f8cb9bd99",
"staffId": 1,
"staffName": "B",
"designation": "E",
"phone": 9876,
"address1": "US",
"address2": "",
"email": "b@b.com",
"manager": "2",
"__v": 0
}
],
"leader": [
{
"_id": "5b5906cb6a6c614f8cb9bd9a",
"staffId": 2,
"staffName": "C",
"designation": "M",
"phone": 2488263783,
"address1": "U",
"address2": "",
"email": "c@c.com",
"manager": "",
"__v": 0
}
]
}
]
}
ResultSet with merge:- [The Last lookup with leader replace member 1 and member 2]
{
"success": true,
"message": "Successfull",
"data": [
{
"_id": "5b5d083cf7edf20be8999746",
"assignmentId": 25,
"taskId": 1,
"assignedLeader": 2,
"assignedMember1": 1,
"assignedMember2": 1,
"priority": "M",
"startDate": "2018-01-08T05:00:00.000Z",
"endDate": "2018-09-08T04:00:00.000Z",
"status": "R",
"billingMode": "F",
"fixedAmount": 12,
"hourlyRate": 0,
"hours": 0,
"hourlyAmount": 0,
"clientId": 4,
"__v": 0,
"staffId": 2,
"staffName": "C",
"designation": "M",
"phone": 2488263783,
"address1": "U",
"address2": "",
"email": "c@c.com",
"manager": "",
}
]
}
Updated Code:-
module.exports.getAssignmentByClient = function(req, res) {
var query = url.parse(req.url,true).query;
Assignment.aggregate([
{
$match :
{
clientId: 4,
} ,
},
{
$lookup:
{
from: "staffs",
localField: "assignedMember1",
foreignField: "staffId",
as: "member1"
},
},
{
$lookup:
{
from: "staffs",
localField: "assignedMember2",
foreignField: "staffId",
as: "member2"
},
},
{
$lookup:
{
from: "staffs",
localField: "assignedLeader",
foreignField: "staffId",
as: "leader"
},
},
{"$replaceRoot":{
"newRoot":{
"$reduce":{
"input":[{"k":"member1","v":"$member1"},{"k":"member2","v":"$member2"},{"k":"leader","v":"$leader"}],
"initialValue":"$$ROOT",
"in":{
"$mergeObjects":[
{"$let":{
"vars":{"v":{"$arrayElemAt":["$$this.v",0]}},
"in":{"$arrayToObject":{
"$map":{
"input":{"$objectToArray":"$$v"},
"as":"val",
"in":{
"k":{"$concat":["$$this.k","-","$$val.k"]},
"v":"$$val.v"}
}
}}
}},
"$$value"
]
}
}
}
}},
{
"$project": {
"member1": 0,
"member2": 0,
"leader": 0
}
}
],
function (err, response) {
if (err) throw err;
if (!response) {
res.status(200).json({ success: false, message: 'Somthing went wrong. Please contact admin.'});
}
else {
res.status(200).json({ success: true, message: 'Successfull', data: response});
}
});
};
JSON Result with updated code:-
{
"success": true,
"message": "Successfull",
"data": [
{
"leader-_id": "5b5906cb6a6c614f8cb9bd9a",
"leader-staffId": 2,
"leader-staffName": "A",
"leader-designation": "M",
"leader-phone": 2488263783,
"leader-address1": "US",
"leader-address2": "",
"leader-email": "a@a.com",
"leader-manager": "",
"leader-__v": 0,
"member2-_id": "5b5906a66a6c614f8cb9bd99",
"member2-staffId": 1,
"member2-staffName": "B",
"member2-designation": "E",
"member2-phone": 9876,
"member2-address1": "US",
"member2-address2": "",
"member2-email": "b@b.com",
"member2-manager": "2",
"member2-__v": 0,
"member1-_id": "5b5906a66a6c614f8cb9bd99",
"member1-staffId": 1,
"member1-staffName": "C",
"member1-designation": "E",
"member1-phone": 9876,
"member1-address1": "US",
"member1-address2": "",
"member1-email": "c@c.com",
"member1-manager": "2",
"member1-__v": 0,
"_id": "5b5d083cf7edf20be8999746",
"assignmentId": 25,
"taskId": 1,
"assignedLeader": 2,
"assignedMember1": 1,
"assignedMember2": 1,
"priority": "M",
"startDate": "2018-01-08T05:00:00.000Z",
"endDate": "2018-09-08T04:00:00.000Z",
"status": "R",
"billingMode": "F",
"fixedAmount": 12,
"hourlyRate": 0,
"hours": 0,
"hourlyAmount": 0,
"clientId": 4,
"__v": 0
},
{
"_id": "5b5e0f9ced856f17187c8888",
"assignmentId": 28,
"taskId": 2,
"assignedLeader": "2",
"assignedMember1": "1",
"assignedMember2": "1",
"priority": "H",
"startDate": "2018-01-08T05:00:00.000Z",
"endDate": "2018-07-08T04:00:00.000Z",
"status": "C",
"billingMode": "F",
"fixedAmount": 900,
"hourlyRate": 0,
"hours": 0,
"hourlyAmount": 0,
"clientId": 4,
"__v": 0
}
]
}
Nested collection field reference * (I have nested lookup for assignmentMaster and then I want to refer this collection field into some other collection as match field but it's not returning the desired result. Seems like I am not able to refer assignmentMaster field correctly.)
module.exports.getAssignmentByClient = function(req, res) {
var query = url.parse(req.url,true).query;
var clientId = parseInt(query.clientId);
AssignmentDetail.aggregate([
{
$match :
{
clientId: clientId,
} ,
},
{
$lookup:
{
from: "assignmentmasters",
localField: "assignmentId",
foreignField: "assignmentId",
as: "assignmentMasterData"
},
},
{
$lookup: {
from: "lists",
let: { "status": "$assignmentMasterData" },
pipeline: [
{
$match:
{ $expr: { $and: [
{ $eq:
[ "$listName", "AssignmentStatus" ]
},
{ $eq:
[ "$listItem", "$$status.status" ]
}
]
}
}
},
],
as: "assignmentStatus"
}},
{"$replaceRoot":{
"newRoot":{
"$reduce":{
"input":[{"k":"assignmentStatus","v":"$assignmentStatus"},{"k":"assignmentMasterData","v":"$assignmentMasterData"}],
"initialValue":"$$ROOT",
"in":{
"$mergeObjects":[
{"$let":{
"vars":{"v":{"$arrayElemAt":["$$this.v",0]}},
"in":{"$arrayToObject":{
"$map":{
"input":{"$objectToArray":"$$v"},
"as":"val",
"in":{
"k":{"$concat":["$$this.k","-","$$val.k"]},
"v":"$$val.v"}
}
}}
}},
"$$value"
]
}
}
}
}},
{
"$project": {
"assignmentStatus": 0,
"assignmentMasterData": 0
}
}
],
function (err, response) {
if (err) throw err;
if (!response) {
res.status(200).json({ success: false, message: 'Somthing went wrong. Please contact admin.'});
}
else {
res.status(200).json({ success: true, message: 'Successfull', data: response});
}
});
};
解决方案
You can use below aggregation query to prefix all the keys of each lookup with its alias.
Use $concat to prefix the lookup alias to each of the looked up column using $map, $objectToArray & $arrayToObject operator.
Example shown below for member1. Add all the other look up alias similarly.
Something like
{"$replaceRoot":{
"newRoot":{
"$mergeObjects":[
{"$let":{
"vars":{"v":{"$arrayElemAt":["$member1",0]}},
"in":{"$arrayToObject":{
"$map":{
"input":{"$objectToArray":"$$v"},
"as":"val",
"in":{
"k":{"$concat":["member1","-","$$val.k"]},
"v":"$$val.v"
}}
}}
}},
--- other look up alias here
]
}
}}
If you don't want to repeat the same code for all alias you can create a array of documents and $reduce to create the merged documents.
Something like
{"$replaceRoot":{
"newRoot":{
"$reduce":{
"input":[{"k":"member1","v":"$member1"},{"k":"member2","v":"$member2"},{"k":"leader","v":"$leader"}],
"initialValue":"$$ROOT",
"in":{
"$mergeObjects":[
{"$let":{
"vars":{"v":{"$arrayElemAt":["$$this.v",0]}},
"in":{"$arrayToObject":{
"$map":{
"input":{"$objectToArray":"$$v"},
"as":"val",
"in":{
"k":{"$concat":["$$this.k","-","$$val.k"]},
"v":"$$val.v"}
}
}}
}},
"$$value"
]
}
}
}
}}
这篇关于MongoDB对同一集合的多次查找的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
