w3resource

Find movies with highest average rating by year


Find the movies released in the year with the highest average IMDb rating from the 'movies' collection in MongoDB.

Structure of 'movies' collection:

{
    _id: ObjectId("573a1390f29313caabcd42e8"),
plot: 'A group of bandits stage a brazen train hold-up, only to find a determined posse hot on their heels.',
genres: [ 'Short', 'Western' ],
runtime: 11,
cast: [
      'A.C. Abadie',
      "Gilbert M. 'Broncho Billy' Anderson",
      'George Barnes',
      'Justus D. Barnes'
    ],
poster: 'https://m.media-amazon.com/images/M/MV5BMTU3NjE5NzYtYTYyNS00MDVmLWIwYjgtMmYwYWIxZDYyNzU2XkEyXkFqcGdeQXVyNzQzNzQxNzI@._V1_SY1000_SX677_AL_.jpg',
title: 'The Great Train Robbery',
fullplot: "Among the earliest existing films in American cinema - notable as the first film that presented a narrative story to tell - it depicts a group of cowboy outlaws who hold up a train and rob the passengers. They are then pursued by a Sheriff's posse. Several scenes have color included - all hand tinted.",
languages: [ 'English' ],
released: ISODate("1903-12-01T00:00:00.000Z"),
directors: [ 'Edwin S. Porter' ],
rated: 'TV-G',
awards: { wins: 1, nominations: 0, text: '1 win.' },
lastupdated: '2015-08-13 00:27:59.177000000',
year: 1903,
imdb: { rating: 7.4, votes: 9847, id: 439 },
countries: [ 'USA' ],
type: 'movie',
tomatoes: {
viewer: { rating: 3.7, numReviews: 2559, meter: 75 },
fresh: 6,
critic: { rating: 7.6, numReviews: 6, meter: 100 },
rotten: 0,
lastUpdated: ISODate("2015-08-08T19:16:10.000Z")
    }
.....

Query:

db.movies.aggregate([
  {
    $group: {
      _id: "$year",
averageRating: { $avg: "$imdb.rating" },
movies: { $push: "$$ROOT" }
    }
  },
  {
    $sort: { averageRating: -1 }
  },
  {
    $limit: 1
  },
  {
    $unwind: "$movies"
  },
  {
    $replaceRoot: { newRoot: "$movies" }
  }
])

Output:

  {
    _id: ObjectId("573a139af29313caabcf184a"),
plot: "David Attenborough's study of the world of plants, which demonstrates, with the aid of time-lapse photography, the rich and varied ways in which they flourish.",
genres: [ 'Documentary' ],
runtime: 292,
cast: [ 'David Attenborough' ],
num_mflix_comments: 1,
poster: 'https://m.media-amazon.com/images/M/MV5BMTU3MDQ2NDI1MV5BMl5BanBnXkFtZTcwMzMwMjIyMQ@@._V1_SY1000_SX677_AL_.jpg',
title: 'The Private Life of Plants',
fullplot: "David Attenborough's study of the world of plants, which demonstrates, with the aid of time-lapse photography, the rich and varied ways in which they flourish.",
languages: [ 'English' ],
released: ISODate("1995-01-05T00:00:00.000Z"),
awards: {
wins: 3,
nominations: 3,
text: 'Won 1 Primetime Emmy. Another 2 wins & 3 nominations.'
    },
lastupdated: '2015-09-06 00:40:33.587000000',
year: '1995è',
imdb: { rating: 9, votes: 1693, id: 123360 },
countries: [ 'UK' ],
type: 'series',
tomatoes: {
viewer: { rating: 3.8, numReviews: 144, meter: 74 },
dvd: ISODate("2006-07-25T00:00:00.000Z"),
lastUpdated: ISODate("2015-07-31T19:04:50.000Z")
    }
  }

Explanation:

The said query in MongoDB retrieve movies with the highest average rating for each year from the 'movies' collection in MongoDB.

The "$avg" operator calculates the average value of the "imdb.rating" field for each group and stores it in the "averageRating" field.

The "$push" operator creates an array "movies" containing all the documents in the group.

The $sort stage sorts the groups descending order to ensure that the group with the highest average rating will be at the top.

The $limit stage limits to retrieve the group with the highest average rating.

The $unwind stage unwinds the "movies" array in multiple documents.

Improve this sample solution and post your code through Disqus.

Previous: Finding the most popular genre.
Next: Top 10 directors by movie count.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.