Difference between revisions of "AGGREGATE Tutorial"

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.

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

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

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

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

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

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

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.

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

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

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

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

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

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

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

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

Show the name and the continent for countries beginning with N - but replace the continent Oceania with Australasia.

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

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

Show the name and the continent but:

• substitute Eurasia for Europe and Asia.
  
• substitute America - for each country in North America or South America or Caribbean.
  
  Only show countries beginning with A or B
  

If you're struggling you may want to experiment with $and,$or, etc.

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

Put the continents right...

• Oceania becomes Australasia
  
• Countries in Eurasia and Turkey go to Europe/Asia
  
• Caribbean islands starting with 'B' go to North America, other Caribbean islands go to South America
  

Show the name, the original continent and the new continent of all countries.

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