mongodb - 在 mongodb 中执行三个集合的连接？

Question

我正在使用 $lookup 来加入 mongoDB 中的不同集合。现在我遇到了一个问题，假设我有下面给出的 3 个集合。

用户电影

{
    "_id": ObjectId("5834ecf7432d92675bde9d83"),
    "mobile_no": "7941750156"
    "movies" : ["dallas00", "titanic00", "green_mile00"]
}

电影

{
    "_id" : ObjectId("4834eff7412d9267556d9d52"),
    "movie_name" : "Dallas Buyer's Club",
    "movie_id": "dallas00",
    "active": 0

}

电影评论

{
    "_id": ObjectId("1264eff7412d92675567h576"),
    "movie_id" : "dallas00",
    "comment": "what a great movie.",
    "time_posted": "1480516635131"
},
{
    "_id": ObjectId("1264eff7412d92675567h578"),
    "movie_id" : "dallas00",
    "comment": "awesome movie.",
    "time_posted": "1480516635141"
},
{
    "_id": ObjectId("1264eff7412d92675567h567"),
    "movie_id" : "titanic00",
    "comment": "titanic awesome movie.",
    "time_posted": "1480516635132"
},
{
    "_id": ObjectId("1264eff7412d92675567h579"),
    "movie_id" : "green_mile00",
    "comment": "Tom hanks did awesome movie.",
    "time_posted": "1480516635133"
}

用户电影是我存储用户喜欢的电影的集合，电影是我存储有关电影的所有详细信息的集合，而 movie_comments 是我存储与电影相关的评论的集合。

现在我想编写一个查询，向用户展示他们喜欢的电影列表，但所有电影都必须有 "active": 1 以及与之相关的评论特别的电影。现在我尝试使用 $lookup 我的代码如下。

db.user_movies.aggregate(
        {$match : {mobile_no : mobile_no}},
        { "$unwind": "$movies" },
        {$lookup: {from: "movies",localField: "movies",foreignField: "movie_id",as: "bmarks"}},
        {$unwind : "$bmarks"},
        {$match : {"bmarks.active": 1}},
        { $group : { _id : "$_id", movies : {$push : "$bmarks"}, movie_ids: {$push : "$bmarks.movie_id"}}},
        {$lookup: {from: "movie_comments", localField: "",foreignField: "movie_id",as: "comments"}},
        {$unwind : "$comments"},
        {$sort: {time_posted: -1}},
        {$group: {_id: '$_id', comments : {$push : "$comments"}}},

我正在尝试编写一个查询来执行所有这些功能，并且我能够对前两个集合(即 user_movies 和 movie)执行联接，但我无法在同一查询中对第三个集合执行查找。我想要的是发送我的第一次查找的输出数组，即movie_ids，并将其发送到movie_comments的下一次查找。这样我就可以在我的 movie_ids 数组中包含与电影相关的所有评论。

现在谁能告诉我为什么我没有得到任何输出，我可以像第二次使用它一样使用查找(使用 localfield 作为 $group 字段)，或者不是我如何在单个查询中执行此功能。

更新- 现在只剩下一件事了，我想以排序的方式获取结果，这样电影就会像它们出现在“user_movies”数组中一样排序(根据它们在数组中的位置) )，相应的评论将按照“发布时间”降序排列。

因此，对于上述文档，我希望我的输出如下

{
     "movie_id": "dallas00"  // dallas00 is first because it appear first in "user_movies" movies array.
     "movie_name": "Dallas Buyer's Club",
     "comments": ["what a great movie.","awesome movie."]
},
{
     "movie_id": "titanic00"  // titanic00 is second because it appear second in "user_movies" movies array.
     "movie_name": "Titanic",
     "comments": ["titanic awesome movie."]
},
{
     "movie_id": "green_mile00"  // green_mile00 is third because it appear third in "user_movies" movies array.
     "movie_name": "Green mile",
     "comments": ["Tom hanks did awesome movie."]
},

好的最后一件事，如果可以获取当我执行第一次查找时匹配的电影数组字符串的位置，那么我可以根据我将给出的权重进行排序(对先前位置的权重更大，依此类推)。所以现在我要做的是 {{$sort: { 'movie_weight': -1, 'time_posted': -1}}。现在我将对结果进行排序，以便电影将根据其在数组 "user_movies" 数组中的位置进行排序，评论将根据与其对应的 "time_posted" 进行排序电影。

更新代码

{$match : {mobile_no : mobile_no}},
        {$unwind: { path: "$movies", includeArrayIndex: "movieposition"}},
        {$lookup: {from: "movies",localField: "movies",foreignField: "movie_id",as: "bmarks"}},
        {$unwind : "$bmarks"},
        {$match : {"bmarks.active": 1}},
        {$group : { "_id" : "$bmarks.movie_id", movie_names : {$push : "$bmarks.movie_name"}, movie_ids: {$push : "$bmarks.movie_id"}, movie_position: {$push : "$movieposition"}}},
        {$unwind : "$movie_ids"},
        {$unwind : "$movie_names"},
        {$lookup: {from: "movie_comments", localField: "movie_ids", foreignField: "movie_id", as: "comments"}},
        {$unwind : "$comments"},
        {$sort: { 'movie_position': -1, 'comments.time_posted': -1 }},
        {$group: {_id: { movie_id: '$comments.movie_id', movie_name: '$movie_names' }, cmnts_ids: {$push: '$comments._id'}}},
        {$project: {_id: 0, movie_id: '$_id.movie_id', movie_name: '$_id.movie_name', tot_cmnts: {"$size" : "$cmnts_ids"}, top_cmnts_ids: {$slice: ['$cmnts_ids', 0, 4]}}}},
        {$skip: page*page_size},
        {$limit: page_size}, */

Answer 1

好吧，看起来你有很多问题。我会尝试总结一下。

问题 1:您提供的聚合代码无法编译。

db.user_movies.aggregate(
{$match : {mobile_no : mobile_no}},
{$unwind: "$movies" },
{$lookup: {from: "movies",localField: "movies",foreignField: "movie_id",as: "bmarks"}},
{$unwind : "$bmarks"},
{$match : {"bmarks.active": 1}},
{$group : { _id : "$_id", movies : {$push : "$bmarks"}, movie_ids: {$push : "$bmarks.movie_id"}}},
{$lookup: {from: "movie_comments", localField: "",foreignField: "movie_id",as: "comments"}},
{$unwind : "$comments"},
{$sort: {time_posted: -1}},
{$group: {_id: '$_id', comments : {$push : "$comments"}}},

修复:

db.user_movies.aggregate([
{$match : {mobile_no : mobile_no}},
{$unwind: "$movies" },
{$lookup: {from: "movies",localField: "movies",foreignField: "movie_id",as: "bmarks"}},
{$unwind : "$bmarks"},
{$match : {"bmarks.active": 1}},
{$group : { _id : "$_id", movies : {$push : "$bmarks"}, movie_ids: {$push : "$bmarks.movie_id"}}},
{$lookup: {from: "movie_comments", localField: "",foreignField: "movie_id",as: "comments"}},
{$unwind : "$comments"},
{$sort: {time_posted: -1}},
{$group: {_id: '$_id', comments : {$push : "$comments"}}}])

现在假设您使用的数据与您提供的数据相同。

问题 2:{$match : {"bmarks.active": 1}} 与任何条目都不匹配。

修复:{$match:{"bmarks.active": 0}}

问题 3:未定义查找字段{$lookup: {from: "movie_comments", localField: "",foreignField: "movie_id",as: "comments"}}

修复:{$lookup: {from: "movie_comments", localField: "movie_ids",foreignField: "movie_id",as: "comments"}}

问题 4:之前查找的 movie_ids 没有展开阶段

修复:{$unwind:“$movie_ids”}

问题 5:没有发布时间字段{$sort: {time_posted: -1}}

修复:排序前包含字段

因此，要将所有内容放在一起，您需要将其聚合为如下所示，以提取每部电影的评论。

db.user_movies.aggregate([
{$match : {mobile_no : mobile_no}},
{$unwind: "$movies"},
{$lookup: {from: "movies",localField: "movies",foreignField: "movie_id",as: "bmarks"}},
{$unwind : "$bmarks"},
{$match : {"bmarks.active": 0}},
{$group : { _id : "$_id", movies : {$push : "$bmarks"}, movie_ids: {$push : "$bmarks.movie_id"}}},
{$unwind : "$movie_ids"},
{$lookup: {from: "movie_comments", localField: "movie_ids",foreignField: "movie_id",as: "comments"}},
{$unwind : "$comments"},
{$group: {_id: '$_id', comments : {$push : "$comments"}}}])

示例输出

{
    "_id": ObjectId("5834ecf7432d92675bde9d83"),
    "comments": [{
        "_id": ObjectId("583d96d7e35f6e9c53c9e894"),
        "movie_id": "dallas00",
        "comment": "what a great movie."
    }, {
        "_id": ObjectId("583d96d7e35f6e9c53c9e895"),
        "movie_id": "dallas00",
        "comment": "awesome movie."
    }]
}

更新:

db.user_movies.aggregate([
{$match : {mobile_no : mobile_no}},
{$unwind: {path: "$movies", includeArrayIndex: "moviePosition"}},
{$sort :  {moviePosition:1}},
{$lookup: {from: "movies",localField: "movies",foreignField: "movie_id",as: "bmarks"}},
{$unwind :"$bmarks"},
{$group : {_id : "$_id", movies : {$push : {movie_name:"$bmarks.movie_name", movie_id:"$bmarks.movie_id"} }}},
{$unwind : "$movies"},
{$lookup: {from: "movie_comments", localField: "movies.movie_id",foreignField: "movie_id",as: "comments"}},
{$unwind : "$comments"},
{$group:  {_id: "$movies.movie_id", movie_name: {$first:"$movies.movie_name"}, comments : {$push : {comment:"$comments.comment", time_posted:"$comments.time_posted"}}}},
{$sort :  {time_posted:-1}},
{$project:{_id:0, movie_id:"$_id", movie_name:1, comments: "$comments.comment"}}
]).pretty();