I have a collection users as follows:

{ "_id" : ObjectId("570557d4094a4514fc1291d6"), "email": "[email protected]", "user_type" : "1", "grade" : "A1", "room_id" : ObjectId("580557d4094a4514fc1291d6") }
{ "_id" : ObjectId("570557d4094a4514fc1291d7"), "email": "[email protected]", "user_type" : "2", "grade" : "A2", "room_id" : ObjectId("580557d4094a4514fc1291d6") }
{ "_id" : ObjectId("570557d4094a4514fc1291d8"), "email": "[email protected]", "user_type" : "3", "grade" : "A2", "room_id" : ObjectId("580557d4094a4514fc1291d6") }
{ "_id" : ObjectId("570557d4094a4514fc1291d9"), "email": "[email protected]", "user_type" : "2", "grade" : "A2", "room_id" : ObjectId("580557d4094a4514fc1291d7") }
{ "_id" : ObjectId("570557d4094a4514fc1291e6"), "email": "[email protected]", "user_type" : "3", "grade" : "A1", "room_id" : ObjectId("580557d4094a4514fc1291d7") }
{ "_id" : ObjectId("570557d4094a4514fc1291e7"), "email": "[email protected]", "user_type" : "3", "grade" : "A2", "room_id" : ObjectId("580557d4094a4514fc1291d7") }
{ "_id" : ObjectId("570557d4094a4514fc1291e8"), "email": "[email protected]", "user_type" : "2", "grade" : "A1", "room_id" : ObjectId("580557d4094a4514fc1291d8") }
{ "_id" : ObjectId("570557d4094a4514fc1291e9"), "email": "[email protected]", "user_type" : "3", "grade" : "A1", "room_id" : ObjectId("580557d4094a4514fc1291d8") }

I want to find email ids of users of type 2 having grade A2, along with their roommates having the same room_id but user_type 3 (grade does not matter for roommates). So the result data should look like this:

{"email": "[email protected]", "roommates": [{"email": "[email protected]"}]}
{"email": "[email protected]", "roommates": [{"email": "[email protected]"}, {"email": "[email protected]"}]}

How do I do this in MongoDB? I have a background in SQL so I am thinking of a self join, but I guess there are other ways to do it.

CodePudding user response：

Yes, the (concept/direction) of self-join users collection is correct.

1. $lookup - Join users collection by room_id and return roommates array.

2. $match - Filter the document by user_type, grade and roommates.user_type.

3. $project - Decorate the output document.

   3.1. $map - Iterate the roommates array and returns an array.

   3.1.1. $filter - Filter the document with user_type in roommates array.

db.users.aggregate([
  {
    $lookup: {
      from: "users",
      localField: "room_id",
      foreignField: "room_id",
      as: "roommates"
    }
  },
  {
    $match: {
      user_type: "2",
      grade: "A2",
      "roommates.user_type": "3"
    }
  },
  {
    $project: {
      email: 1,
      roommates: {
        $map: {
          input: {
            $filter: {
              input: "$roommates",
              cond: {
                $eq: [
                  "$$this.user_type",
                  "3"
                ]
              }
            }
          },
          in: {
            email: "$$this.email"
          }
        }
      }
    }
  }
])

Sample Mongo Playground