AGGREGATE Movies Tutorial - Revision history

Andr3w: /* Casablanca */

2021-03-31T22:58:15Z

Casablanca

← Older revision		Revision as of 22:58, 31 March 2021
Line 29:		Line 29:
	}		}
	}]).pretty();</nowiki></pre>		}]).pretty();</nowiki></pre>
	<pre class="ans"><nowiki>db.movies.aggregate([{$match:{title:'Casablanca'}}]).pretty();</nowiki></pre>		<pre class="ans"><nowiki>db.movies.aggregate([{$match:{title:'Casablanca'}}]).pretty(); </nowiki></pre>
	</div>		</div>

40166222: /* Dark Years */

2018-10-17T13:17:08Z

Dark Years

← Older revision		Revision as of 13:17, 17 October 2018
Line 65:		Line 65:
	<div class="hint">		<div class="hint">
	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:		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 ~~<code>~~$match~~</code>~~ to select the '''Jack Black''' movies - this will include howlers such as "'''Anchorman: The Legend of Ron Burgundy'''" (2004) and "'''School of Rock'''" (2003).		*Use '''$match''' to select the '''Jack Black''' movies - this will include howlers such as "'''Anchorman: The Legend of Ron Burgundy'''" (2004) and "'''School of Rock'''" (2003).
	*Use ~~<code>~~$group~~</code>~~ together with <~~code~~>$sum: 1</~~code~~> to get the years together with a count, you will have to name the count field		*Use '''$group''' together with <syntaxhighlight lang="JavaScript" inline>$sum: 1</syntaxhighlight> to get the years together with a count, you will have to name the count field
	*Use ~~<code>~~$match~~</code>~~ to get those where the count is <~~code~~>$gte: 3</~~code~~>.		*Use '''$match''' to get those where the count is <syntaxhighlight lang="JavaScript" inline>$gte: 3</syntaxhighlight>.
	*Use ~~<code>~~$project~~</code>~~ to extract the year only.		*Use '''$project''' to extract the year only.
	*Use ~~<code>~~$sort~~</code>~~ to get the output in ascending order.		*Use '''$sort''' to get the output in ascending order.
	</div>		</div>
	<pre class="def"><nowiki>		<pre class="def"><nowiki>

40166222 at 13:07, 17 October 2018

2018-10-17T13:07:50Z

← Older revision		Revision as of 13:07, 17 October 2018
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><nowiki~~>		<syntaxhighlight lang="JavaScript">
	{		{
	"_id" : 10522,		"_id" : 10522,
Line 19:		Line 19:
	]		]
	}		}
	</~~nowiki></pre~~>		</syntaxhighlight>
	==Casablanca==		==Casablanca==
	<div class="q" data-lang="mongo">		<div class="q" data-lang="mongo">

40166222 at 08:35, 22 June 2018

2018-06-22T08:35:53Z

← Older revision		Revision as of 08:35, 22 June 2018
Line 28:		Line 28:
	title: 'Alien'		title: 'Alien'
	}		}
	}]).pretty();		}]).pretty();</nowiki></pre>
	</nowiki></pre>
	<pre class="ans"><nowiki>db.movies.aggregate([{$match:{title:'Casablanca'}}]).pretty();</nowiki></pre>		<pre class="ans"><nowiki>db.movies.aggregate([{$match:{title:'Casablanca'}}]).pretty();</nowiki></pre>
	</div>		</div>

40166222 at 08:35, 22 June 2018

2018-06-22T08:35:21Z

← Older revision		Revision as of 08:35, 22 June 2018
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>

40166222: Tidy.

2018-04-10T19:36:00Z

Tidy.

Show changes

Andr3w: /* Dark Years */

2016-10-24T10:12:52Z

Dark Years

← Older revision		Revision as of 10:12, 24 October 2016
Line 91:		Line 91:
	<div class=i>List the years in which Jack Black made three or more movies.</div>		<div class=i>List the years in which Jack Black made three or more movies.</div>
	<div class=hint>		<div class=hint>
	One way to do this is to set up an aggregate pipeline with the following stages:		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 $match to select the Jack Black movies		*Use $match to select the Jack Black movies - this will include howlers such as "Anchorman: The Legend of Ron Burgundy" (2004) and "School of Rock" (2003)
	*Use $group together with $sum:1 to get the years together with a count, you will have to name the count field		*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 $match to get those where the count is $gte:3

Andr3w: /* Dark Years */

2016-10-17T20:43:07Z

Dark Years

@@ Line 57: / Line 57: @@
      {$sort:{yr:-1}},
      {$limit:10}
 ])
 </pre>

Andr3w: /* Tom Hanks */

2016-10-17T20:23:11Z

Tom Hanks

← Older revision		Revision as of 20:23, 17 October 2016
Line 46:		Line 46:
	cast:'Harrison Ford'		cast:'Harrison Ford'
	}},		}},
	{$project:{_id:0,title:1}		{$project:{_id:0,title:1}}
	]).pretty()		]).pretty()
	</pre>		</pre>

Andr3w: /* Dark Years */

2016-10-17T20:22:58Z

Dark Years

← Older revision		Revision as of 20:22, 17 October 2016
Line 78:		Line 78:
	cast:'Harrison Ford'		cast:'Harrison Ford'
	}},		}},
	{$project:{_id:0,title:1}		{$project:{_id:0,title:1}}
	]).pretty()		]).pretty()
	</pre>		</pre>