I have two collections:

users - profiles of all users

partnership - users liked each other.

How to find for a given user (use "rrr" as the user for this example)

• number of mutual likes
• profiles of mutual likers

The expected output is count of 2 AND profiles of users "abc" and "eee" because both these users have mutually liked "rrr".

Here is the db

db={
  users: [
    {
      _id: "abc",
      name: "abc",
      group: 1
    },
    {
      _id: "xyz",
      name: "xyyy",
      group: 1
    },
    {
      _id: "123",
      name: "yyy",
      group: 1
    },
    {
      _id: "rrr",
      name: "tttt",
      group: 1
    },
    {
      _id: "eee",
      name: "uuu",
      group: 1
    }
  ],
  partnership: [
    {
      _id: "abc_123",
      fromUser: "abc",
      toUser: "123"
    },
    {
      _id: "eee_rrr",
      fromUser: "eee",
      toUser: "rrr"
    },
    {
      _id: "rrr_abc",
      fromUser: "rrr",
      toUser: "abc"
    },
    {
      _id: "abc_rrr",
      fromUser: "abc",
      toUser: "rrr"
    },
    {
      _id: "xyz_rrr",
      fromUser: "xyz",
      toUser: "rrr"
    },
    {
      _id: "rrr_eee",
      fromUser: "rrr",
      toUser: "eee"
    },
    
  ]
}

https://mongoplayground.net/p/jnW6fPOTd8I

CodePudding user response：

One option is to start from the partnership collections:

1. Keep only documents that contains rrr
2. $group to find all its partners
3. Find the set intersection between its froms and tos, these are the mutual partners.
4. $lookup to bring the users data
5. Add count

db.partnership.aggregate([
  {$match: {$or: [{fromUser: "rrr"}, {toUser: "rrr"}]}},
  {$group: {
      _id: 0,
      from: {$addToSet: "$fromUser"},
      to: {$addToSet: "$toUser"}
    }
  },
  {$project: {
      _id: 0,
      users: {
        $filter: {
          input: {$setIntersection: ["$from", "$to"]},
          cond: {$ne: ["$$this", "rrr"]}
        }
      }
    }
  },
  {$lookup: {
      from: "users",
      localField: "users",
      foreignField: "_id",
      as: "users"
    }
  },
  {$project: {users: 1, count: {$size: "$users"}}}
])

See how it works on the playground example