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

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

$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.

pp.pprint(list(
    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
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.

pp.pprint(list(
    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
        }}
    ])
))

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

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