Join two collection in mongodb

Question

I'm new in mongodb. Could you please tell me how to perform join operation in this. I've two collection:

Collection 1 ("user")

{
 _id: "d04d53dc-fb88-433e-a1c5-dd41a68d7655",
 userName: "XYZ User",
 age: 12
}

Collection 2 ("square")

{
 _id: "ef6f6ac2-a08a-4f68-a63c-0b4a70285427",
 userId: "d04d53dc-fb88-433e-a1c5-dd41a68d7655",
 side: 4,
 area: 16
}

Now I want to retrieve the data from collection 2 is like this. Expected output:

{
 _id: "ef6f6ac2-a08a-4f68-a63c-0b4a70285427",
 userId: "d04d53dc-fb88-433e-a1c5-dd41a68d7655",
 userName: "XYZ User",
 side: 4,
 area: 16
}

Thanks in advance :)

score 4 · Accepted Answer · answered May 19 '22 at 21:06

Here's one way to do it.

db.square.aggregate([
  {
    "$lookup": {
      "from": "user",
      "localField": "userId",
      "foreignField": "_id",
      "as": "userDoc"
    }
  },
  {
    "$set": {
      "userName": {
        "$first": "$userDoc.userName"
      }
    }
  },
  { "$unset": "userDoc" }
])

Try it on mongoplayground.net.

score 2 · Answer 2 · edited May 31 '22 at 07:33

You can keep the first documentid (_id) in the second document as userId for refrence and after that, you can use the join feature supported by MongoDB 3.2 and later versions. You can use joins by using an aggregate query. You can do it using the below example :

    db.user.aggregate([

    // Join with square table
    {
        $lookup:{
            from: "square",       // other table name
            localField: "_id",   // name of user table field
            foreignField: "userId", // name of square table field
            as: "square"         // alias for userinfo table
        }
    },
    {   $unwind:"$user_info" },     // $unwind used for getting data in object or for one record only

     
    // define some conditions here 
    {
        $match:{
            $and:[{"userName" : "XYZ User"}]
        }
    },

    // define which fields are you want to fetch
    {   
        $project:{
            _id: 1,
            userId: "$square.userId",
            userName: 1,
            side: "$square.side",
            area: "$square.area"
        } 
    }
]);

The Result will be

     {
      _id: "ef6f6ac2-a08a-4f68-a63c-0b4a70285427",
      userId: "d04d53dc-fb88-433e-a1c5-dd41a68d7655",
      userName: "XYZ User",
      side: 4,
      area: 16
     }

Cheers

Join two collection in mongodb

2 Answers2