MongoDB聚合操作Aggregation

MongoDB聚合操作Aggregation，主要用于处理数据统计，并返回计算后的数据结果

在mongodb官网https://docs.mongodb.com/manual/， 左侧菜单 preference -> operators -> aggregation pipeline operators-> pipeline aggregation stages ，列出来所有的操作命令，常用的$match, $group来做例子说明 。

1. $match 匹配，想到与sql中等于，格式 : { $match: { <query> } }

Example：

Collection : articles

{ "_id" :ObjectId("512bc95fe835e68f199c8686"), "author" :"dave", "score" : 80, "views" : 100 }

{ "_id" :ObjectId("512bc962e835e68f199c8687"), "author" :"dave", "score" : 85, "views" : 521 }

{ "_id" :ObjectId("55f5a192d4bede9ac365b257"), "author" :"ahn", "score" : 60, "views" : 1000 }

{ "_id" :ObjectId("55f5a192d4bede9ac365b258"), "author" :"li", "score" : 55, "views" : 5000 }

{ "_id" :ObjectId("55f5a1d3d4bede9ac365b259"), "author" :"annT", "score" : 60, "views" : 50 }

{ "_id" :ObjectId("55f5a1d3d4bede9ac365b25a"), "author" :"li", "score" : 94, "views" : 999 }

{ "_id" :ObjectId("55f5a1d3d4bede9ac365b25b"), "author" :"ty", "score" : 95, "views" : 1000 }

查询:

db.articles.aggregate(

    [ { $match : { author : "dave" }} ]

);

结果：

{ "_id" :ObjectId("512bc95fe835e68f199c8686"), "author" :"dave", "score" : 80, "views" : 100 }

{ "_id" :ObjectId("512bc962e835e68f199c8687"), "author" :"dave", "score" : 85, "views" : 521 }

2. $group 分组统计，格式：{ $group: { _id: <expression>,<field1>: { <accumulator1> : <expression1> }, ... } }

Example:

Collection: sales

{ "_id" :1, "item" : "abc", "price" : 10,"quantity" : 2, "date" :ISODate("2014-03-01T08:00:00Z") }

{ "_id" :2, "item" : "jkl", "price" : 20,"quantity" : 1, "date" :ISODate("2014-03-01T09:00:00Z") }

{ "_id" :3, "item" : "xyz", "price" : 5,"quantity" : 10, "date" :ISODate("2014-03-15T09:00:00Z") }

{ "_id" :4, "item" : "xyz", "price" : 5,"quantity" : 20, "date" :ISODate("2014-04-04T11:21:39.736Z") }

{ "_id" :5, "item" : "abc", "price" : 10,"quantity" : 10, "date" :ISODate("2014-04-04T21:23:13.331Z") }

 查询：

db.sales.aggregate(

   [

      {

        $group : {

           _id : { month: { $month:"$date" }, day: { $dayOfMonth: "$date" }, year: { $year:"$date" } },

           totalPrice: { $sum: { $multiply: ["$price", "$quantity" ] } },

           averageQuantity: { $avg:"$quantity" },

           count: { $sum: 1 }

        }

      }

   ]

)

结果：

{ "_id" :{ "month" : 3, "day" : 15, "year" : 2014 },"totalPrice" : 50, "averageQuantity" : 10,"count" : 1 }

{ "_id" :{ "month" : 4, "day" : 4, "year" : 2014 },"totalPrice" : 200, "averageQuantity" : 15,"count" : 2 }

{ "_id" :{ "month" : 3, "day" : 1, "year" : 2014 },"totalPrice" : 40, "averageQuantity" : 1.5,"count" : 2 }

--- $gourp支持accumulator逻辑运算方法($sum, $avg等)，在上面的例子中使用了日期方法($month, $ dayOfMonth, $year, 日期聚合:https://docs.mongodb.com/manual/reference/operator/aggregation-date/)

Mongo的key-value形式，在聚合操作的语法中也对应使用，注意些$, ‘’ 的位置

3. $match $group 的组合使用

第一个例子

db.articles.aggregate([

  { $match: { $or: [ { score: { $gt: 70, $lt:90 } }, { views: { $gte: 1000 } } ] } },

  { $group: { _id: null, count: { $sum: 1 } } }

] );

结果：

{ "_id" :null, "count" : 5 }

4. mongo的日期处理，时区、格式都比较麻烦

{ $match : {lastLocatedTime : { $gte : ISODate(\"2017-06-01T16:00:02.531Z\"), $lt: ISODate(‘2017-06-01T16:10:02.531Z’) } } }

-- - ISODate不加’’(引号)；xxxx-xx-xxTxx:xx:xx.xZ的格式；结果是没有时区的日期显示