Mongo Aggregation examples

gistfile1.txt

Query to group by a category (from shell) 

db.products.aggregate ([
     {"$group":
      {
        "_id":"$category",
        "num_products":{"$sum":1}
      }}
])


Sum

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


Avg

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

Add to set

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

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

Max/min

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



Project

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

       }
  }
])

Match

db.zips.aggregate([
     { $match :
          { state : "PA" }
     } ,
     { $group : {
          _id : "$city" ,
          population : { $sum : "$pop"} ,
          zips : { $addToSet : "$_id" }
      }
     },
     { $project :
       {
         _id:0,
         city: "$_id",
         population:1,
         zips:1
       }
     },
     { $sort :
       {
         city : 1,
         zips : 1
       }

     }
])

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

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

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

     }
])


db.messages.aggregate([
     { $project: {
          "headers.From": 1,
          "headers.To": 1
        }
     },
     { $unwind : "$headers.To"},
     { $group : {
          _id : { from : "$headers.From", to : "$headers.To" },
          "total_messages" : { $sum : 1},
     }},
     { $sort :
       {
         total_messages : -1
       }
     },
     { $limit : 20 }
])


db.zips.aggregate ([ {"$group": {
        "_id":"$state",
        "total_zips":{"$sum":1}
      }},
     { $sort :

       {
         "total_zips" : -1
       }
     },
     { "$limit" : 20 }
])