CODEWITHSUNDEEP
mongodb
anamar
anamar

Reputation: 380

MongoDB recursive query top parent object

I have a collection of locations and want to retrieve a top parent location. For example, if I query for location by id 4, location with id 1 should be returned.

{_id: "1", "parentLocationID": null, "childLocationIds": [2]}
{_id: "2", "parentLocationID": "1", "childLocationIds": [3]}
{_id: "3", "parentLocationID": "2", "childLocationIds": [4]}
{_id: "4", "parentLocationID": "3", "childLocationIds": []}

My current solution is to recursively query db from backend until parentLocationID is null. What I'd like to do is move recursion processing to Mongo. I found that Mongo supports recursive queries with $graphLookup, and this is how I started:

db.locations.aggregate([
    {
        "$match": {
            "childLocationIds": {"$elemMatch": {"$eq", "4"}}
        }
    },
    {
        "$graphLookup": {
            "from": "locations",
            "startWith: "$childLocationIds",
            "connectFromField": "childLocationIds",
            "connectToField": "childLocationIds",
            "as": "parentLocations"
        }
    }
])

Query only returns first level parent. Do you have any suggestions?

Upvotes: 0

Views: 1539

Answers (1)

anamar
anamar

Reputation: 380

Found the solution, a couple of more pipelines required:

db.locations.aggregate([
    {
        "$match": {
            "_id": "4"
        }
    },
    {
        "$graphLookup": {
            "from": "locations",
            "startWith: "$parentLocationID",
            "connectFromField": "_id",
            "connectToField": "parentLocationID",
            "as": "parentLocations"
        }
    },
    {
        "$unwind": "$parentLocations"
    },
    {
        "$replaceRoot": {
            "newRoot": "$parentLocations"
        }
    },
    {
        "$match": {
            "parentLocationID": null
        }
    }
])

$match will find location with id 4, then $graphLookup will append parent objects to parentLocations property. Remaining pipelines are used to extract top level location.

Upvotes: 2

Related Questions