Chapter 3: Lab $unwind not correct

Hey @Doug_Duncan
I’m stuck… My average in not correct

I am doing the following:

  1. Match, for Enlish in languages array
  2. Unwind the cast array
  3. Group by cast and title
  4. Calculate average of imdb.rating
  5. Sort by numFilms – decreasing

db.movies.aggregate([
{$match: {languages:{$all:[“English”] }}},
{ $unwind: “$cast”},
{$group: {_id: {actor: “$cast”, “$title”}}},
ave_rating: {$avg: “$imdb.rating” } ,
numFilms: {$sum:1}}},
{$sort: {“numFilms”:-1} }
])

OK, so taking a look at you results (I had to fix a couple of typos), I notice that you only have a max of two titles which doesn’t seem right.

You want to get a count of all movies that an actor was in, but you’re currently finding what could potentially be duplicate entries in the data set. Think of really popular actors like Nicholas Cage or Carey Grant. You should see several dozen titles in your count for them.

Again you are very close to getting the correct answer, and I know you can can figure out what you’re doing to get such low counts. Again, if you bang your head too much let me know and I’ll let you know what you’re missing (or rather adding in this case :wink: ).

1 Like

The above is not a valid JSON object and it generates a syntax error because the value “$title” has no key:

mongosh> id = {actor: "$cast", "$title"}
SyntaxError: Unexpected token (1:30)

> 1 | id = {actor: "$cast", "$title"}

If “$title” really belongs to the _id object of the $group stage, a valid object would be

id = { actor : "$cast", title : "$title"}
{ actor: '$cast', title: '$title' }

See https://www.mongodb.com/docs/manual/reference/operator/aggregation/group/ for the role of the _id.

1 Like

That’s one of the typo’s I had to fix. :wink:

1 Like

Thanks guys!! I did sent the wrong stage
so here is what I’ve have
db.movies.aggregate([{$match: {languages:{$all:[“English”] }}}, { $unwind: “$cast”}, {$group: {_id: {actor: “$cast”}, cast_member: {$sum:1}}},{$sort: {“cast_member”:-1} } ])
[
{ _id: { actor: ‘John Wayne’ }, cast_member: 107 },
{ _id: { actor: ‘Michael Caine’ }, cast_member: 82 },
{ _id: { actor: ‘Christopher Lee’ }, cast_member: 76 },
{ _id: { actor: ‘Robert De Niro’ }, cast_member: 75 },
{ _id: { actor: ‘Bette Davis’ }, cast_member: 68 },
{ _id: { actor: ‘Nicolas Cage’ }, cast_member: 66 },

When I add the title field to the $group stage
db.movies.aggregate([{$match: {languages:{$all:[“English”] }}}, { $unwind: “$cast”}, {$group: {_id: {actor: “$cast”, movie_title: “$title”}}},])
[
{ _id: { actor: “Richard O’Brien”, movie_title: ‘Shock Treatment’ } },
{ _id: { actor: ‘Pat Evison’, movie_title: ‘The Silent One’ } },
{ _id: { actor: ‘Leslie Wing’, movie_title: ‘The Dungeonmaster’ } },
{_id: {actor: ‘Mark Cousins’, movie_title: ‘The Story of Film: An Odyssey’}},
Some of these should have multiple movies per actor. Correct?
So what I doing wrong??

You just want to group on actor and then get a count of the movies they starred in like you have in your first query. In your second query you are just getting unique combinations of actor and movie title and that’s taking you down the wrong path.

In addition to the count of movies that an actor was in you need to get the average imdb.rating for all of those movies to get the answer.

1 Like