使用$ graphLookup在Mongo中进行分层查询 [英] Hierarchical queries with Mongo using $graphLookup
问题描述
我有一个拥有50万条记录的员工集合.每条记录将具有以下详细信息.
I have an employee collection with half a million records. Each record will have the following details.
mongo文档如下.
The mongo document is as follows.
{
"_id": "234463456453643563456",
"name": "Mike",
"empId": "10",
"managerId": "8",
"projects" : [ "123", "456", "789"]
}
- 当我提供任何empId时，它应将该管理器的完整层次结构以及以下过滤器返回到底层.
a.筛选 位置
b.过滤项目
a. filter on location
b. filter on projects
结果应该像
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.
样本数据:-
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([
{
$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
}
}
]);
实际结果:-
{
"_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)
}
]
}
预期结果:-
{
"_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)
}
]
}
]
}
问题:-
- 是否可以通过$ graphLookup获得预期的输出?
- 此外，是否有可能获得最高级别的计数以及每个子级别的计数?
- 如何在各个级别应用投影?
- 如何在此之上应用过滤器?
推荐答案
这正是您 $ filter 或 $ match ，具体取决于您要过滤的程度.
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.
看看这个查询的结果:
db.employees.aggregate({
$graphLookup: {
from: "employees",
startWith: "$managerId",
connectFromField: "managerId",
connectToField: "empId",
as: "managers",
}
})
UPDATE 1根据您的说明:
为了获得您想要的层次结构，可以执行以下操作.但是，我不会将其称为一个漂亮的解决方案，因为它需要您静态定义要下降的级别数并重复各个部分，但是它确实可以完成您的示例.不确定是否/可以轻松地将其扩展到更多级别.我个人认为，客户端循环解决方案将更适合此类工作:
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
}
}
])
更新2:
以下查询从输出结构的角度将您带到您想要的位置(我省略了level字段，但是应该很容易添加).但是，它并不是特别漂亮，并且再次要求您预先定义最大的组织深度.
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屋！
