Perform joins in mongodb with three collections?

Question

I am using $lookup for joining different collections in mongoDB. Now i am facing a problem here suppose i have 3 collections given below.

user_movies

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

movies

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

}

movie_comments

{
    "_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"
}

User Movies is a collection where i am storing user liked movies, Movies is a collection where i am storing all details about the movie and movie_comments is the collection where i am storing comments associated to the movie.

Now i want to write a query where i am going to show users the list of their movies they have liked but all movies must have "active" : 1, and also the comments associated to that particular movie. Now i tried to use $lookup my code is given below.

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"}}},

I am trying to write a single query for doing all this functionality and i am able to perform join on first two collections that is user_movies and movies but i am not able to perform lookup on third collection in the same query. What i want is to send output array of my first lookup which is movie_ids and send it to next lookup with movie_comments. So that i can have all comments associated with the movie i have in my movie_ids array.

Now can anyone please tell me why i am not getting any output, can i use lookup like i am using it second time (which is using localfield as a $group field) or not than how can i perform this functionality in a one single query.

Update- Now there is only one thing left, i want to get results in a sorted manner such that movies will be sorted like they appear in "user_movies" array(according to their position in array) and their corresponding comments will sorted according to 'timeposted' in decreasing order.

So for above documents i want my output to be like

{
     "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."]
},

Ok One Last thing if can get position of movies array string which is matched when i perform first lookup then i can sort according to weight which i will give(more weight to previous position and so on). So now what i will do is {{$sort: { 'movie_weight': -1, 'time_posted': -1}}. Now i will get my results sorted such that movies will be sorted according to their position in array "user_movies" array and comments will be sorted according to "time_posted" corresponding to their movies.

Updated code

{$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

Okay, looks like you've bunch of problems. I'll try to sum them up.

Problem 1 : the aggregate code you've provided doesnt compile.

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"}}},

Fix:

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"}}}])

Now assume you're using the same data you have provided.

Problem 2 : {$match : {"bmarks.active": 1}} doesnt match any entry.

Fix : {$match : {"bmarks.active": 0}}

Problem 3 : No look up field defined {$lookup: {from: "movie_comments", localField: "",foreignField: "movie_id",as: "comments"}}

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

Problem 4 : No unwind stage for movie_ids for previous look up

Fix : {$unwind : "$movie_ids"}

Problem 5 : No time posted field {$sort: {time_posted: -1}}

Fix : include field before sorting

So, to put everything together you need aggregate to look like something below for extracting the comments for each movie.

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"}}}])

Sample Output

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

Update:

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();