CODEWITHSUNDEEP
mongodbtree
Zillon
Zillon

Reputation: 73

Mongodb: calculated connectToField inside graphlookup

I have mongo tree structure that looks like this:

{"parent": null, "path": "#a", "name": "a"}
{"parent": "a", "path": "#a#b", "name": "b"}
{"parent": "a", "path": "#a#c", "name": "c"}
{"parent": "b", "path": "#a#b#1", "name": "1"}
{"parent": "b", "path": "#a#b#2", "name": "2"}
{"parent": "c", "path": "#a#c#1", "name": "1"}
{"parent": "1", "path": "#a#c#1#x", "name": "x"}

which can be represented as follows:

#a
|_#a#b
|    |_#a#b#1
|    |_#a#b#2
|_#a#c
    |_#a#c#1
          |_#a#c#1#x

My goal is to build a request that will get only the leaves under a specified node.

Had I stored the parent path inside the field parent instead of the parent identifier I would have been able to do it using the following request:

db.tree.aggregate([
        {$match:{"parent": {$regex:"^#a#c"}}}, 
        {$graphLookup:{
                       from:"tree", 
                       startWith:"$path", 
                       connectFromField:"path", 
                       connectToField:"parent", 
                       as:"dep"}}, 
        {$match:{dep:[]}}, 
        {$project:{"_id":0, path:1}}
])

as already answered in my previous question here: Mongodb: get only leaves of tree

The problem is I did not.

So I have to somehow transform the 'connectToField' in my request so that it represents the path of my parent instead of the id of my parent. Does anybody have an idea on how to do this?

Upvotes: 0

Views: 598

Answers (1)

Alex Blex
Alex Blex

Reputation: 37128

connectToField is a name, not an expression. You can't do anything with it.

You really need to re-consider the schema. It is flawed in many ways starting from non-unique names used in parent references. Since you rely on the path string instead (which is a questionable decision by itself), you need a path to reference the parent.

The answer below does it runtime, and is hardly advisable for operational queries due to performance inefficiency and some assumptions of how the path is built. It can be used as a one-off request though.

Basically you need to create a view with calculated parent path:

db.createView("rootless_tree", "tree", [
    { $match: { parent: { $ne: null } } },
    { $addFields: {
        parent_path: { $let: {
            vars: { parents: { $split: [ "$path", "#" ] } },
            in: { $reduce: {
                input: { $slice: [ "$$parents", 1, { $subtract: [ { $size: "$$parents" }, 2 ] } ] },
                initialValue: "",
                in: { $concat: [ "$$value", "#", "$$this" ] }
            } }
        } }
    } }
]);

So then you can do your lookup as advised in your previous question:

db.tree.aggregate([
    { $graphLookup: {
        from: "rootless_tree", 
        startWith: "$path", 
        connectFromField: "path", 
        connectToField: "parent_path", 
        as:"dep"
    } },
    { $match: { dep: [] } },                       
])

Upvotes: 1

Related Questions