Difference between revisions of "AGGREGATE examples"

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

Show all the details for France

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

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

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

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

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

A downside of grouping is that it is not possible to find the $name of the country with the min/max value.

A simple way is to do this combine a sort sort=[(<fieldname>, <direction>)] and a find_one(). Performance will be improved if these fields have previously been indexed.

Get the names and GDPs of the two countries with the smallest and largest GDPs.

pp.pprint(
    db.world.find_one({"gdp":{"$ne":None}},{"name":1,"gdp":1,"_id":0},sort=[("gdp", 1)])
)
pp.pprint(
    db.world.find_one({},{"name":1,"gdp":1,"_id":0},sort=[("gdp", -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
        }}
    ])
))