MongoDB aggregation graphlookup child's parent

Question

I've got some documents representing folders. I'd like to match every folder's parent if it has one.

{"_id":{"$oid":"5f8c406fc8c88110e0d927f2"},"name":"A"}
{"_id":{"$oid":"5f8c406fc8c88110e0d927f3"},"parentFolderId":"5f8c406fc8c88110e0d927f2","name":"B"}

Even if the parent-child relationships are deeper, for A I need an empty parent folder, and for B parent folder A. I tried to use GraphLookup for this but it's not working (every parentFolder is empty), and I don't know what I'm missing:

db.folders.aggregate([
    {
        $addFields: {
            convertedId: { $toString: "$_id" }
        }
    },
    {
        $graphLookup: {
            from: "folders",
            startWith: "$parentFolderId",
            connectFromField: "parentFolderId",
            connectToField: "convertedId",
            as: "parentFolder",
            maxDepth: 0,
        }
    }
])

If I flip the startWith, connectFromField, connectToFields respectively, I get the reverse of what I need, A has an array which is containing B, B has nothing.

Edit:

With the non-working lookup, I get the following result:

{ "_id" : ObjectId("5f8c406fc8c88110e0d927f2"), "name" : "A", "convertedId" : "5f8c406fc8c88110e0d927f2", "parentFolder" : [ ] }
{ "_id" : ObjectId("5f8c406fc8c88110e0d927f3"), "parentFolderId" : "5f8c406fc8c88110e0d927f2", "name" : "B", "convertedId" : "5f8c406fc8c88110e0d927f3", "parentFolder" : [ ] }

What I'd need is something like:

{ "_id" : ObjectId("5f8c406fc8c88110e0d927f2"), "name" : "A", "convertedId" : "5f8c406fc8c88110e0d927f2", "parentFolder" : [ ] }
{ "_id" : ObjectId("5f8c406fc8c88110e0d927f3"), "parentFolderId" : "5f8c406fc8c88110e0d927f2", "name" : "B", "convertedId" : "5f8c406fc8c88110e0d927f3", "parentFolder" : [ { "_id" : ObjectId("5f8c406fc8c88110e0d927f2"), "name" : "A", "convertedId" : "5f8c406fc8c88110e0d927f2" } ] }

The only difference I see from the official "reportsTo" example that I'm using the ID field to match the documents, but that shouldn't be a problem, since I'm not using the ObjectID: https://docs.mongodb.com/manual/reference/operator/aggregation/graphLookup/

user_7255330 · Answer

May this is what you are looking for. I did not require additional convertedId field. So i have not added that. If you want I can add that too. But i think that would be redundant information.

db.collection.aggregate([
  {
    $graphLookup: {
      from: "collection",
      startWith: {
        $toObjectId: "$parentFolderId"
      },
      connectFromField: "parentFolderId",
      connectToField: "_id",
      as: "parentFolder",
      maxDepth: 0,
      
    }
  }
])

Link to Mongo Playground

MongoDB aggregation graphlookup child's parent

1 Answers