AGGREGATE Tutorial - Revision history

Andr3w: /* Messing with continent names 4 */

2021-03-31T22:53:37Z

Messing with continent names 4

@@ Line 270: / Line 270: @@
 ]);
 </nowiki></pre>
 </div>

2021-03-31T22:53:21Z

Messing with continent names 3

@@ Line 270: / Line 270: @@
 ]);
 </nowiki></pre>
 </div>

2021-03-31T22:38:48Z

Messing with continent names 2

← Older revision		Revision as of 22:38, 31 March 2021
Line 246:		Line 246:
	</div>		</div>

	==~~Messing with continent names 2~~==		==Country populations by order of magnitude==
	<div class="q" data-lang="mongo">		<div class="q" data-lang="mongo">
	Show the ~~<b>name</b> and the <b>continent</b> for~~ countries ~~beginning with N - but replace~~ the ~~continent <b>Oceania</b> with <b>Australasia</b>~~.		Show the number of countries in each order.

			For example UK has a population of 64,105,700 so it belongs in the 10,000,000 bucket.

			You will need the functions $floor, $log10 and $pow

	<pre class="def"><nowiki>		<pre class="def"><nowiki>
	db.world.aggregate([		db.world.aggregate([
Line 259:		Line 264:
	}}		}}
	]);</nowiki></pre>		]);</nowiki></pre>
	<pre class="ans"><nowiki>db.world.aggregate([{"$~~match"~~:{"name":{"$~~regex"~~:"^N"}}},{"$~~project"~~:{"_id":0,"~~name~~":2,~~"continent"~~:{"$~~cond"~~:[{"$~~eq"~~:~~["$continent","Oceania"]},"Australasia","$continent"]}~~}}]);</nowiki></pre>		<pre class="ans"><nowiki>db.world.aggregate([
			{$project: {name:1,l10:{$pow:[10,{$floor:{$log10:"$population"}}]}}},
			{$group:{_id:"$l10",c:{$sum:1}}},
			{$sort:{_id:1}}
			]);
			</nowiki></pre>
	</div>		</div>

2021-03-31T22:02:34Z

Messing with continent names 2

← Older revision		Revision as of 22:02, 31 March 2021
Line 259:		Line 259:
	}}		}}
	]);</nowiki></pre>		]);</nowiki></pre>
	<pre class="ans"><nowiki>db.world.aggregate([{"$match":{"name":{"$regex":"^N"}}},{"$project":{"_id":0,"name":2,"continent":{"$cond":[{"$eq":["$continent","Oceania"]},"Australasia","$continent"]}}}]) ; </nowiki></pre>		<pre class="ans"><nowiki>db.world.aggregate([{"$match":{"name":{"$regex":"^N"}}},{"$project":{"_id":0,"name":2,"continent":{"$cond":[{"$eq":["$continent","Oceania"]},"Australasia","$continent"]}}}]);</nowiki></pre>
	</div>		</div>

2021-03-31T22:02:05Z

Messing with continent names 2

← Older revision		Revision as of 22:02, 31 March 2021
Line 259:		Line 259:
	}}		}}
	]);</nowiki></pre>		]);</nowiki></pre>
	<pre class="ans"><nowiki>db.world.aggregate([{"$match":{"name":{"$regex":"^N"}}},{"$project":{"_id":0,"name":1,"continent":{"$cond":[{"$eq":["$continent","Oceania"]},"Australasia","$continent"]}}}]);</nowiki></pre>		<pre class="ans"><nowiki>db.world.aggregate([{"$match":{"name":{"$regex":"^N"}}},{"$project":{"_id":0,"name":2,"continent":{"$cond":[{"$eq":["$continent","Oceania"]},"Australasia","$continent"]}}}]) ; </nowiki></pre>
	</div>		</div>

2021-03-31T21:57:34Z

← Older revision		Revision as of 21:57, 31 March 2021
Line 243:		Line 243:
	}}		}}
	]);</nowiki></pre>		]);</nowiki></pre>
	<pre class="ans"><nowiki>db.world.aggregate([{"$group":{"_id":{"$cond":[{"$eq":["$continent","South America"]},"America",{"$cond":[{"$eq":["$continent","North America"]},"America","$continent"]}]},"area":{"$sum":"$area"}}},{"$sort":{"area":-1}},{"$project":{"_id":1,"area":1}}]);</nowiki></pre>		<pre class="ans"><nowiki>db.world.aggregate([{"$group":{"_id":{"$cond":[{"$eq":["$continent","South America"]},"America",{"$cond":[{"$eq":["$continent","North America"]},"America","$continent"]}]},"area":{"$sum":"$area"}}},{"$sort":{"area":-1}},{"$project":{"_id":1,"area":1}}]); </nowiki></pre>
	</div>		</div>

2020-12-11T19:34:50Z

$group on continent

← Older revision		Revision as of 19:34, 11 December 2020
Line 13:		Line 13:
	db.world.aggregate({		db.world.aggregate({
	$group: {		$group: {
	_id: '~~world~~',		_id: '$continent',
	res: {		res: {
	$sum: 1		$sum: '$population'
	}		}
	}		}
Line 21:		Line 21:
	<pre class="ans"><nowiki>db.world.aggregate({		<pre class="ans"><nowiki>db.world.aggregate({
	$group: {		$group: {
	_id: '~~world~~',		_id: '$continent',
	res: {		res: {
	$sum: 1		$sum: 1

2020-12-11T19:33:58Z

$group on continent

← Older revision		Revision as of 19:33, 11 December 2020
Line 6:		Line 6:
	==$group on continent==		==$group on continent==
	<div class="q" data-lang="mongo">		<div class="q" data-lang="mongo">
	The aggregate method allows a <code>$group</code> - you must specify the <code>_id</code> and you can use aggregating functions such as $sum $min $max $push		The aggregate method allows a <code>$group</code> - you must specify the <code>_id</code> and you can use aggregating functions such as <code>$sum</code> <code>$min</code> <code>$max</code> <code>$push</code>

			The sample code shows the total population of each continent.
			<div class='imper'>Show the number of countries in each continent.</div>
	<pre class="def"><nowiki>		<pre class="def"><nowiki>
	db.world.aggregate([		db.world.aggregate({
	{$~~match~~: {		$group: {
	~~population~~: ~~{$gte: 250000000}~~		_id: 'world',
	}},		res: {
	~~{$project~~: {		$sum: 1
	~~_id: 0,~~		}
	~~name~~: 1,		}
	~~"per capita GDP": {$divide: ['$gdp', 1000000]~~}		});</nowiki></pre>
	}}
	]);</nowiki></pre>
	<pre class="ans"><nowiki>db.world.aggregate({		<pre class="ans"><nowiki>db.world.aggregate({
	$group: {		$group: {

2020-12-11T19:30:51Z

← Older revision		Revision as of 19:30, 11 December 2020
Line 283:		Line 283:
	<pre class="ans"><nowiki>db.world.aggregate([{"$project":{"_id":0,"name":1,"original":"$continent","new":{"$cond":[{"$or":[{"$eq":["$continent","Eurasia"]},{"$eq":["$name","Turkey"]}]},"Europe/Asia",{"$cond":[{"$eq":["$continent","Oceania"]},"Australasia",{"$cond":[{"$and":[{"$eq":["$continent","Caribbean"]},{"$eq":[{"$substr":["$name",0,1]},"B"]}]},"North America",{"$cond":[{"$and":[{"$eq":["$continent","Caribbean"]},{"$ne":[{"$substr":["$name",0,1]},"B"]}]},"South America","$continent"]}]}]}]}}}]);</nowiki></pre>		<pre class="ans"><nowiki>db.world.aggregate([{"$project":{"_id":0,"name":1,"original":"$continent","new":{"$cond":[{"$or":[{"$eq":["$continent","Eurasia"]},{"$eq":["$name","Turkey"]}]},"Europe/Asia",{"$cond":[{"$eq":["$continent","Oceania"]},"Australasia",{"$cond":[{"$and":[{"$eq":["$continent","Caribbean"]},{"$eq":[{"$substr":["$name",0,1]},"B"]}]},"North America",{"$cond":[{"$and":[{"$eq":["$continent","Caribbean"]},{"$ne":[{"$substr":["$name",0,1]},"B"]}]},"South America","$continent"]}]}]}]}}}]);</nowiki></pre>
	</div>		</div>
	~~[https://goo.gl/forms/ep8rBbCQSa381ic82 {{huge\| Survey}}] <br/>~~
	~~Do you have thoughts about this website that you would like to share? Help improve NoSQLZoo!~~

2020-12-11T19:25:50Z

$group on continent

← Older revision		Revision as of 19:25, 11 December 2020
Line 6:		Line 6:
	==$group on continent==		==$group on continent==
	<div class="q" data-lang="mongo">		<div class="q" data-lang="mongo">
	The aggregate method allows a <code>$group/code> - you must specify the <code>_id</code> and you can use aggregating functions such as $sum $min $max $push		The aggregate method allows a <code>$group</code> - you must specify the <code>_id</code> and you can use aggregating functions such as $sum $min $max $push
	<pre class="def"><nowiki>		<pre class="def"><nowiki>
	db.world.aggregate([		db.world.aggregate([
Line 28:		Line 28:
	</nowiki></pre>		</nowiki></pre>
	</div>		</div>


	==Per Capita GDP==		==Per Capita GDP==