Calculate average movie ratings by rating category

Write a query in MongoDB to find the average IMDb rating for movies with different ratings (e.g., 'PG', 'R', 'G') from the ‘movies’ collection.

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: "$rated",
averageRating: { $avg: "$imdb.rating" }
    }
  }
])

Output:

{ _id: 'GP', averageRating: 7.040909090909091 },
{ _id: null, averageRating: 6.82571922334045 },
{ _id: 'PASSED', averageRating: 7.305494505494505 },
{ _id: 'OPEN', averageRating: 7.2 },
{ _id: 'APPROVED', averageRating: 7.167369901547117 },
{ _id: 'PG-13', averageRating: 6.405897546276367 },
{ _id: 'NC-17', averageRating: 6.760526315789473 },
{ _id: 'TV-Y7', averageRating: 6.033333333333334 },
{ _id: 'TV-14', averageRating: 6.886516853932584 },
{ _id: 'G', averageRating: 6.6280922431865825 },
{ _id: 'Not Rated', averageRating: 7.7 },
{ _id: 'Approved', averageRating: 7.040000000000001 },
{ _id: 'AO', averageRating: 5.3999999999999995 },
{ _id: 'UNRATED', averageRating: 6.97190412782956 },
{ _id: 'TV-G', averageRating: 6.4576271186440675 },
{ _id: 'NOT RATED', averageRating: 7.0212703101920235 },
{ _id: 'TV-MA', averageRating: 7.364999999999999 },
{ _id: 'TV-PG', averageRating: 6.951315789473684 },
{ _id: 'PG', averageRating: 6.435456017269293 },
{ _id: 'M', averageRating: 7.013513513513513 }

Explanation:

The said query in MongoDB returns a result containing the average ratings for each rating category from the 'movies' collection in MongoDB.

The $group stage groups the movies based on the value of the "rated" field.

The $avg operator creates a new field called "averageRating" that calculates the average IMDb rating for each group.

Improve this sample solution and post your code through Disqus.

Previous: Top 10 directors by movie count.
Next: Retrieve award-winning movies sorted by year.