Difference between revisions of "AGGREGATE examples"

$group allows you to collect group items that share common features

• _id - this determines the values to be grouped
• $continent and $population refers to keys 'continent' and 'population' available in each item
• $sum is an aggregating function, it takes many values in and returns a single value. Other examples of aggregating functions are $min $max, $avg

List the continents

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

$match performs queries in a similar way to find()

Show all the details for France

db.world.aggregate([
    {$match: {name: "France"}}
]);

db.world.aggregate([{$match:{name:"France"}}]);

Return the first two document

db.world.aggregate([
    {$limit: 2}
]);

db.world.aggregate([{"$limit":2}]);

$project selects what fields to display.
It can also has the ability to create new fields and to compare fields against each other without using $where

Show the name and population density of all Asian countries. (population/area)

Note that "density" is a new field made from the result of dividing two existing fields, and that $divide is an aggregate function.

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

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

You can have several pipeline stages, the data flows through each one in turn.

Show the name of Asian countries with a density that's over 500 people per km². (population/area)

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

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

$sort allows ordering of the results set, where 1 is ascending and -1 is descending.
Note that not including $match is the same as {"$match":{}}

Show the name of all countries in descending order.

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

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

$max and $min can be used to get the largest and smallest values in a group.

Get the smallest and largest GDPs of each continent.

db.world.aggregate([
    {$group: {
        _id: '$continent',
        min: {$min: "$gdp"},
        max: {$max: "$gdp"}
    }},
    {$project: {
        _id: 1,
        min: 1,
        max: 1
    }}
]);

db.world.aggregate([{"$group":{'_id':'$continent','min':{"$min":"$gdp"},'max':{"$max":"$gdp"}}},{"$project":{"_id":1,"min":1,"max":1}}]);

Some other useful aggregate functions to know are $sum and average: $avg
The example below combines previous example material.

Order the continents in descending order by total GDP, Include the average GDP for each country.

db.world.aggregate([
    {$match: {}},
    {$group: {
        _id:"$continent",
        "Total GDP": {"$sum": "$gdp"},
        "Average GDP": {"$avg": "$gdp"}
    }},
    {$sort: {
        "Total GDP":-1
    }},
    {$project:{
        "Area": "$_id",
        "Total GDP": 1,
        "Average GDP": 1,
        _id: 0
    }}
]);

db.world.aggregate([{"$group":{"_id":"$continent","Total GDP":{"$sum":"$gdp"},"Average GDP":{"$avg":"$gdp"}}},{"$sort":{"Total GDP":-1}},{"$project":{"Area":"$_id","Total GDP":1,"Average GDP":1,"_id":0}}]);

Using Conditions

$cond is similar to a CASE statement in other languages.
It has the form "$cond": [{<comparison>: [<field or value>, <field or value>]}, <true case>, <false case>]

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