I'm trying the MongoDB aggregation framework to work with nested documents but having trouble returning the expected output, specifically in the $graphLookup
stage. In a non-nested schema, it correctly looks up all the documents as defined in the options and returns all. But in a nested one, it only returns one. I have tried $unwind
and $replaceRoot
as answered here but now it does not work. It would be more understandable through code so here are the samples.
Non-nested document (fileSystem
does not count)
db={
"fileSystem": [
{
"_id": "a",
"label": "Root",
"children": [
"b",
],
},
{
"_id": "b",
"label": "Nested folder 1",
"children": [
"c",
"d",
"e"
],
"parent": "a"
},
{
"_id": "c",
"label": "Nested File 1.1",
"parent": "b"
},
{
"_id": "d",
"label": "Nested File 1.2",
"parent": "b"
},
]
}
// Aggregation Query
db.fileSystem.aggregate([
{
"$match": {
"_id": "a"
}
},
{
"$graphLookup": {
"from": "fileSystem",
"startWith": "$children",
"connectFromField": "children",
"connectToField": "_id",
"as": "nest",
"depthField": "level",
"maxDepth": 1
}
},
])
// correct and expected result
[
{
"_id": "a",
"children": [
"b"
],
"label": "Root",
"nest": [
{
"_id": "b",
"children": [
"c",
"d",
"e"
],
"label": "Nested folder 1",
"level": NumberLong(0),
"parent": "a"
},
{
"_id": "d",
"label": "Nested File 1.2",
"level": NumberLong(1),
"parent": "b"
},
{
"_id": "c",
"label": "Nested File 1.1",
"level": NumberLong(1),
"parent": "b"
}
]
}
]
Nested document and query
db={
"fileSystem": [
{
pp: [
{
"_id": "a",
"label": "Root",
"children": [
"b",
],
},
// ... same as previous
]
}
]
}
// Aggregation Query
db.fileSystem.aggregate([
{
"$unwind": "$pp"
},
{
"$replaceRoot": {
"newRoot": "$pp"
}
},
{
"$match": {
"_id": "a"
}
},
{
"$graphLookup": {
"from": "fileSystem",
"startWith": "$pp.children",
"connectFromField": "pp.children",
"connectToField": "pp._id",
"as": "nest",
"depthField": "level",
}
},
])
// incorrect result
[
{
"_id": "a",
"children": [
"b"
],
"label": "Root",
"nest": []
}
]
$graphLookup searches the collection given in
from
for matching documents. It uses each document in the pipeline as a starting point, but it does not search for, and will not return documents from the pipline.In the sample data there is there is only 1 document, so the best you'll get in that case is for the
next
array to contain the original document.Playground