Difference between revisions of "AGGREGATE Movies Tutorial"

Revision as of 20:43, 17 October 2016

Investigating the Movie Database

The movie database includes thousands of movies with documents such as:

{
	"_id" : 10522,
	"title" : "Alien",
	"yr" : 1979,
	"director" : "Ridley Scott",
	"budget" : 11000000,
	"gross" : 104931801,
	"cast" : [
		"Tom Skerritt",
		"Sigourney Weaver",
		"Veronica Cartwright",
		"Harry Dean Stanton",
		"John Hurt",
		"Ian Holm",
		"Yaphet Kotto"
	]
}

Casablanca

Show the details of the movie Casablanca

db.movies.aggregate([
    {$match:{
        title:'Alien'
    }},
]).pretty()

db.movies.aggregate([
    {$match:{ 
        title:'Casablanca'
    }}
]).pretty()

Tom Hanks

You can use the match operator on listed items like cast. As there is an index on cast these queries operate quickly and do not have to scan the entire collection.

Show the title and year of the 10 most recent Tom Hanks movies - show the most recent first.

db.movies.aggregate([
    {$match:{
        cast:'Harrison Ford'
    }},
    {$project:{_id:0,title:1}}
]).pretty()

db.movies.aggregate([
    {$match:{
        cast:'Tom Hanks'
    }},
    {$project:{title:1,yr:1,_id:0}},
    {$sort:{yr:-1}},
    {$limit:10}
])

Brad and George

Show the movies that included Brad Pitt and George Clooney. Give the title and year ordered by year

db.movies.aggregate([
    {$match:{
        cast:'Harrison Ford'
    }},
    {$project:{_id:0,title:1}}
])

db.movies.aggregate([
    {$match:{
        cast:'Brad Pitt'
    }},
    {$match:{
        cast:'George Clooney'
    }},
    {$project:{_id:0,title:1,yr:1}},
    {$sort:{yr:1}}
])

Dark Years

A "Dark Year" is one in which Jack Black made more than two movies.

List the years in which Jack Black made three or more movies.

One way to do this is to set up an aggregate pipeline with the following stages:

Use $match to select the Jack Black movies
Use $group together with $sum:1 to get the years together with a count, you will have to name the count field
Use $match to get those where the count is $gte:3
Use $project to extract the year only
Use sort to get the output in ascending order

db.movies.aggregate([
    {$match:{
        cast:'Harrison Ford'
    }},
    {$project:{_id:0,title:1}}
]).pretty()

db.movies.aggregate([
    {$match:{
        cast:'Jack Black'
    }},
    {$group:{_id:'$yr',num:{$sum:1}}},
    {$match:{num:{$gte:3}}},
    {$project:{_id:1}},
    {$sort:{_id:1}}
]).pretty()