Difference between revisions of "AGGREGATE examples"

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

Show all the details for France

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

Return the first document

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

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

Because aggregate is a pipeline stages may be repeated, and stages don't have to be used in a specific order.

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

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

Some other useful aggregate functions to know are $sum and average: $avg
This example combines all the material in these examples.

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

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