aggregating multiple fields from documents in separate collections using MongoDB

Question

I used below code to display result of two fields of separate collections: InfoMovie and Rate, having MID as common field between them. I want a resultset that displays "Actors" from collection "InfoMovie" and "remark" from collection "Rate" into one resltset using Mongodb. But when i execute below code, it just displays the list of "remark" but it doesnt show the remarked actors name. [Note: all the actors in the collection aren't remarked.Few of them are remark. We have to display the remarked actor's name only.] I would be very thankful if anyone could solve this. Thank you in advance! :)

db.InfoMovie.aggregate([
 {
    $lookup: 
    { 
        from:"Rate",
        localField:"MID", 
        foreignField:"MID", 
        as:"ActorsRating"
    }
},
    {
        $project:{"Remark":1,"Actors":1}
    }
])

Below are the sample documents: sample document of collection infoMovie :

{
    "MID":"1",
    "MovieName":"Iron man",
    "Actors":"RDJ",
    "RDate":"2008",
    "Country":"USA",
}, 

{
    "MovieID":"2",
    "MovieName":"Dark Knight",
    "Actors":"Christian Bail",
    "RDate":"2007",
    "Country":"USA",
}

The mongod version that i am using is 3.4.7. Thank You !

sample document of collection Rate:

{
    "MID":1,
    "ReviewedBy":"John",
    "Rate":4,
    "Date":"10/04/2013",
    "remark":"The best Movie ever!",
},
{
    "MID":2,
    "ReviewedBy":"William",
    "Rate":8,
    "Date":"19/06/2014",
}     

Answer 1

You can try below aggregation

You need to use $filter aggregation to filter out the documents where Remark is equal to undefined.

db.InfoMovie.aggregate([
  { "$lookup": { 
    "from":"Rate",
    "localField":"MID", 
    "foreignField":"MID", 
    "as":"ActorsRating"
  }},
  { "$project": {
    "Actors": 1,
    "Remark": {
      "$filter": {
        "input": "$ActorsRating",
        "as": "ac",
        "cond": { "$ne": ["$$ac.remark", undefined] }
      }
    }
  }}
])