How to join more than 2 collections in mongodb?

Question

I have got 3 collections: groups, users, and members. Groups collection contains group specific details, users collection contains user-specific details and members collection contains the association of users with groups.

For example:

Groups:

id                                      |   name 
ObjectId("5ee5e346fae4a21e28a81d91")    |   Housemates 
ObjectId("5ee5e346fae4a21e28a81d92")    |   Co-workers


Users:

id                                      |   name
ObjectId("5ee493b0989d0f271cdc41c1")    |   Joulie
ObjectId("5ee493b0989d0f271cdc41c3")    |   Newelle
ObjectId("5ee493b0989d0f271cdc41c5")    |   John
ObjectId("5ee493b0989d0f271cdc41c7")    |   Larry

Members:
group_id                                | user_id  
ObjectId("5ee5e346fae4a21e28a81d91")    | ObjectId("5ee493b0989d0f271cdc41c1")
ObjectId("5ee5e346fae4a21e28a81d91")    | ObjectId("5ee493b0989d0f271cdc41c3")
ObjectId("5ee5e346fae4a21e28a81d92")    | ObjectId("5ee493b0989d0f271cdc41c5")
ObjectId("5ee5e346fae4a21e28a81d92")    | ObjectId("5ee493b0989d0f271cdc41c7")

I want to join these three collections and get user details for each group with the group name.

   Expected Output:
    [
        { "group_name":"Housemates", 
          "user_info": [
            {"name":"Joulie"},
            {"name":"Newelle"}
            ]
        },
        { "group_name":"Co-workers", 
          "user_info": [
            {"name":"John"},
            {"name":"Larry"}
            ]
        }
   ]

I've written a query to get the output as above but it's not working:

db.members.aggregate([
      {
        $lookup : {
          from: 'users',
          localField: "user_id",
          foreignField: "_id",
          as: "user_info"
        }
      },{
        $lookup: {
            from: 'groups',
            localField: "group_id",
            foreignField: "_id",
            as: "group_info"
        }
      }
    ]);

This question looks similar and I have tried the solution from it as well, but it does not seem to work for me. I would really appreciate any help or guideline. Thank you in advance!

Answer 1

You have the right idea, we just need to reconstruct the data after the lookups:

db.members.aggregate([
  {
    $lookup: {
      "from": "groups",
      "localField": "group_id",
      "foreignField": "_id",
      as: "groups"
    }
  },
  {
    $lookup: {
      "from": "users",
      "localField": "user_id",
      "foreignField": "_id",
      as: "users"
    }
  },
  {
    $unwind: "$groups"
  },
  {
    $unwind: "$users"
  },
  {
    $group: {
      _id: "$groups._id",
      group_name: {
        $first: "$groups.name"
      },
      user_info: {
        $addToSet: {
          name: "$users.name"
        }
      }
    }
  }
])

Mongo Playground