CODEWITHSUNDEEP
mongodbaggregation-framework
triples13
triples13

Reputation: 216

lookup for tree hierarchy in mongo

i have collections which represent a tree hiearchy meta, site and geolocation collections, one meta can have multiple sites and one site can have multiple geolocation

meta collection

{
    "_id": "1",
    "meta_id": 1,
    "meta_name": "yankung"
}

site collection

{
    "_id": "1",
    "meta_id": 1,
    "site_id" :2,
    "site_name": "uoop"
}

geo collection

{
    "_id": "1",
    "site_id": 2,
    "geo_id" :3,
    "geo_name": "toop"
}

i have to get the final result like this

{
  "_id": "1",
  "meta_id": 1,
  "meta_name": "yankung",
  "sites": [
    {
      "site_id": 2,
      "site_name": "uoop",
      "geos:": [
        {
          "geo_id": 3,
          "geo_name": "toop"
        },
        {
          "geo_id": 4,
          "geo_name": "toop"
        }
      ]
    },
    {
      "site_id": 1000,
      "site_name": "uoop",
      "geos:": [
        {
          "geo_id": 5,
          "geo_name": "toop"
        },
        {
          "geo_id": 6,
          "geo_name": "toop"
        }
      ]
    }
  ]
}

i tried using aggregation query with lookup and unwind was able to segregate sites and geos as list , thought of getting the required result from application level, but would have to iterate through each document and add which will increase the time complexity, any help on how should i proceed?

this is what i was able to achieve

{
  "_id": "1",
  "meta_id": 1,
  "meta_name": "yankung",
  "sites": [
    { "site_id": 2, "site_name": "uoop"
    },
    {"site_id": 1000,"site_name": "uoop"
    }
      ],
   "geos:": [
        { "geo_id": 5,"geo_name": "toop"
        },
        {"geo_id": 6,"geo_name": "toop"
        }
  ]
}

Upvotes: 0

Views: 386

Answers (1)

matthPen
matthPen

Reputation: 4343

The trick is to use $lookup with join conditions and uncorrelated subqueries. By this way you can define $lookup inside $lookup.

Here's the query :

db.meta_collection.aggregate([
  {
    $lookup: {
      from: "site_collection",
      let: {
        meta: "$meta_id"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                "$meta_id",
                "$$meta"
              ]
            }
          }
        },
        {
          $lookup: {
            from: "geo_collection",
            let: {
              site: "$site_id"
            },
            pipeline: [
              {
                $match: {
                  $expr: {
                    $eq: [
                      "$site_id",
                      "$$site"
                    ]
                  }
                }
              },
              
            ],
            as: "geos"
          }
        }
      ],
      as: "sites"
    }
  }
])

You can test it here

Upvotes: 1

Related Questions