How to use MongoDB aggregation to find common items between one document and all others?

Question

Now there is a likes collection, and the information recorded in it is as follows.

// 1
{
    "_id": ObjectId("1"), 
    "masterid": ObjectId("45678"), // user _id of the owner of the moment
    "momentid": ObjectId("123456"),// moment id
    "userid": ObjectId("123456"), // User _ID of the liker
    "avatar": "1660531471959.jpg", // avatar of the liker
    "username": "userA", // usernameof the liker
    "timestamp": NumberInt("1677072930") // Timestamp of the like
}

// 2
{
    "_id": ObjectId("63f61d8d99b4ee0004e76e03"),
    "masterid": ObjectId("45678"),
    "momentid": ObjectId("123456"),
    "userid": ObjectId("45678"),
    "avatar": "defaultAvatar.jpg",
    "username": "userB",
    "timestamp": NumberInt("1677073805")
}

For example, User A likes Moments S and X, while User B likes Moment S and User C likes Moments S, X, and Y. All of them have moments that they both like. I want to query which users have common likes with User A and who has the most common likes with User A.

I want to find other users who have liked the same moments as user A, and then determine the number of moments that they have both liked, and finally find out who among them has liked the most moments in common with user A.

Please help me, this problem has been bothering me for a long time

I have tried the following approach, but it was not successful

db.likes.aggregate([

  { $match: { userid: ObjectId("id") } },

  {
    $group: {
      _id: "$momentid",
      count: { $sum: 1 },
      users: { $addToSet: "$userid" }
    }
  },
  
  {
    $lookup: {
      from: "likes",
      let: { users: "$users", momentid: "$_id" },
      pipeline: [
        { $match: { $expr: { $and: [
          { $eq: [ "$momentid", "$$momentid" ] },
          { $ne: [ "$userid", ObjectId("id") ] },
          { $in: [ "$userid", "$$users" ] }
        ] } } },
        { $project: { _id: 0, userid: 1 } }
      ],
      as: "otherLikes"
    }
  },
 
  {
    $project: {
      _id: "$moment.userid",
      count: { $size: "$otherLikes.userid" },
      users: "$otherLikes.userid"
    }
  },
  
  { $sort: { count: -1 } }
])

nimrod serok · Answer 1 · 2023-02-26T10:31:37.217

One option is to use $setWindowFields for this:

$group by users
Mark the wanted user as type 0 and all other users with type 1
Use $setWindowFields to set the wanted user's moments to all other users as chosenUser
Remove the wanted user itself
Find for each user the common moments with the wanted user.
$sort by number of common moments

db.collection.aggregate([
  {$group: {_id: "$userid", moments: {$push: "$momentid"}}},
  {$set: {type: {$cond: [{$eq: ["$_id", ObjectId("id")]}, 0, 1]}}},
  {$setWindowFields: {
      sortBy: {type: 1},
      output: {chosenUser: {
          $first: "$moments",
          window: {range: [-1, 0]}
      }}
  }},
  {$skip: 1},
  {$project: {common: {$setIntersection: ["$chosenUser", "$moments"]}}},
  {$set: {commonSize: {$size: "$common"}}},
  {$sort: {commonSize: -1}}
])

See how it works on the playground example

How to use MongoDB aggregation to find common items between one document and all others?

1 Answers1