使用 $graphLookup 使用 Mongo 进行分层查询 [英] Hierarchical queries with Mongo using $graphLookup
本文介绍了使用 $graphLookup 使用 Mongo 进行分层查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个包含 50 万条记录的员工集合.每条记录都将包含以下详细信息.
mongo文档如下.
<代码>{"_id": "234463456453643563456","name": "迈克","empId": "10","managerId": "8",项目":[123"、456"、789"]}- 当我提供任何 empId 时，它应该返回从该经理到底层的完整层次结构以及以下过滤器.
<块引用>
一个.过滤位置
湾项目过滤
结果应该是这样的，
10 ->>>经理//8 6 ---->>8 &6 向经理汇报 10////4 5 2 1 ---->>4 &5 向经理汇报 8 ...对于获得带有级别的分层结果有什么帮助吗?
我无法得到预期的结果.
样本数据:-
db.getCollection("employees").insert({"_id":"10","empId":"10","name":"Employee10","managerId":"15" });db.getCollection("employees").insert({"_id":"8","empId":"8","name":"Employee8","managerId":"10" });db.getCollection("employees").insert({"_id":"6","empId":"6","name":"Employee6","managerId":"10" });db.getCollection("employees").insert({"_id":"4","empId": "4","name":"Employee4","managerId":"8" });db.getCollection("employees").insert({"_id":"5","empId":"5","name":"Employee5","managerId":"8" });db.getCollection("employees").insert({"_id":"2","empId":"2","name":"Employee2","managerId":"6" });db.getCollection("employees").insert({"_id":"1","empId":"1","name":"Employee1","managerId":"6" });查询:-
db.getCollection('employees').aggregate([{$匹配:{empId : "10"}},{$graphLookup:{来自:员工"，startWith: "$empId",connectFromField: "empId",connectToField: "managerId",如:报告人"，最大深度:4，深度字段:级别"}},{$项目:{"empId":1,经理ID":1，"reportees.empId":1,"reportees.name":1,"reportees.managerId":1,"reportees.level":1}}]);实际结果:-
<代码>{_id":10"，"empId" : "10","managerId" : "15",报告人":[{"empId" : "1","name" : "员工 1","managerId" : "6",级别":NumberLong(1)},{"empId" : "4","name" : "Employee4","managerId" : "8",级别":NumberLong(1)},{"empId" : "2","name" : "Employee2","managerId" : "6",级别":NumberLong(1)},{"empId" : "5","name" : "Employee5","managerId" : "8",级别":NumberLong(1)},{"empId" : "6","name" : "Employee6","managerId" : "10",级别":NumberLong(0)},{"empId" : "8","name" : "Employee8","managerId" : "10",级别":NumberLong(0)}]}预期结果:-
<代码>{_id":10"，"empId" : "10","managerId" : "15",报告人":[{"empId" : "6","name" : "Employee6","managerId" : "10",级别":NumberLong(0),报告人":[{"empId" : "1","name" : "员工 1","managerId" : "6",级别":NumberLong(1)},{"empId" : "2","name" : "Employee2","managerId" : "6",级别":NumberLong(1)}]},{"empId" : "8","name" : "Employee8","managerId" : "10",级别":NumberLong(0),报告人":[{"empId" : "5","name" : "Employee5","managerId" : "8",级别":NumberLong(1)},{"empId" : "4","name" : "Employee4","managerId" : "8",级别":NumberLong(1)}]}]}问题:-
- 是否可以使用 $graphLookup 获得预期的输出?
- 此外，是否可以在顶级以及每个子级别获得计数?
- 如何在各个层面应用投影?
- 如何在此之上应用过滤器?
解决方案
这正是你想要的 $graphLookup 用于(至少是遍历位).对于过滤部分，您可以简单地使用 $filter 或
看看这个查询的结果:
db.employees.aggregate({$graphLookup:{来自:员工"，startWith: "$managerId",connectFromField: "managerId",connectToField: "empId",如:经理"，}})根据您的说明更新 1:
为了获得您想要的层次结构，您可以执行以下操作.但是，我不会称其为一个漂亮的解决方案，因为它要求您静态定义要下降的级别数以及重复部分，但它可以为您的示例完成工作.不确定，是否/如何轻松地将其扩展到更多级别.就个人而言，我认为客户端循环解决方案更适合这种工作:
db.employees.aggregate([{$匹配:{empId : "10"}},//级别 0{$graphLookup:{来自:员工"，startWith: "$empId",connectFromField: "empId",connectToField: "managerId",如:报告人"，最大深度:0}},{$unwind: "$reportees"//展平},{$addFields:{"reportees.level": 0//添加级别字段}},//1级{$graphLookup:{来自:员工"，startWith: "$reportees.empId",connectFromField: "reportees.empId",connectToField: "managerId",如:reportees.reportees"，最大深度:0}},{$group: {//将以前扁平化的文档重新组合在一起_id: "$_id",empId: { $first: "$empId" },姓名:{ $first: "$name" },managerId: { $first: "$managerId" },报告人:{ $push: "$reportees" },}},{$addFields:{"reportees.reportees.level": 1//添加级别字段}}])更新 2:
以下查询让您从输出结构的角度到达您想要的位置(我省略了 level 字段，但它应该很容易添加).然而，它并不是特别漂亮，而且需要您预先定义最大的组织深度.
db.employees.aggregate([{$匹配:{empId : "10"}},{$graphLookup: {//从我们的员工范围中获取相关文档来自:员工"，startWith: "$empId",connectFromField: "empId",connectToField: "managerId",如:报告人"}},{$project: {//将我们感兴趣的员工添加到我们正在查看的员工数组中_id: 0,报告者:{ $concatArrays: [ "$reportees", [ { _id: "$_id", empId: "$empId", name: "$name", managerId: "$managerId" } ] ] }}},{$项目:{记者:{$let: {变量:{经理:{$filter: {//删除没有女记者的员工，只保留经理输入: {$地图:{输入:$reportees"，如:这个"，在: {$合并对象:["$$this",{记者:{$filter: {//从员工列表中提取报告人输入:$reportees"，如:那个"，条件: {$eq: [ "$$this._id", "$$that.managerId" ]}}}}]}}},如:这个"，cond: { $ne: [ "$$this.reportees", [] ] }}}},在: {$cond: [//这是在我们到达顶级管理器后中断处理{ $eq: [ "$$managers", [] ] },"$reportees",$$经理"]}}}}},//第二级:与前一阶段完全相同//第三级:与前一阶段完全相同//基本上，从这里开始，您需要重复前一阶段的精确副本才能更深一层]);I have an employee collection with half a million records. Each record will have the following details.
The mongo document is as follows.
{
"_id": "234463456453643563456",
"name": "Mike",
"empId": "10",
"managerId": "8",
"projects" : [ "123", "456", "789"]
}
- When i give any empId, it should return the complete hierarchies from that manager to the bottom level along with the following filter.
a. filter on location
b. filter on projects
The result should be like,
10 ->>> Manager
/
/
8 6 ---->> 8 & 6 reporting to manager 10
/ /
/ /
4 5 2 1 ---->> 4 & 5 reporting to manager 8 ...
Any help will be appreciated for getting the hierarchical results with level?
I am not able to get the result as expected.
Sample Data :-
db.getCollection("employees").insert({"_id":"10","empId": "10","name":"Employee10","managerId":"15" });
db.getCollection("employees").insert({"_id":"8","empId": "8","name":"Employee8","managerId":"10" });
db.getCollection("employees").insert({"_id":"6","empId": "6","name":"Employee6","managerId":"10" });
db.getCollection("employees").insert({"_id":"4","empId": "4","name":"Employee4","managerId":"8" });
db.getCollection("employees").insert({"_id":"5","empId": "5","name":"Employee5","managerId":"8" });
db.getCollection("employees").insert({"_id":"2","empId": "2","name":"Employee2","managerId":"6" });
db.getCollection("employees").insert({"_id":"1","empId": "1","name":"Employee1","managerId":"6" });
Query :-
db.getCollection('employees').aggregate([
{
$match: {
empId : "10"
}
},
{
$graphLookup: {
from: "employees",
startWith: "$empId",
connectFromField: "empId",
connectToField: "managerId",
as: "reportees",
maxDepth: 4,
depthField: "level"
}
},
{
$project: {
"empId":1,
"managerId":1,
"reportees.empId":1,
"reportees.name":1,
"reportees.managerId":1,
"reportees.level":1
}
}
]);
Actual Result :-
{
"_id" : "10",
"empId" : "10",
"managerId" : "15",
"reportees" : [
{
"empId" : "1",
"name" : "Employee1",
"managerId" : "6",
"level" : NumberLong(1)
},
{
"empId" : "4",
"name" : "Employee4",
"managerId" : "8",
"level" : NumberLong(1)
},
{
"empId" : "2",
"name" : "Employee2",
"managerId" : "6",
"level" : NumberLong(1)
},
{
"empId" : "5",
"name" : "Employee5",
"managerId" : "8",
"level" : NumberLong(1)
},
{
"empId" : "6",
"name" : "Employee6",
"managerId" : "10",
"level" : NumberLong(0)
},
{
"empId" : "8",
"name" : "Employee8",
"managerId" : "10",
"level" : NumberLong(0)
}
]
}
Expected Result :-
{
"_id" : "10",
"empId" : "10",
"managerId" : "15",
"reportees" : [
{
"empId" : "6",
"name" : "Employee6",
"managerId" : "10",
"level" : NumberLong(0),
"reportees" : [
{
"empId" : "1",
"name" : "Employee1",
"managerId" : "6",
"level" : NumberLong(1)
},
{
"empId" : "2",
"name" : "Employee2",
"managerId" : "6",
"level" : NumberLong(1)
}
]
},
{
"empId" : "8",
"name" : "Employee8",
"managerId" : "10",
"level" : NumberLong(0),
"reportees" : [
{
"empId" : "5",
"name" : "Employee5",
"managerId" : "8",
"level" : NumberLong(1)
},
{
"empId" : "4",
"name" : "Employee4",
"managerId" : "8",
"level" : NumberLong(1)
}
]
}
]
}
Questions :-
- Is it possible to get the expected output with $graphLookup?
- Also, Is it possible to get the count at the top level and also for each sub level?
- How to apply projection at all level?
- How to apply filter on top of this?
解决方案
That's precicsely what you would $graphLookup for (the traversal bit at least). For the filtering part you could simply use $filter or $match depending on how exactly you want to filter.
Have a look at the results of this query:
db.employees.aggregate({
$graphLookup: {
from: "employees",
startWith: "$managerId",
connectFromField: "managerId",
connectToField: "empId",
as: "managers",
}
})
UPDATE 1 based on your clarification:
In order to get the hierarchical structure that you'd like to get you could do the following. However, I wouldn't call this a pretty solution since it requires you statically define the number of levels you want to go down and also to repeat sections but it does the job for your example. Not sure, if/how easily this can be extended to more levels, either. Personally, I think a client side loop solution would be more suitable for this kind of job:
db.employees.aggregate([
{
$match: {
empId : "10"
}
},
// level 0
{
$graphLookup: {
from: "employees",
startWith: "$empId",
connectFromField: "empId",
connectToField: "managerId",
as: "reportees",
maxDepth: 0
}
},
{
$unwind: "$reportees" // flatten
},
{
$addFields: {
"reportees.level": 0 // add level field
}
},
// level 1
{
$graphLookup: {
from: "employees",
startWith: "$reportees.empId",
connectFromField: "reportees.empId",
connectToField: "managerId",
as: "reportees.reportees",
maxDepth: 0
}
},
{
$group: { // group previously flattened documents back together
_id: "$_id",
empId: { $first: "$empId" },
name: { $first: "$name" },
managerId: { $first: "$managerId" },
reportees: { $push: "$reportees" },
}
},
{
$addFields: {
"reportees.reportees.level": 1 // add level field
}
}
])
UPDATE 2:
The following query gets you to where you want to be from an output structure point of view (I omitted the level field but it should be easy to add). It is, however, not particularly pretty and, again, requires you to define a maximum organisational depth upfront.
db.employees.aggregate([
{
$match: {
empId : "10"
}
},
{
$graphLookup: { // get the relevant documents out of our universe of employees
from: "employees",
startWith: "$empId",
connectFromField: "empId",
connectToField: "managerId",
as: "reportees"
}
},
{
$project: { // add the employee we are interested in into the array of employees we're looking at
_id: 0,
reportees: { $concatArrays: [ "$reportees", [ { _id: "$_id", empId: "$empId", name: "$name", managerId: "$managerId" } ] ] }
}
},
{
$project: {
reportees: {
$let: {
vars: {
managers: {
$filter: { // remove employees with no reportess so keep managers only
input: {
$map: {
input: "$reportees",
as: "this",
in: {
$mergeObjects: [
"$$this",
{
reportees: {
$filter: { // extract reportees from list of employees
input: "$reportees",
as: "that",
cond: {
$eq: [ "$$this._id", "$$that.managerId" ]
}
}
}
}
]
}
}
},
as: "this",
cond: { $ne: [ "$$this.reportees", [] ] }
}
}
},
in: {
$cond: [ // this is to break the processing once we have reached a top level manager
{ $eq: [ "$$managers", [] ] },
"$reportees",
"$$managers"
]
}
}
}
}
},
// second level: exactly identical to the previous stage
// third level: exactly identical to the previous stage
// basically, from here onwards you would need to repeat an exact copy of the previous stage to go one level deeper
]);
这篇关于使用 $graphLookup 使用 Mongo 进行分层查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
