How to grab recursively data from mongo db?

Question

I have some data (let's call it logs) in mongodb, let's say like this:

{
  name: String,
  category_id: String
}

Each category has parent_id. What I want is to get as up tree as possible, to the first parent and get all parents for each item of data I get from so called logs.

What I thought of first: in the controller to get all projects, then recursively get it's all parents. It'll probably works, but it seems tedious and wrong.

There is probably a better thing to do on the model itself, like a static method.

So, my question is how would you do this with mongodb? I know there are aggregations, and I used them a couple of times, but I can see how to use them if by certain field with the certain value. But here you get one project, get the next by it's parent_id and so on and so on.

Answer 1

You have to look at $graphLookup aggregation stage. Provide a set of relevant data for more help.

EDIT : here an example :

---DATA---

#logs collection
db.logs.find({});
{ 
    "_id" : ObjectId("5b4f2970d42ef3178d108e86"), 
    "name" : "01", 
    "category" : "cat1"
}
{ 
    "_id" : ObjectId("5b4f2981d42ef3178d108e87"), 
    "name" : "02", 
    "category" : "cat1"
}
{ 
    "_id" : ObjectId("5b4f298ad42ef3178d108e88"), 
    "name" : "03", 
    "category" : "cat2"
}
{ 
    "_id" : ObjectId("5b4f2997d42ef3178d108e89"), 
    "name" : "04", 
    "category" : "cat2"
}
{ 
    "_id" : ObjectId("5b4f29bed42ef3178d108e8a"), 
    "name" : "015", 
    "category" : "cat10"
}

#categories collection
db.categories.find({});
{ 
    "_id" : "cat1", 
    "parent_id" : "cat2"
}
{ 
    "_id" : "cat2", 
    "parent_id" : "cat10"
}
{ 
    "_id" : "cat10"
}

---AGGREGATION QUERY---

db.logs.aggregate(
    [
        {
            $graphLookup: {
                from: "categories",
                startWith: "$category", // connectToField value(s) that recursive search starts with
                connectFromField: "parent_id",
                connectToField: "_id",
                as: "related_categories",
                maxDepth: 10, // optional
                depthField: "depthField" // optional - name of field in output documents
            }
        },
    ],
);

---OUTPUT---

{ 
    "_id" : ObjectId("5b4f2970d42ef3178d108e86"), 
    "name" : "01", 
    "category" : "cat1", 
    "related_categories" : [
        {
            "_id" : "cat10", 
            "depthField" : NumberLong(2)
        }, 
        {
            "_id" : "cat2", 
            "parent_id" : "cat10", 
            "depthField" : NumberLong(1)
        }, 
        {
            "_id" : "cat1", 
            "parent_id" : "cat2", 
            "depthField" : NumberLong(0)
        }
    ]
}
{ 
    "_id" : ObjectId("5b4f2981d42ef3178d108e87"), 
    "name" : "02", 
    "category" : "cat1", 
    "related_categories" : [
        {
            "_id" : "cat10", 
            "depthField" : NumberLong(2)
        }, 
        {
            "_id" : "cat2", 
            "parent_id" : "cat10", 
            "depthField" : NumberLong(1)
        }, 
        {
            "_id" : "cat1", 
            "parent_id" : "cat2", 
            "depthField" : NumberLong(0)
        }
    ]
}
{ 
    "_id" : ObjectId("5b4f298ad42ef3178d108e88"), 
    "name" : "03", 
    "category" : "cat2", 
    "related_categories" : [
        {
            "_id" : "cat10", 
            "depthField" : NumberLong(1)
        }, 
        {
            "_id" : "cat2", 
            "parent_id" : "cat10", 
            "depthField" : NumberLong(0)
        }
    ]
}
{ 
    "_id" : ObjectId("5b4f2997d42ef3178d108e89"), 
    "name" : "04", 
    "category" : "cat2", 
    "related_categories" : [
        {
            "_id" : "cat10", 
            "depthField" : NumberLong(1)
        }, 
        {
            "_id" : "cat2", 
            "parent_id" : "cat10", 
            "depthField" : NumberLong(0)
        }
    ]
}
{ 
    "_id" : ObjectId("5b4f29bed42ef3178d108e8a"), 
    "name" : "015", 
    "category" : "cat10", 
    "related_categories" : [
        {
            "_id" : "cat10", 
            "depthField" : NumberLong(0)
        }
    ]
}