mongodb aggregate 筆記

aggregate 為mongodb整合計算的function

以下引用官網的例子:

https://docs.mongodb.com/manual/reference/operator/aggregation/match/

{ "_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 }

$match 操作:

即條件:

db.articles.aggregate(
    [ { $match : { author : "dave" } } ]

);

等於mysql的 select * from articles where author='dave';

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

db.articles.aggregate( [
  { $match: { $or: [ { score: { $gt: 70, $lt: 90 } }, { views: { $gte: 1000 } } ] } }
] );

等於mysql的 select * from atricles where (score>70 and score<90) or (views >= 1000) 

$project 操作:

壓縮 刪除欄位 

原始資料:
{
  "_id" : 1,
  title: "abc123",
  isbn: "0001122223334",
  author: { last: "zzz", first: "aaa" },
  copies: 5

}

db.books.aggregate( [ { $project : { title : 1 , author : 1 } } ] )

只留下tilte和author, _id是自動留下, 1為true的意思

{ "_id" : 1, "title" : "abc123", "author" : { "last" : "zzz", "first" : "aaa" } }

若要刪除_id, 使用_id:0

db.books.aggregate( [ { $project : { _id: 0 ,title : 1 , author : 1 } } ] )

{ "title" : "abc123", "author" : { "last" : "zzz", "first" : "aaa" } }

$group 操作

類似mysql的group by

{ "_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 }

參考:

http://www.runoob.com/mongodb/mongodb-aggregate.html

https://docs.mongodb.com/v3.2/reference/operator/aggregation