MongoDB Aggregate

1. $sum: add 1 for each document in group

example1:

select manufacturer,count(*) from xx

group by manufacturer

{
    "_id" : ObjectId("50b1aa983b3d0043b51b2c52"),
    "name" : "Nexus 7",
    "category" : "Tablets",
    "manufacturer" : "Google",
    "price" : 199
}

 

db.products.aggregate([  // don't forget the bracket
    {$group:
     {
	 _id:"$manufacturer", 
	 num_products:{$sum:1}
     }
    }
])

 result

{ "_id" : "Amazon", "num_products" : 2 }
{ "_id" : "Sony", "num_products" : 1 }
{ "_id" : "Samsung", "num_products" : 2
{ "_id" : "Google", "num_products" : 1 }
{ "_id" : "Apple", "num_products" : 4 }

 example2:

sum pop for each state

{
    "city" : "CLANTON",
    "loc" : [
        -86.642472,
        32.835532
    ],
    "pop" : 13990,
    "state" : "AL",
    "_id" : "35045"
}

db.zips.aggregate([
{$group:
    {_id:"$state","population":{$sum:"$pop"}}
}
])

{ "_id" : "WA", "population" : 4866692 }
{ "_id" : "HI", "population" : 1108229 }
{ "_id" : "CA", "population" : 29754890 }
{ "_id" : "OR", "population" : 2842321 }
{ "_id" : "NM", "population" : 1515069 }
{ "_id" : "UT", "population" : 1722850 }
{ "_id" : "OK", "population" : 3145585 }
{ "_id" : "LA", "population" : 4217595 }

group id as a document

db.zips.aggregate([{$group:{"_id":{"state":"$state","city":"$city"}}}])

 

2. $avg: average

average population of a zipcode for each state

db.zips.aggregate([
{$group:
    {_id:"$state","avg_pop":{$avg:"$pop"}}
}
])

 3. $addToSet

find all zipcode for a city

{ "city" : "PALO ALTO", "loc" : [ -122.149685, 37.444324 ], "pop" : 15965, "state" : "CA", "_id" : "94301" }
{ "city" : "PALO ALTO", "loc" : [ -122.184234, 37.433424 ], "pop" : 1835, "state" : "CA", "_id" : "94304" }
{ "city" : "PALO ALTO", "loc" : [ -122.127375, 37.418009 ], "pop" : 24309, "state" : "CA", "_id" : "94306" }

 

db.zips.aggregate([
{$group:
    {"_id":"$city","postal_codes":{$addToSet:"$_id"}}
}])

 4. $push: similar to $addToSet, but has duplicate

 5. $max, $min

6. double $group stage

{ "_id" : 0, "a" : 0, "b" : 0, "c" : 21 }
{ "_id" : 1, "a" : 0, "b" : 0, "c" : 54 }
{ "_id" : 2, "a" : 0, "b" : 1, "c" : 52 }
{ "_id" : 3, "a" : 0, "b" : 1, "c" : 17 }
{ "_id" : 4, "a" : 1, "b" : 0, "c" : 22 }
{ "_id" : 5, "a" : 1, "b" : 0, "c" : 5 }
{ "_id" : 6, "a" : 1, "b" : 1, "c" : 87 }
{ "_id" : 7, "a" : 1, "b" : 1, "c" : 97 }

db.fun.aggregate([
{$group:
    {_id:{a:"$a", b:"$b"}, c:{$max:"$c"}}
}, 
{$group:
    {_id:"$_id.a", c:{$min:"$c"}}
}])

 7. $projection

data

{
    "city" : "ACMAR",
    "loc" : [
        -86.51557,
        33.584132
    ],
    "pop" : 6055,
    "state" : "AL",
    "_id" : "35004"
}

project to

{
    "city" : "acmar",
    "pop" : 6055,
    "state" : "AL",
    "zip" : "35004"
}

cmd

db.zips.aggregate([
{$project:
    {_id:0, city:{$toLower:"$city"}, pop:1, state:1, zip:"$_id"}
}
])

 8. $match

filters for zipcodes with pop greater than 100,000 people

db.zips.aggregate([
{$match:
    {"pop":{$gt:100000}}
}
])

match or

db.zips.aggregate([{$match:{"state":{$in:["NY","CA"]}}}])

$match:{$or:[{author:"dave"},{author:"john"}]}

http://stackoverflow.com/questions/16902930/mongodb-aggregation-framework-match-or 

9. full text search

the search field should have text index before searcg

db.s.aggregate([
    {$match:
        {$text:{$search:"tree rat"}}
    }
])

 10. $sort

 sort by (state, city), both ascending

db.zips.aggregate([
{$sort:
    {"state":1,"city":1}}
])

 11. $unwind: opposite of $push

db.posts.aggregate([
    /* unwind by tags */
    {"$unwind":"$tags"}
]}

 12. $out: redirect to a new collection, drop previous if exists

db.a.aggregate([
    {$out:"collection_name"}
])

 13. aggregation option

explain

db.zips.aggregate([{$group:{_id:"$state",popu:{$sum:"$pop"}}}],
    {explain:true}
)

db.zips.aggregate([{$group:{_id:"$state",popu:{$sum:"$pop"}}}],
    {allowDiskUse:true}
)