I have this data as below in two collections of MongoDB. categories:
[
{ "_id" : 1, "name" : "A" },
{ "_id" : 2, "name" : "B", "categoryId" : 1 },
{ "_id" : 3, "name" : "C", "categoryId" : 1 },
{ "_id" : 4, "name" : "D", "categoryId" : 2 },
{ "_id" : 5, "name" : "E", "categoryId" : 3 },
{ "_id" : 6, "name" : "F", "categoryId" : 2 }
];
And I have Locos documents:
[
{ "_id" : 1, "name" : "X", "categoryId" : 2 },
{ "_id" : 2, "name" : "Y", "categoryId" : 3 },
{ "_id" : 2, "name" : "B", "categoryId" : 1 }
]
For example if I want to get all the chilren of category A which has the id 1, i call the function and it returns an array of ids of children categories and if possible the Locos chilren. So the result will be like this:
chilren: {
categories: [2, 3, 4, 5, 6],
locos: [1, 2, 3]
}
If i call the function with the id 2 which is category B, I get the result:
chilren: {
categories: [2, 6],
locos: [1]
}
You are on the right track to use $graphLookup
.
db.categories.aggregate([
{
"$match": {
_id: 1
}
},
{
"$graphLookup": {
"from": "categories",
"startWith": "$_id",
"connectFromField": "_id",
"connectToField": "categoryId",
"as": "categoryLookup"
}
},
{
"$graphLookup": {
"from": "locos",
"startWith": "$_id",
"connectFromField": "_id",
"connectToField": "categoryId",
"as": "locosLookup"
}
},
{
"$project": {
children: {
categories: {
"$map": {
"input": "$categoryLookup",
"as": "c",
"in": "$$c._id"
}
},
locos: {
"$map": {
"input": "$locosLookup",
"as": "l",
"in": "$$l._id"
}
}
}
}
}
])
Here is the Mongo playground for your reference.