MongoDB - $setIntersection with $facet

Question

Here's my query:

db.movies.aggregate([
   {$facet:{
        rating:[{$match: {"imdb.rating":{$gte: 0},"metacritic":{$gte: 0}}}, 
   {$sort: {"imdb.rating":-1}},{$project: 
   {"title":1,"imdb.rating":1,"metacritic":1}},{$limit: 10}],

     critic:[{$match: {"metacritic":{$gte: 0},"imdb.rating":{$gte: 0}}}, 
   {$sort: {"metacritic": -1}},{$project: 
   {"title":1,"imdb.rating":1,"metacritic":1}},{$limit: 10}],

     intersection:[{$project: {common: {$setIntersection: 
   ["$rating","$critic"]}}}]

 }}


])

And my sample data set is:

{
"_id" : ObjectId("573a13cef29313caabd8709c"),
"title" : "Justin Bieber: Never Say Never",
"year" : 2011,
"runtime" : 105,
"released" : ISODate("2011-02-11T00:00:00.000Z"),
"cast" : [ 
    "Justin Bieber", 
    "Boys II Men", 
    "Miley Cyrus", 
    "Sean Kingston"
],
"metacritic" : 52,
"poster" : "http://ia.media-imdb.com/images/M/MV5BMTY0NDQzMjIzOF5BMl5BanBnXkFtZTcwNDk2NzczNA@@._V1_SX300.jpg",
"plot" : "Follows Justin Bieber with some footage of performances from his 2010 concert tour.",
"fullplot" : "The camera follows Justin Bieber (1994- ) during the ten days leading up to his August, 2010, sold-out show at Madison Square Garden. Footage of these ten days of concerts, rehearsals, and down time with boyhood friends, his mom, and his entourage is inter-cut with home movies, old photos, and interviews showing a musical prodigy who loves to perform, comes to the attention of an Atlanta agent via YouTube, impresses Usher, and rockets to international stardom soon after his 15th birthday. His manager emphasizes the importance of social media and of Justin's work ethic and personality in making him a star; the camera emphasizes Bieber's look. His mom and grandparents shine.",
"awards" : "2 wins & 6 nominations.",
"lastupdated" : "2015-08-23 00:33:04.327000000",
"type" : "movie",
"languages" : [ 
    "English"
],
"directors" : [ 
    "Jon M. Chu"
],
"imdb" : {
    "rating" : 1.6,
    "votes" : 73548,
    "id" : 1702443
},
"countries" : [ 
    "USA"
],
"rated" : "G",
"genres" : [ 
    "Documentary", 
    "Music"
],
"tomatoes" : {
    "website" : "http://www.JustinBieberNeverSayNever.com",
    "viewer" : {
        "rating" : 3.5,
        "numReviews" : 61961,
        "meter" : 65
    },
    "dvd" : ISODate("2011-05-13T00:00:00.000Z"),
    "rotten" : 37,
    "boxOffice" : "$73.0M",
    "consensus" : "As a tour documentary, it's rather uninspired -- but as a 3D glimpse of a building pop culture phenomenon, Never Say Never is undeniably entertaining.",
    "critic" : {
        "rating" : 5.8,
        "numReviews" : 102,
        "meter" : 64
    },
    "production" : "Paramount Pictures",
    "lastUpdated" : ISODate("2015-08-18T19:05:06.000Z"),
    "fresh" : 65
},
"num_mflix_comments" : 2,
"comments" : [ 
    {
        "name" : "Petyr Baelish",
        "email" : "[email protected]",
        "movie_id" : ObjectId("573a13cef29313caabd8709c"),
        "text" : "A earum quae quos perspiciatis tempora natus. Voluptatem quod cum illum magni reiciendis. Labore exercitationem velit suscipit dicta.",
        "date" : ISODate("2009-05-10T13:15:19.000Z")
    }, 
    {
        "name" : "Daario Naharis",
        "email" : "[email protected]",
        "movie_id" : ObjectId("573a13cef29313caabd8709c"),
        "text" : "Ut quod rem rem dolor voluptatum necessitatibus sapiente. Ea nulla dignissimos iste porro natus eveniet eum. Quidem sit totam libero iusto repudiandae ab ducimus. Facere nesciunt assumenda ab.",
        "date" : ISODate("1992-02-17T22:39:11.000Z")
    }
]
}

I'm trying to find the movies which are in both: list 1) Top 10 movies as per imdb rating list 2) Top 10 movies as per metacritic

If one or more movies are seen in both lists, those should be filtered and displayed.

intersection:[{$project: {common: {$setIntersection:["$rating","$critic"]}}}]

This part isnt working and I even tried matching for titles $setIntersection:["$rating.title","$critic.title"]

I see that arrays (rating, critic & common) are calculated seperately, but why can't I access those arrays with $ as a variable? Can someone please tell me how to get the intersection of rating and critic?

Answer 1

Your approach is ok and I think by repositioning your brackets you get what you want. Separate the $project stage out into another stage, not another stage inside the $facet:

db.movies.aggregate([{
    $facet:{
        rating:[
            {$match:{"imdb.rating":{$gte: 0},"metacritic":{$gte: 0}}},
            {$sort:{"imdb.rating":-1}},
            {$project:{"title":1,"imdb.rating":1,"metacritic":1}},
            {$limit:10}
        ],
        critic:[
            {$match:{"metacritic":{$gte: 0},"imdb.rating":{$gte: 0}}}, 
            {$sort:{"metacritic": -1}},
            {$project:{"title":1,"imdb.rating":1,"metacritic":1}},
            {$limit:10}
        ]
    }
}, {
    $project:{
        common:{$setIntersection:["$rating","$critic"]}
    }
}])

The reason why this is needed can be found in the documentation:

Each sub-pipeline within $facet is passed the exact same set of input documents. These sub-pipelines are completely independent of one another and the document array output by each is stored in separate fields in the output document. The output of one sub-pipeline can not be used as the input for a different sub-pipeline within the same $facet stage. If further aggregations are required, add additional stages after $facet and specify the field name, , of the desired sub-pipeline output.