Difference between revisions of "AGGREGATE examples"
| Line 109: | Line 109: | ||
<div class=q data-lang="py3"> | <div class=q data-lang="py3"> | ||
| − | In the previous example | + | In the previous example we can't use <code>$name<code> to get the names of the countries with the smallest and largest values as we lost the ability to associate documents when we set the <code>_id</code> to <code>null</code> to perform a grouping<br/> |
| − | If we want to do this, | + | If we want to do this, a simple way is to use <code>sort=[("uid", -1)]</code> inside a <code>find_one()</code> statement. Performance will be improved if these fields have previously been indexed. |
<p class=strong>Get the names and GDPs of the two countries with the smallest and largest GDPs.</p> | <p class=strong>Get the names and GDPs of the two countries with the smallest and largest GDPs.</p> | ||
<div class="hint" title="Avoiding null results"> | <div class="hint" title="Avoiding null results"> | ||
Revision as of 10:15, 17 July 2015
#ENCODING
import io
import sys
sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-16')
#MONGO
from pymongo import MongoClient
client = MongoClient()
client.progzoo.authenticate('scott','tiger')
db = client['progzoo']
#PRETTY
import pprint
pp = pprint.PrettyPrinter(indent=4)
Introducing the aggregation framework
These examples introduce the aggregation framework and its operators. Again we will be using the collection world
$match Allows us to perform queries in a similar way to find()
Show all the details for France
pp.pprint(list(
db.world.aggregate([
{"$match":{"name":"France"}}
])
))
pp.pprint(list(db.world.aggregate([{"$match":{"name":"France"}}])))
$project Allows us to select what fields to display.
It can also has the ability to insert new fields and allows you 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.
To avoid diving by 0 we do a $match to remove any countries with 0 area (Vatican City), then pipe these results through to $project
pp.pprint(list(
db.world.aggregate([
{"$match":{"area":{"$ne":0},"continent":"Asia"}},
{"$project":{
"_id":0,
"name":1,
"density": {"$divide": ["$population","$area"]}
}}
])
))
pp.pprint(list(db.world.aggregate([{"$match":{"area":{"$ne":0},"continent":"Asia"}},{"$project":{"_id":0,"name":1,"density":{"$divide":["$population","$area"]}}}])))
$sort Allows us to choose how the results are displayed, where 1 is ascending and -1 is descending.
Note that excluding $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
}}
])
))
pp.pprint(list(db.world.aggregate([{"$project":{"_id":0,"name":1,}},{"$sort":{"name":-1}}])))
Grouping
Grouping allows us to use accumulator operations sum as $sum
All groups must have an _id. To accumulate over all the results you can just use null
$max and $min can be used to get the largest and smallest values in a group.
Get the smallest and largest GDPs.
pp.pprint(list(
db.world.aggregate([
{"$group":{
'_id':'null',
'min':{"$min":"$gdp"},
'max':{"$max":"$gdp"},
}},
{"$project":{
"_id":0,
"min":1,
"max":1
}},
])
))
pp.pprint(list(db.world.aggregate([{"$group":{'_id':'null','min':{"$min":"$gdp"},'max':{"$max":"$gdp"},}},{"$project":{"_id":0,"min":1,"max":1}},])))
In the previous example we can't use $name to get the names of the countries with the smallest and largest values as we lost the ability to associate documents when we set the _id to null to perform a grouping
If we want to do this, a simple way is to use sort=[("uid", -1)] inside a find_one() statement. 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.
It is possible that we will occasionally encounter null values in a data collection. This could be either where the field is simply not included on a certain document, or a document has a redundancy where the field is spesified as null, none, etc.
To deal with this we can use {<field>: {"$ne": None}} to prevent any null values from being included.
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)])
)
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)]))