Mongodb聚合-首先创建项列表并获取项的交集

Question

我有如下文档，

{
    "_id" : ObjectId("5539d45ee3cd0e48e99c3fa6"),
    "userId" : 1,
    "movieId" : 6,
    "rating" : 2.0000000000000000,
    "timestamp" : 9.80731e+008
}

然后，我需要获得给定两个用户(如userId:1和userId:2(的公共(相交(项

例如

{
    "_id" : ObjectId("5539d45ee3cd0e48e99c1fa7"),
    "userId" : 1,
    "movieId" : 22,
    "rating" : 3.0000000000000000,
    "timestamp" : 9.80731e+008
},
{
    "_id" : ObjectId("5539d45ee3cd0e48e99c1fa8"),
    "userId" : 1,
    "movieId" : 32,
    "rating" : 2.0000000000000000,
    "timestamp" : 9.80732e+008
},

{
    "_id" : ObjectId("5539d45ee3cd0e48e99c1fa9"),
    "userId" : 2,
    "movieId" : 32,
    "rating" : 4.0000000000000000,
    "timestamp" : 9.80732e+008
},
{
    "_id" : ObjectId("5539d45ee3cd0e48e99c1fa3"),
    "userId" : 2,
    "movieId" : 6,
    "rating" : 5.0000000000000000,
    "timestamp" : 9.80731e+008
}

那么我需要得到[6,32]的结果我试过这样做，

aggregate([{"$match":{"$or":[{"userId":2},{"userId":1}]}},{"$group":{"_id":"$userId","movie":{"$addToSet":"$movieId"}}}])

但不起作用。

我该怎么做？

Answer 1

试试这个：

db.movies.aggregate(
  // Limit rating records to the relevant users
  {$match:{userId:{$in:[1,2]}}},
  // For each movie rated by either user, keep track of how many users rated the movie.
  {$group:{_id:'$movieId',users:{$sum:1}}},
  // Restrict the result to only movies rated by both users.
  {$match:{users:2}}
)

Answer 2

使用set运算符可以获得所需的结果，过滤掉同一用户/电影对的可能重复条目：

db.collection.aggregate([
  {$match: {"$or":[{"userId":2},{"userId":1}]}},
  {$group: {_id: "$movieId", users: {$addToSet: "$userId"}}},
  {$project: { movieId: "$_id", _id: 0, allUsersIncluded: { $setIsSubset: [ [1,2], "$users"]}}},
  {$match: { allUsersIncluded: true }},
  {$group: { _id: null, movies: {$addToSet: "$movieId"}}}
])

生产，举个例子：

{ "_id" : null, "movies" : [ 32, 6 ] }

• 第一个$match阶段将只保留用户1或2的文档
• 第一CCD_ 2阶段将使用CCD_
• 此时，所有文档在users中都具有[1]、[2]、[1,2]或[2,1]。用$setIsSubset筛选出$project/$match阶段的前两种情况
• 最后，我只需要把所有的电影都放在一个片场里