Difference between revisions of "AGGREGATE Tutorial"

Latest revision as of 22:53, 31 March 2021

1 Country Profile
2 $group on continent
3 Per Capita GDP
4 Population Density in South America
5 Population Density for "V"
6 Population in millions
7 Population density
8 Continents by area
9 Big Continents
10 First and last country by continent
11 Countries beginning with...
12 Harder Questions
13 Messing with continent names
14 Country populations by order of magnitude

Country Profile

For these questions you should use aggregate([]) on the collection world

You may find these AGGREGATE examples useful.

$group on continent

The aggregate method allows a $group - you must specify the _id and you can use aggregating functions such as $sum $min $max $push

The sample code shows the total population of each continent.

Show the number of countries in each continent.

db.world.aggregate({
  $group: {
    _id: '$continent',
    res: {
      $sum: '$population'
    }
  }
});

db.world.aggregate({
  $group: {
    _id: '$continent',
    res: {
      $sum: 1
    }
  }
});

Per Capita GDP

Give the name and the per capita GDP for those countries with a population of at least 200 million.

per capita GDP is the GDP divided by the population.

db.world.aggregate([
    {$match: {
        population: {$gte: 250000000}
    }},
    {$project: {
        _id: 0,
        name: 1,
        "per capita GDP": {$divide: ['$gdp', 1000000]}
    }}
]);

db.world.aggregate([{"$match":{"population":{"$gte":200000000}}},{"$project":{"_id":0,"name":1,"per capita GDP": {"$divide": ["$gdp","$population"]}}}]);

Population Density in South America

Give the name and the population density of all countries in South America.

population density is the population divided by the area

Use a $match. {"area":{"$ne":0}}

db.world.aggregate([
    {$match: {continent: 'Asia'}},
    {$project: {
        _id: 0,
        name: 1,
        density: {$divide: ["$population", "$area"]}
    }}
]);

db.world.aggregate([{$match:{continent:'South America'}},{$project:{_id:0,name:1,density:{$divide:["$population","$area"]}}}]);

Population Density for "V"

Give the name and the population density of all countries with name after V in the alphabet.

Note that because Vatican City (with area 0) is in Europe you will get a divide by zero error unless you filter first.

Use a $match.

{ 
  $match: {
    area: {
      "$ne": 0
    }
  }
}

db.world.aggregate([
    {$match: {name: {$gt: 'V'}}},
    {$project: {
        _id: 0,
        name: 1,
        area: 1
    }}
]);

db.world.aggregate([{$match:{name:{$gt:'V'}}},{$match:{area:{"$ne":0}}},{$project:{_id:0,name:1,density:{$divide:["$population","$area"]}}}]);

Population in millions

Show the name and population in millions for the countries of the continent South America. Divide the population by 1000000 to get population in millions.

db.world.aggregate([
    {$match:{

    }},
    {$project:{
        _id: 0,
        name: 1
    }}
]);

db.world.aggregate([{"$match":{"continent":{"$eq":"South America"}}},{"$project":{"_id":0,"name":1,"population":{"$divide":["$population",1000000]}}}]);

Population density

Show the name and population density for France, Germany, and Italy

db.world.aggregate([
    {$match:{
        name: {$in: ['United Kingdom', 'United States', 'Brazil']},
        population: {$ne: null},
        area: {$ne: 0}
    }},
    {$project:{
        _id: 0,
        name: 1
    }}
]);

db.world.aggregate([{"$match":{"name":{"$in":['France','Germany','Italy']},"population":{"$ne":null},"area":{"$ne":0}}},{"$project":{"_id":0,"name":1,"population density":{"$divide":["$population","$area"]}}}]);

Continents by area

Order the continents by area from most to least.

db.world.aggregate([
    {$group: {
        _id: "$name",
        area: {$max: "$area"}
    }},
    {$sort: {
        area: -1
    }},
    {$project: {
        _id: 1,
        area: 1
    }}
]);

db.world.aggregate([{"$group":{"_id":"$continent","area":{"$sum":"$area"}}},{"$sort":{"area":-1}},{"$project":{"_id":1,"area":1}}]);

Big Continents

Show the only two continents with total area greater than 25000000 and then sort from largest to smallest.

db.world.aggregate([
  {$match: {
      continent: "North America"
  }},
  {$project: {
      _id: 0,
      name: 1
  }}
]);

db.world.aggregate([{$group:{_id:"$continent",area:{$sum:"$area"}}},{$sort:{area:-1}},{$match:{area:{$gt:25000000}}}]);

First and last country by continent

For each continent show the first and last country alphabetically like this:

 { "_id" : "Africa", "from" : "Algeria", "to" : "Zimbabwe" }
 { "_id" : "Asia", "from" : "Afghanistan", "to" : "Yemen" }
 { "_id" : "Caribbean", "from" : "Antigua and Barbuda", "to" : "Trinidad and Tobago" }
 { "_id" : "Eurasia", "from" : "Armenia", "to" : "Russia" }
 { "_id" : "Europe", "from" : "Albania", "to" : "Vatican City" }
 { "_id" : "North America", "from" : "Belize", "to" : "United States" }
 { "_id" : "Oceania", "from" : "Australia", "to" : "Vanuatu" }
 { "_id" : "South America", "from" : "Argentina", "to" : "Venezuela" }

db.world.aggregate([
  {$group: {
      _id: "$continent"
  }},
  {$sort: {
      _id: 1
  }}
]);

db.world.aggregate([{$sort:{name:1}},{$group:{_id:'$continent',from:{$first:'$name'},to:{$last:'$name'}},},{$sort:{_id:1}}])

Countries beginning with...

Group countries according to the first letter of the name. As shown. Only give "U" through to "Z".

You will need to use the $substr function and the $push aggregate function.

 { "_id" : "U", "list" : [ "Uganda", "Ukraine", "United Arab Emirates", "United Kingdom", "United States", "Uruguay", "Uzbekistan" ] }
 { "_id" : "V", "list" : [ "Vanuatu", "Vatican City", "Venezuela", "Vietnam" ] }
 { "_id" : "Y", "list" : [ "Yemen" ] }
 { "_id" : "Z", "list" : [ "Zambia", "Zimbabwe" ] }

db.world.aggregate([
  {$project: {
    _id: '$name',
    startsWith: {$substr: ['$name', 0, 1]}
  }},
  {$match: {
    _id: {$gte: 'U'}
  }},
  {$sort: {_id: 1}}
]);

db.world.aggregate([{$group:{_id:{$substr:['$name',0,1]},list:{$push:'$name'}}},{$match:{_id:{$gte:'U'}}},{$sort:{_id:1}}]);

Harder Questions

Messing with continent names

Combine North America and South America to America, and then list the continents by area. Biggest first.

db.world.aggregate([
  {$group: {
    _id: {
      $cond: [
        {$eq: ["$continent", "North America"]},
        "America",
        {$cond: [
          {$eq: ["$continent", "Asia"]},
          "The East",
          "$continent"
        ]}
      ]
    },
    area: {$sum: "$area"}
  }},
  {$sort: {area: -1}},
  {$project: {
    _id: 1,
    area: 1
  }}
]);

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}}]);

Country populations by order of magnitude

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

db.world.aggregate([
    {$match: {
        name: {$regex: "^N"}
    }},
    {$project: {
        _id: 0,
        name: 1
    }}
]);

db.world.aggregate([
    {$project: {name:1,l10:{$pow:[10,{$floor:{$log10:"$population"}}]}}},
    {$group:{_id:"$l10",c:{$sum:1}}},
    {$sort:{_id:1}}
]);

@@ Line 6: / Line 6: @@
 ==$group on continent==
 <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>
-db.world.aggregate([
+db.world.aggregate({
-    {$match: {
+  $group: {
-        population: {$gte: 250000000}
+    _id: '$continent',
-    }},
+     res: {
-     {$project: {
+      $sum: '$population'
-        _id: 0,
+     }
-        name: 1,
+  }
-        "per capita GDP": {$divide: ['$gdp', 1000000]}
+});</nowiki></pre>
-     }}
-]);</nowiki></pre>
 <pre class="ans"><nowiki>db.world.aggregate({
    $group: {
-     _id: 'world',
+     _id: '$continent',
      res: {
        $sum: 1
@@ Line 242: / Line 243: @@
    }}
 ]);</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>
-==Messing with continent names 2==
+==Country populations by order of magnitude==
 <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>
 db.world.aggregate([
@@ Line 258: / Line 264: @@
      }}
 ]);</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([
-</div>
+    {$project: {name:1,l10:{$pow:[10,{$floor:{$log10:"$population"}}]}}},
+    {$group:{_id:"$l10",c:{$sum:1}}},
-==Messing with continent names 3==
+    {$sort:{_id:1}}
-<div class="q" data-lang="mongo">
+]);
-Show the <b>name</b> and the <b>continent</b> but:<br/><br/>
+</nowiki></pre>
-*substitute <b>Eurasia</b> for <b>Europe</b> and <b>Asia</b>.<br/>
-*substitute <b>America</b> - for each country in <b>North America</b> or <b>South America</b> or <b>Caribbean</b>.<br/><br/>Only show countries beginning with <b>A</b> or <b>B</b><br/>
-If you're struggling you may want to experiment with <code>$and</code>,<code>$or</code>, etc.
-<pre class="def"><nowiki></nowiki></pre>
-<pre class="ans"><nowiki>db.world.aggregate([{$match:{name:{$regex:"^A|^B"}}},{$project:{_id:0,name:1,continent:{$cond:[{$or:[{$eq:["$continent","Europe"]},{$eq:["$continent","Asia"]}]},"Eurasia",{$cond:[{$or:[{$eq:["$continent","North America"]},{$eq:["$continent","South America"]},{$eq:["$continent","Caribbean"]}]},"America","$continent"]}]}}}]);</nowiki></pre>
-</div>
-==Messing with continent names 4==
-<div class="q" data-lang="mongo">
-Put the continents right...
-<br/><br/>
-*<b>Oceania</b> becomes <b>Australasia</b><br/>
-*Countries in <b>Eurasia</b> and <b>Turkey</b> go to <b>Europe/Asia</b><br/>
-*<b>Caribbean</b> islands starting with '<b>B</b>' go to <b>North America</b>, other Caribbean islands go to <b>South America</b><br/>
-<br/><br/>
-<b>Show the name, the original continent and the new continent of all countries.</b>
-<pre class="def"></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>