how can I query multiple table to get the data from collections in mongodb using pymongo-CodePudding

I have 5 collections

user : { "id": 1}

group: { "gid": 13, "name": "group1"}

subgroup: {"sgid": 22, "gid": 13, "name": "sgroup1"}

maps : {"userId": 1, "id": 10, "mapId": 13}

data: {"sgid": 22, "somedata": "somedata"}

I have the current user id, now using userId I need to get mapId from maps, using mapId get the sgid from subgroup, from sgid get the data, along with the data i need groupname and subgroup name.

CodePudding user response：

I think you need something like this:

The trick basically is to use the $lookup to do the "join" and additionally $unwind to deconstruct the result (also $lookup also works with an array).

And the last stage is $project to get only values you want.

db.user.aggregate([
  {
    "$lookup": {
      "from": "maps",
      "localField": "id",
      "foreignField": "userId",
      "as": "map"
    }
  },
  {
    "$unwind": "$map"
  },
  {
    "$lookup": {
      "from": "subgroup",
      "localField": "map.mapId",
      "foreignField": "gid",
      "as": "subgroup"
    }
  },
  {
    "$unwind": "$subgroup"
  },
  {
    "$lookup": {
      "from": "data",
      "localField": "subgroup.sgid",
      "foreignField": "sgid",
      "as": "data"
    }
  },
  {
    "$unwind": "$data"
  },
  {
    "$lookup": {
      "from": "group",
      "localField": "subgroup.gid",
      "foreignField": "gid",
      "as": "group"
    }
  },
  {
    "$unwind": "$group"
  },
  {
    "$project": {
      "data": "$data.somedata",
      "groupname": "$group.name",
      "subgroupname": "$subgroup.name"
    }
  }
])

Example here

By the way, if you need 4 ´$lookup` maybe Mongo is not the best DB (but this is only an opinion).