Answers to this lab's questions

1/ Compter le nombre de Thriller présents en base (films qui possèdent au moins le genre Thriller)

Query

db.movies.find({ "genres": "Thriller" }).count()

Result

2658

2/ Trouver la liste des films qui contiennent le mot "ghost" dans leur titre

Query

db.movies.find({ "title": { "$regex": /ghost/i } })

Result

[
  {
    _id: ObjectId("573a1392f29313caabcda826"),
    plot: "An American businessman's family convinces him to buy a Scottish castle and disassemble it to ship it to America brick by brick, where it will be put it back together. The castle though is ...",
    genres: [ 'Comedy', 'Fantasy', 'Horror' ],
    runtime: 95,
    rated: 'APPROVED',
    cast: [
      'Robert Donat',
      'Jean Parker',
      'Eugene Pallette',
      'Elsa Lanchester'
    ],
    num_mflix_comments: 1,
    poster: 'https://m.media-amazon.com/images/M/MV5BMDhiMjE2MzktYzQzNi00MD
FmLThiMWUtZTMwYmFhZjIyYzc4XkEyXkFqcGdeQXVyNjE5MjUyOTM@._V1_SY1000_SX677_AL_.jpg',
    title: 'The Ghost Goes West',
    fullplot: "An American businessman's family convinces him to buy a Scot
tish castle and disassemble it to ship it to America brick by brick, where 
it will be put it back together. The castle though is not the only part of the deal, with it goes the several-hundred year old ghost who haunts it.", 
    languages: [ 'English' ],
    released: ISODate("1936-02-07T00:00:00.000Z"),
    directors: [ 'Renè Clair' ],
    writers: [
      'Renè Clair',
      'Eric Keown (story)',
      'Geoffrey Kerr (scenario)',
      'Robert E. Sherwood'
    ],
    awards: { wins: 1, nominations: 1, text: '1 win & 1 nomination.' },    
    lastupdated: '2015-09-02 00:36:23.240000000',
    year: 1935,
    imdb: { rating: 6.8, votes: 1072, id: 26406 },
    countries: [ 'UK' ],
    type: 'movie',
    tomatoes: {
      viewer: { rating: 3.5, numReviews: 190, meter: 56 },
      production: 'Criterion Collection',
      lastUpdated: ISODate("2015-08-31T18:17:48.000Z")
    }
  },
  {
    _id: ObjectId("573a1393f29313caabcdcd4e"),
    plot: 'Tom Merriam signs on the ship Altair as third officer under Capt
ain Stone. At first things look good, Stone sees Merriam as a younger version of himself and Merriam sees Stone as the first ...',
    genres: [ 'Drama', 'Mystery', 'Thriller' ],
    runtime: 69,
...

3/ Trouver la liste des films qui contiennent le mot "ghost" dans leur titre et qui sont sortis après 2013

Query

db.movies.find({ "title": { "$regex": /ghost/i }, "year": { "$gt": 2013 } })

Result

[
  {
    _id: ObjectId("573a13ddf29313caabdb3f4b"),
    plot: "A man, Joseph, loses his wife at sea, then spirals deep into a world of con
fusion. The wife's brothers' need revenge! Joseph tries to tell anyone who will listen that a whale killed his ...",
    genres: [ 'Drama', 'Mystery', 'Thriller' ],
    runtime: 105,
    title: 'The Ghost and the Whale',
    num_mflix_comments: 1,
    poster: 'https://m.media-amazon.com/images/M/MV5BZTQ3MTc1OTAtNjMxNy00MTc1LTlkZDktNjcyYzJiZWQxOTM0XkEyXkFqcGdeQXVyMzM0OTM4MTQ@._V1_SY1000_SX677_AL_.jpg',
    countries: [ 'USA' ],
    fullplot: "A man, Joseph, loses his wife at sea, then spirals deep into a world of
 confusion. The wife's brothers' need revenge! Joseph tries to tell anyone who will li
sten that a whale killed his beautiful Annabel Lee, but even he doesn't quite remember
 the truth. A journey into the depths of his mind, a conversation with a whale and blood thirsty brothers. Love is Forever, So is Revenge",
    languages: [ 'English' ],
    cast: [
      'Monica Keena',
      'Tippi Hedren',
      'Jonathan Pryce',
      'Ashlynn Yennie'
    ],
    directors: [ 'Anthony Gaudioso', 'James Gaudioso' ],
    writers: [
      'Anthony Gaudioso (story)',
      'Anthony Gaudioso',
      'Anthony Gaudioso'
    ],
    awards: { wins: 1, nominations: 0, text: '1 win.' },
    lastupdated: '2015-08-14 01:12:08.707000000',
    year: 2015,
    imdb: { rating: '', votes: '', id: 2429278 },
    type: 'movie',
    tomatoes: {
      viewer: { rating: 0.5, numReviews: 1 },
      lastUpdated: ISODate("2015-06-06T18:45:01.000Z")
    }
  }
]

4/ Trouver le film qui a gagné le plus de récompenses

Query

db.movies.aggregate([
   { "$sort": { "awards.wins": -1 } },
   { "$limit": 1 }
])

Result

[
  {
    _id: ObjectId("573a13d5f29313caabd9cae7"),
    fullplot: "Based on an incredible true story of one man's fight for survival and f
reedom. In the pre-Civil War United States, Solomon Northup (Chiwetel Ejiofor), a free
 black man from upstate New York, is abducted and sold into slavery. Facing cruelty (p
ersonified by a malevolent slave owner, portrayed by Michael Fassbender), as well as u
nexpected kindnesses, Solomon struggles not only to stay alive, but to retain his dign
ity. In the twelfth year of his unforgettable odyssey, Solomon's chance meeting with a Canadian abolitionist (Brad Pitt) will forever alter his life.",
    imdb: { rating: 8.1, votes: 363143, id: 2024544 },
    year: 2013,
    plot: 'In the antebellum United States, Solomon Northup, a free black man from upstate New York, is abducted and sold into slavery.',
    genres: [ 'Biography', 'Drama', 'History' ],
    rated: 'R',
    metacritic: 97,
    title: '12 Years a Slave',
    lastupdated: '2015-08-22 00:05:56.030000000',
    languages: [ 'English' ],
    writers: [
      'John Ridley (screenplay)',
      'Solomon Northup (based on "Twelve Years a Slave" by)'
    ],
    type: 'movie',
    tomatoes: {
      viewer: { rating: 0, numReviews: 0 },
      lastUpdated: ISODate("2015-08-22T18:57:07.000Z")
    },
    poster: 'https://m.media-amazon.com/images/M/MV5BMjExMTEzODkyN15BMl5BanBnXkFtZTcwNTU4NTc4OQ@@._V1_SY1000_SX677_AL_.jpg',
    num_mflix_comments: 2,
    released: ISODate("2013-11-08T00:00:00.000Z"),
    awards: {
      wins: 267,
      nominations: 256,
      text: 'Won 3 Oscars. Another 264 wins & 256 nominations.'
    },
    countries: [ 'USA', 'UK' ],
    cast: [
      'Chiwetel Ejiofor',
      'Dwight Henry',
      'Dickie Gravois',
      'Bryan Batt'
    ],
    directors: [ 'Steve McQueen' ],
    runtime: 134
  }
]

5/ Trouver le plus vieux film de plus de 2 heures ayant une note inférieur à 2 sur la plateforme imdb

Query

db.movies.aggregate([
    { "$match": { "runtime": { "$gt": 120 }, "imdb.rating": { "$lt": 2.0 }}},
    { "$sort": { "year": 1 } },
    { "$limit": 1 }
])

Result

[
  {
    _id: ObjectId("573a13dbf29313caabdaf3c6"),
    plot: 'When a temple priest commits suicide after being dishonored by an evil landlord, his son returns to his native village on a mission of vengeance.',
    genres: [ 'Action', 'Comedy' ],
    runtime: 150,
    rated: 'NOT RATED',
    cast: [
      'Ajay Devgn',
      'Tamannaah Bhatia',
      'Mahesh Manjrekar',
      'Paresh Rawal'
    ],
    num_mflix_comments: 3,
    poster: 'https://m.media-amazon.com/images/M/MV5BMTM4MTYwNDA0NF5BMl5BanBnXkFtZTcwNjE0MjQyOQ@@._V1_SY1000_SX677_AL_.jpg',
    title: 'Courageous',
    fullplot: 'An honest temple priest takes on more than he can handle when he bears witnes
s to a murder and gives evidence in a court of law against a powerful landlord in his villag
e. The landlord bribes his way to freedom and engineers a ploy to ruin the priests reputatio
n and his status among his peers in the village. Ashamed of his tarnished reputation the pri
est commits suicide,and leaves his wife and young children to face the atrocities of the evi
l landlord. The son flees the village and travels to Mumbai to make a life, but returns years later to avenge the shame caused to his family. Thi',
    languages: [ 'Hindi' ],
    released: ISODate("2013-03-29T00:00:00.000Z"),
    directors: [ 'Sajid Khan' ],
    writers: [
      'K. Raghavendra Rao (original story)',
      'Sajid Khan (story)',
      'Sajid Khan (screenplay)',
      'Farhad (screenplay)',
      'Sajid (screenplay)'
    ],
    awards: { wins: 2, nominations: 1, text: '2 wins & 1 nomination.' },
    lastupdated: '2015-09-02 00:21:26.757000000',
    year: 2013,
    imdb: { rating: 1.8, votes: 5448, id: 2344678 },
    countries: [ 'India' ],
    type: 'movie',
    tomatoes: {
      viewer: { rating: 2.1, numReviews: 369, meter: 22 },
      website: 'http://www.utvgroup.com/motion-pictures/coming-soon/himmatwala.html',       
      production: 'UTV Motion Pictures',
      boxOffice: '$0.3M',
      lastUpdated: ISODate("2015-08-22T19:18:28.000Z")
    }
  }
]

6/ Modifier la requête précédente pour récupérer en même temps les commentaires.

Query

db.movies.aggregate([
    { "$match": { "runtime": { "$gt": 120 }, "imdb.rating": { "$lt": 2.0 } } },
    { "$sort": { "year": 1 } },
    { "$limit": 1 },
    { "$lookup": {
        "from": "comments",
        "localField": "_id",
        "foreignField": "movie_id",
        "as": "movie_comments"
    }}
])

Result

[
  {
    _id: ObjectId("573a13dbf29313caabdaf3c6"),
    plot: 'When a temple priest commits suicide after being dishonored by an evil landlord, his son returns to his native village on a mission of vengeance.',
    genres: [ 'Action', 'Comedy' ],
    runtime: 150,
    rated: 'NOT RATED',
    cast: [
      'Ajay Devgn',
      'Tamannaah Bhatia',
      'Mahesh Manjrekar',
      'Paresh Rawal'
    ],
    num_mflix_comments: 3,
    poster: 'https://m.media-amazon.com/images/M/MV5BMTM4MTYwNDA0NF5BMl5BanBnXkFtZTcwNjE0MjQyOQ@@._V1_SY1000_SX677_AL_.jpg',
    title: 'Courageous',
    fullplot: 'An honest temple priest takes on more than he can handle when he bears witnes
s to a murder and gives evidence in a court of law against a powerful landlord in his villag
e. The landlord bribes his way to freedom and engineers a ploy to ruin the priests reputatio
n and his status among his peers in the village. Ashamed of his tarnished reputation the pri
est commits suicide,and leaves his wife and young children to face the atrocities of the evi
l landlord. The son flees the village and travels to Mumbai to make a life, but returns years later to avenge the shame caused to his family. Thi',
    languages: [ 'Hindi' ],
    released: ISODate("2013-03-29T00:00:00.000Z"),
    directors: [ 'Sajid Khan' ],
    writers: [
      'K. Raghavendra Rao (original story)',
      'Sajid Khan (story)',
      'Sajid Khan (screenplay)',
      'Farhad (screenplay)',
      'Sajid (screenplay)'
    ],
    awards: { wins: 2, nominations: 1, text: '2 wins & 1 nomination.' },
    lastupdated: '2015-09-02 00:21:26.757000000',
    year: 2013,
    imdb: { rating: 1.8, votes: 5448, id: 2344678 },
    countries: [ 'India' ],
    type: 'movie',
    tomatoes: {
      viewer: { rating: 2.1, numReviews: 369, meter: 22 },
      website: 'http://www.utvgroup.com/motion-pictures/coming-soon/himmatwala.html',       
      production: 'UTV Motion Pictures',
      boxOffice: '$0.3M',
      lastUpdated: ISODate("2015-08-22T19:18:28.000Z")
    },
    movie_comments: []
  }
]

7/ Récupérer le nombre de films par pays ainsi que leurs titres. Le résultat devra être trié du pays avec le plus grand nombre de films au plus petit.

Query

db.movies.aggregate([
    { "$unwind": "$countries" },
    {
        "$group": {
            "_id": "$countries",
            "count": { "$sum": 1 },
            "titles": { "$push": "$title" }
        }
    },
    {
        "$project": {
            "_id": 0,
            "country": "$_id",
            "count": 1,
            "titles": 1
        }
    },
    { "$sort": { "count": -1 } }
])

Result

It's very long. Here's part of the 2nd document:

...
      'La Sapienza',
      'The New Girlfriend',
      'Xenia',
      "If You Don't, I Will",
      "Relationship Status: It's Complicated",
      'Love at First Fight',
      'The Blue Room',
      'Still the Water',
      'Allèluia',
      'The Empty Hours',
      'Emergency Exit: Young Italians Abroad',
      'Tale of Tales',
      'Once in a Lifetime',
      'Arabian Nights: Volume 1 - The Restless One',
      'As We Were Dreaming',
      'Youth',
      'Still Alice',
      "Next Time I'll Aim for the Heart",
      'We Come as Friends',
      'Breathe',
      'Young Tiger',
      'Atlèntida',
      'Ciencias naturales',
      'Samba',
      'The Man from Oran',
      'Timbuktu',
      "Shrew's Nest",
      'Stand',
      'April and the Extraordinary World',
      'Number One Fan',
      'Francofonia',
      'Happiness',
      'La buca',
      'To Kill a Man',
      'The Lobster',
      'Stations of the Cross',
      'Blind Massage',
      'Vie sauvage',
      'Mon roi',
      'Return to Ithaca',
      'Mediterranea',
      'Pas son genre',
      'The Kidnapping of Michel Houellebecq',
      'Misunderstood',
      'The Assassin',
      'The Look of Silence',
      'Dior and I',
      'A Year in Champagne',
      'The Bèlier Family',
      'La cèrèmonie',
      '24 Days',
      'Wondrous Boccaccio',
      'Force Majeure',
      'Sworn Virgin',
      'Girlhood',
      'Party Girl',
      'Free Fall',
      'The Salt of the Earth',
      'Silvered Water, Syria Self-Portrait',
      'Alias Marèa',
      'Of Men and War',
      'The Kindergarten Teacher',
      'Refugiado',
      'National Gallery',
      'The Wait',
      'Mountains May Depart',
      'Asterix and Obelix: Mansion of the Gods',
      'Summer Nights',
      'Love',
      'Hitchcock/Truffaut',
      'Paulina',
      'A Blast',
      'The Brand New Testament',
      'Bang Gang (A Modern Love Story)',
      'Chronic',
      'Mune, le gardien de la lune',
      'Tokyo Fiancèe',
      'Black Souls',
      'Mustang',
      'The President',
      'Cosmos',
      'My Golden Days',
      'Cemetery of Splendour',
      'Dheepan',
      'Disorder',
      'Valley of Love',
      'Ixcanul Volcano',
      'Olmo & the Seagull',
      'The Here After',
      'Two Friends',
      'Courted',
      'Les cowboys',
      'Evolution',
      'An',
      'The Mysterious Death of Pèrola',
      'The Pearl Button',
      'Aferim!',
      'Homeland (Iraq Year Zero)',
      'The Wakhan Front',
      'Ave Maria',
      'Marguerite',
      'Marguerite & Julien',
      'The Measure of a Man',
      'Dègradè',
      'Ice and the Sky',
      'The Treasure',
      'Les oiseaux de passage',
      'The Other Side',
      'One Floor Below',
      'Fly Away Solo',
      'Land and Shade',
      'Much Loved',
      'The Magic Mountain',
      'No Home Movie',
      'Babai'
    ],
    country: 'France'
  }
]

BONUS/ Trouver les cinémas aux alentours (5km maxi) de New York (x = -73.983487 et y = 40.76078)

Query

db.theaters.createIndex({ "location.geo": "2dsphere" })

db.theaters.aggregate([
  {
    $geoNear: {
      near: { type: "Point", coordinates: [ -73.983487, 40.76078 ] },
      distanceField: "dist.calculated",
      maxDistance: 5000,
      spherical: true
    }
  }
])

Result

[
  {
    _id: ObjectId("59a47287cfa9a3a73e51e8e2"),
    theaterId: 1908,
    location: {
      address: {
        street1: '750 Seventh Ave',
        city: 'New York',
        state: 'NY',
        zipcode: '10019'
      },
      geo: { type: 'Point', coordinates: [ -73.983487, 40.76078 ] }
    },
    dist: { calculated: 0 }
  },
  {
    _id: ObjectId("59a47286cfa9a3a73e51e744"),
    theaterId: 1028,
    location: {
      address: {
        street1: '529 5th Ave',
        city: 'New York',
        state: 'NY',
        zipcode: '10017'
      },
      geo: { type: 'Point', coordinates: [ -73.980011, 40.754333 ] }
    },
    dist: { calculated: 775.2179160541888 }
  },
  {
    _id: ObjectId("59a47286cfa9a3a73e51e838"),
    theaterId: 1448,
    location: {
      address: {
        street1: '1880 Broadway',
        city: 'New York',
        state: 'NY',
        zipcode: '10023'
      },
      geo: { type: 'Point', coordinates: [ -73.982094, 40.769882 ] }
    },
    dist: { calculated: 1020.0082247577323 }
  },
  {
    _id: ObjectId("59a47287cfa9a3a73e51eb63"),
    theaterId: 482,
    location: {
      address: {
        street1: '60 West 23rd Street',
        city: 'New York',
        state: 'NY',
        zipcode: '10010'
      },
      geo: { type: 'Point', coordinates: [ -73.99295, 40.74194 ] }
    },
    dist: { calculated: 2243.939771813251 }
  },
  {
    _id: ObjectId("59a47287cfa9a3a73e51e87d"),
    theaterId: 1531,
    location: {
      address: {
        street1: '52 E 14th St',
        street2: '#64',
        city: 'New York',
        state: 'NY',
        zipcode: '10003'
      },
      geo: { type: 'Point', coordinates: [ -73.9905737, 40.7349109 ] }
    },
    dist: { calculated: 2941.0819136760388 }
  },
  {
    _id: ObjectId("59a47287cfa9a3a73e51e8ed"),
    theaterId: 1906,
    location: {
      address: {
        street1: '2 Union Square',
        city: 'New York',
        state: 'NY',
        zipcode: '10003'
      },
      geo: { type: 'Point', coordinates: [ -73.9899, 40.73445 ] }
    },
    dist: { calculated: 2980.505320477483 }
  },
  {
    _id: ObjectId("59a47287cfa9a3a73e51eccb"),
    theaterId: 835,
    location: {
      address: {
        street1: '1280 Lexington Ave',
        city: 'New York',
        state: 'NY',
        zipcode: '10028'
      },
      geo: { type: 'Point', coordinates: [ -73.956085, 40.779835 ] }
    },
    dist: { calculated: 3136.263768746724 }
  },
  {
    _id: ObjectId("59a47287cfa9a3a73e51ebe1"),
    theaterId: 609,
    location: {
      address: {
        street1: '622 Broadway',
        city: 'New York',
        state: 'NY',
        zipcode: '10012'
      },
      geo: { type: 'Point', coordinates: [ -73.996689, 40.72551 ] }
    },
    dist: { calculated: 4081.0481110504775 }
  }
]