Mongodb recursive query

Question

I have the following schema in my taxon collection :

{ 
  "_id": 1, 
  "na": [ "root_1",
        "root_2",
        "root_3" ], 
  "pa": 1 
},{
  "_id": 2, 
  "na": [ "name_1", 
        "name_2", 
        "name_3"], 
  "pa": 1
},{
  "_id": 4, 
  "na": [ "otherName_1", 
        "otherName_2", 
        "otherName_3"],
  "pa": 2
}

Each document is related to another by the parent field, which correspond to the _id of it's parent.

I would like to perform a recursive search to get the following result:

{ "_id": 4, 
  "nameList": [ "otherName_1",
              "name_1",
              "root_1"]
}

From document with a certain _id, get the first item of na array of each parent until document with _id: 1 is reached

I currently get this result by performing X queries (one by parent document, here 3 for example), but I'm pretty sure that this can be achieved using a single query. I already looked at the new $graphLookup operator, but couldn't manage to get my way with it...

Is it possible to achieve this in a single query using MongoDB 3.4.1?

Edit

I would run this for 50 documents each time, so the optimal solution would be to combine everything in a single query

for example, it would looks like

var listId = [ 4, 128, 553, 2728, ...];
var cursor = db.taxon.aggregate([
  {$match: 
     { _id: {$in: listId}}
  }, ...
)];

and would output :

[{ "_id": 4, 
  "nameList": [ "otherName_1",
              "name_1",
              "root_1"]
}, { "_id": 128, 
  "nameList": [ "some_other_ame_1",
              "some_name_1",
              "root_1"]
}, { "_id": 553, 
  "nameList": [ "last_other_ame_1",
              "last_name_1",
              "root_1"]
} ... ]

try it online: mongoplayground.net/p/Gfp-L03Ub0Y

s7vr · Accepted Answer · 2017-03-17 12:26:03Z

21

+50

You can try below aggregation.

Stages $match - $graphLookup - $project.

$reduce to pick the first element from the each of $graphLookup nameList's na array.

db.taxon.aggregate([{
    $match: {
        _id: {
            $in: listId
        }
    }
}, {
    $graphLookup: {
        from: "taxon",
        startWith: "$_id",
        connectFromField: "pa",
        connectToField: "_id",
        as: "nameList"
    }
}, {
    $project: {
        nameList: {
            $reduce: {
                input: "$nameList",
                initialValue: [],
                in: {
                    "$concatArrays": ["$$value", {
                        $slice: ["$$this.na", 1]
                    }]
                }
            }
        }
    }
}])

edited Mar 17, 2017 at 12:26

answered Mar 17, 2017 at 11:08

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

felix Over a year ago

this is a very nice solution, thanks a lot! Would it be possible for several documents in the same time ? (see my edit for details)

s7vr Over a year ago

You are welcome. Updated answer. It should work with any matching condition.

felix Over a year ago

Ok my bad I tried with $in but forgot to iterate over the cursor... This is exactly what I need, just need to run some performance test to see the actual gain! Do you have any advise on indexes for such a query ?

Collectives™ on Stack Overflow

Mongodb recursive query

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related