I have a data set
db.users.insertMany([ {"_id":1, "name":"abcd" }, {"_id":2, "name":"abcd"}, {"_id":3, "name":"abcd" }, {"_id":4, "name":"abcd"}, {"_id":5, "name":"abcd" }, {"_id":6, "name":"abcd"}, {"_id":7, "name":"abcd" }, {"_id":8, "name":"abcd"}, {"_id":9, "name":"abcd"}, {"_id":10, "name":"abcd" }, {"_id":11, "name":"abcd"}, {"_id":12, "name":"abcd"}, {"_id":13, "name":"abcd"}, {"_id":14, "name":"abcd"}, {"_id":15, "name":"abcd"}, {"_id":16, "name":"abcd"}, ])
db.users_hirarchy.insertMany([{ "_id": "1101", "_from": 14, "_to": 15 },{ "_id": "1102", "_from": 14, "_to": 16 },{ "_id": "1103", "_from": 15, "_to": 3 },{ "_id": "1104", "_from": 15, "_to": 5 },{ "_id": "1105", "_from": 15, "_to": 7 },{ "_id": "1106", "_from": 3, "_to": 1 },{ "_id": "1107", "_from": 3, "_to": 2 },{ "_id": "1108", "_from": 3, "_to": 4 },{ "_id": "1109", "_from": 3, "_to": 4 },{ "_id": "1110", "_from": 3, "_to": 4 },{ "_id": "1111", "_from": 3, "_to": 4 }
])
What I want to achieve is something like this
{
nodes:[
{
"_id" : 3,
"name": "abcd"
},
{
"_id" : 4,
"name": "abcd"
}
] ,
hirarchies : [
{
"_id" : 3,
"Hierachy" : [
{
"_id" : "1106",
"_from" : 3.0,
"_to" : 1.0,
"depth" : NumberLong(0)
},
{
"_id" : "1107",
"_from" : 3.0,
"_to" : 2.0,
"depth" : NumberLong(0)
} ] },
{
"_id" : 4,
"Hierachy" : [
{
"_id" : "1106",
"_from" : 3.0,
"_to" : 1.0,
"depth" : NumberLong(0)
} ] } ]
I have written a query but it doesn't give the required results. how to do it using graphlookup. my tried query is
db.users.aggregate([
{ $graphLookup: {
from: "users_hirarchy",
connectToField: "_from",
startWith: "$_id",
maxDepth: 0,
connectFromField: "_to",
depthField: "depth",
as: "hirarchy"
} } ,
{$project:{"Data":{
"id":"$_id",
"name":"$name"},
"Hierachy":"$hirarchy"}
}])
How can I achieve my desired output?
so i achieved this using facet in aggregate as
db.users.aggregate([
{ $graphLookup: {
from: "users_hirarchy",
connectToField: "_from",
startWith: "$_id",
maxDepth: 0,
connectFromField: "_to",
depthField: "depth",
as: "hirarchy"
}
}, {
$facet: {
"Node": [
{
$project: {
"hirarchy": 0
}
}],
"hirarchy": [
{
$project: {
"hirarchy": 1,
"_id": 1
}
}
]}}
])