Difference between revisions of "AGGREGATE Movies Tutorial"
From NoSQLZoo
m (Tidy.) |
|||
| Line 1: | Line 1: | ||
==Investigating the Movie Database== | ==Investigating the Movie Database== | ||
The movie database includes thousands of movies with documents such as: | The movie database includes thousands of movies with documents such as: | ||
| − | <pre> | + | <pre><nowiki> |
{ | { | ||
"_id" : 10522, | "_id" : 10522, | ||
| Line 19: | Line 19: | ||
] | ] | ||
} | } | ||
| − | </pre> | + | </nowiki></pre> |
==Casablanca== | ==Casablanca== | ||
<div class="q" data-lang="mongo"> | <div class="q" data-lang="mongo"> | ||
<p class="strong">Show the details of the movie Casablanca.</p> | <p class="strong">Show the details of the movie Casablanca.</p> | ||
| − | <pre class="def"> | + | <pre class="def"><nowiki> |
db.movies.aggregate([{ | db.movies.aggregate([{ | ||
$match: { | $match: { | ||
title: 'Alien' | title: 'Alien' | ||
} | } | ||
| − | }]).pretty() | + | }]).pretty(); |
| − | </pre> | + | </nowiki></pre> |
| − | <pre class="ans">db.movies.aggregate([{$match:{title:'Casablanca'}}]).pretty()</pre> | + | <pre class="ans"><nowiki>db.movies.aggregate([{$match:{title:'Casablanca'}}]).pretty();</nowiki></pre> |
</div> | </div> | ||
| Line 37: | Line 37: | ||
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. | 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. | ||
<p class="strong">Show the title and year of the 10 most recent Tom Hanks movies - show the most recent first.</p> | <p class="strong">Show the title and year of the 10 most recent Tom Hanks movies - show the most recent first.</p> | ||
| − | <pre class="def"> | + | <pre class="def"><nowiki> |
db.movies.aggregate([ | db.movies.aggregate([ | ||
{$match: { | {$match: { | ||
| Line 43: | Line 43: | ||
}}, | }}, | ||
{$project: {_id: 0, title: 1}} | {$project: {_id: 0, title: 1}} | ||
| − | ]).pretty() | + | ]).pretty();</nowiki></pre> |
| − | </pre> | ||
<pre class="ans">db.movies.aggregate([{$match:{cast:'Tom Hanks'}},{$project:{title:1,yr:1,_id:0}},{$sort:{yr:-1}},{$limit:10}]).pretty()</pre> | <pre class="ans">db.movies.aggregate([{$match:{cast:'Tom Hanks'}},{$project:{title:1,yr:1,_id:0}},{$sort:{yr:-1}},{$limit:10}]).pretty()</pre> | ||
</div> | </div> | ||
| Line 51: | Line 50: | ||
<div class="q" data-lang="mongo"> | <div class="q" data-lang="mongo"> | ||
<p class="strong">Show the movies that included Brad Pitt and George Clooney. Give the title and year ordered by year</p> | <p class="strong">Show the movies that included Brad Pitt and George Clooney. Give the title and year ordered by year</p> | ||
| − | <pre class="def"> | + | <pre class="def"><nowiki> |
db.movies.aggregate([ | db.movies.aggregate([ | ||
{$match: { | {$match: { | ||
| Line 57: | Line 56: | ||
}}, | }}, | ||
{$project: {_id: 0, title: 1}} | {$project: {_id: 0, title: 1}} | ||
| − | ]).pretty() | + | ]).pretty();</nowiki></pre> |
| − | </pre> | ||
<pre class="ans">db.movies.aggregate([{$match:{cast:'Brad Pitt'}},{$match:{cast:'George Clooney'}},{$project:{_id:0,title:1,yr:1}},{$sort:{yr:1}}]).pretty()</pre> | <pre class="ans">db.movies.aggregate([{$match:{cast:'Brad Pitt'}},{$match:{cast:'George Clooney'}},{$project:{_id:0,title:1,yr:1}},{$sort:{yr:1}}]).pretty()</pre> | ||
</div> | </div> | ||
| Line 74: | Line 72: | ||
*Use <code>$sort</code> to get the output in ascending order. | *Use <code>$sort</code> to get the output in ascending order. | ||
</div> | </div> | ||
| − | <pre class="def"> | + | <pre class="def"><nowiki> |
db.movies.aggregate([ | db.movies.aggregate([ | ||
{$match: { | {$match: { | ||
| Line 80: | Line 78: | ||
}}, | }}, | ||
{$project: {_id: 0, title: 1}} | {$project: {_id: 0, title: 1}} | ||
| − | ]).pretty() | + | ]).pretty();</nowiki></pre> |
| − | </pre> | + | <pre class="ans"><nowiki>db.movies.aggregate([{$match:{cast:'Jack Black'}},{$group:{_id:'$yr',num:{$sum:1}}},{$match:{num:{$gte:3}}},{$project:{_id:1}},{$sort:{_id:1}}]).pretty();</nowiki></pre> |
| − | <pre class="ans">db.movies.aggregate([{$match:{cast:'Jack Black'}},{$group:{_id:'$yr',num:{$sum:1}}},{$match:{num:{$gte:3}}},{$project:{_id:1}},{$sort:{_id:1}}]).pretty()</pre> | ||
</div> | </div> | ||
Revision as of 08:35, 22 June 2018
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}]).pretty()
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}}
]).pretty();
db.movies.aggregate([{$match:{cast:'Brad Pitt'}},{$match:{cast:'George Clooney'}},{$project:{_id:0,title:1,yr:1}},{$sort:{yr:1}}]).pretty()
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. You can build up to your answer by completing each stage in turn:
- Use
$matchto select the Jack Black movies - this will include howlers such as "Anchorman: The Legend of Ron Burgundy" (2004) and "School of Rock" (2003). - Use
$grouptogether with$sum: 1to get the years together with a count, you will have to name the count field - Use
$matchto get those where the count is$gte: 3. - Use
$projectto extract the year only. - Use
$sortto 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();