Mongo 聚合框架-Aggregate(三)

三 表达式操作符

6、字符串类型聚合操作符

　1)＄concat
　　作用：连接字符串，如果某个表达式字段不存在或字段值为null，则返回null
　　语法：{ $concat: [ ＜expression1>, ＜expression2>, … ] }
　　示例

{ "_id" : 1, "item" : "ABC1", quarter: "13Q1", "description" : "product 1" }{ "_id" : 2, "item" : "ABC2", quarter: "13Q4", "description" : "product 2" }{ "_id" : 3, "item" : "XYZ1", quarter: "14Q2", "description" : null }db.inventory.aggregate(   [      { $project: { itemDescription: { $concat: [ "$item", " - ", "$description" ] } } }   ])结果如下{ "_id" : 1, "itemDescription" : "ABC1 - product 1" }{ "_id" : 2, "itemDescription" : "ABC2 - product 2" }{ "_id" : 3, "itemDescription" : null }

　2)＄toLower|＄toUpper
　　作用：将一个字符串全部转为小写|大写，如果该字符串为null，则返回空字符串。
　　语法：{ ＄toLower: ＜expression> }|{ ＄toUpper: ＜expression> }
　　示例

{ "_id" : 1, "item" : "ABC1", quarter: "13Q1", "description" : "PRODUCT 1" }{ "_id" : 2, "item" : "abc2", quarter: "13Q4", "description" : "Product 2" }{ "_id" : 3, "item" : "xyz1", quarter: "14Q2", "description" : null }db.inventory.aggregate(   [     {       $project:         {           upItem: { $toLower: "$item" },           upDescription: { $toLower: "$description" },           loItem: { $toUpper: "$item" },                       loDescription: { $toUpper: "$description" }         }     }   ])结果如下{ "_id" : 1, "upItem" : "ABC1", "upDescription" : "PRODUCT 1", "loItem" : "abc1", "loDescription" : "product 1" }{ "_id" : 2, "upItem" : "ABC2", "upDescription" : "PRODUCT 2", "loItem" : "abc2", "loDescription" : "product 2" }{ "_id" : 3, "upItem" : "XYZ1", "upDescription" : "", "loItem" : "xyz1", "loDescription" : "" }

　3)＄split
　　作用：根据指定字符串分割字符串，返回分割后的字符串数组
　　语法：{ ＄split: [ ＜string expression>, ＜delimiter> ] }
　　示例

{ "_id" : 1, "city" : "Berkeley, CA", "qty" : 648 }{ "_id" : 2, "city" : "Bend, OR", "qty" : 491 }{ "_id" : 3, "city" : "Kensington, CA", "qty" : 233 }{ "_id" : 4, "city" : "Eugene, OR", "qty" : 842 }{ "_id" : 5, "city" : "Reno, NV", "qty" : 655 }{ "_id" : 6, "city" : "Portland, OR", "qty" : 408 }{ "_id" : 7, "city" : "Sacramento, CA", "qty" : 574 }汇总每个州的qty的总大小，并按其大小排序db.deliveries.aggregate([  { $project : { city_state : { $split: ["$city", ", "] }, qty : 1 } },  { $unwind : "$city_state" },  { $match : { city_state : /[A-Z]{2}/ } },  { $group : { _id: { "state" : "$city_state" }, total_qty : { "$sum" : "$qty" } } },  { $sort : { total_qty : -1 } }]);{ "_id" : { "state" : "OR" }, "total_qty" : 1741 }{ "_id" : { "state" : "CA" }, "total_qty" : 1455 }{ "_id" : { "state" : "NV" }, "total_qty" : 655 }

　4)＄substr
　5)＄indexOfBytes
　6)＄indexOfCP
　7)＄strLenBytes
　8)＄strLenCP
　9)＄strcasecmp
　10)＄substrBytes
　11)＄substrCP

7、日期类型聚合操作符

　1)年月日等
　　＄dayOfYear：该时间在一年中是第几天，范围1至366
　　＄dayOfMonth：该时间在一月中是第几天，范围1至31
　　＄dayOfWeek：该时间在一周中是第几天，范围1(Sunday)至7(Saturday)
　　＄year：该时间属于哪一年
　　＄month：该时间属于哪一月
　　＄week：该时间在一年中是第几周，范围从0至53
　　＄hour：该时间的小时
　　＄minute：该时间的分钟
　　＄second：该时间的秒数
　　＄millisecond：该时间的毫秒数
　示例

{ "_id" : 1, "item" : "abc", "price" : 10, "quantity" : 2, "date" : ISODate("2014-01-01T08:15:39.736Z") }db.sales.aggregate(   [     {       $project:         {           year: { $year: "$date" },           month: { $month: "$date" },           day: { $dayOfMonth: "$date" },           hour: { $hour: "$date" },           minutes: { $minute: "$date" },           seconds: { $second: "$date" },           milliseconds: { $millisecond: "$date" },           dayOfYear: { $dayOfYear: "$date" },           dayOfWeek: { $dayOfWeek: "$date" },           week: { $week: "$date" }         }     }   ])结果如下{  "_id" : 1,  "year" : 2014,  "month" : 1,  "day" : 1,  "hour" : 8,  "minutes" : 15,  "seconds" : 39,  "milliseconds" : 736,  "dayOfYear" : 1,  "dayOfWeek" : 4,  "week" : 0}

　2)＄dateToString
　　作用：根据要求格式化时间对象
　　语法：{ ＄dateToString: { format: ＜formatString>, date: ＜dateExpression> } }
　　示例

{"_id" : 1,"item" : "abc","price" : 10,"quantity" : 2,"date" : ISODate("2014-01-01T08:15:39.736Z")}db.sales.aggregate(  [    {      $project: {         yearMonthDayUTC: { $dateToString: { format: "%Y-%m-%d", date: "$date" } },         time: { $dateToString: { format: "%H:%M:%S:%L", date: "$date" } }      }    }  ])结果如下{ "_id" : 1, "yearMonthDayUTC" : "2014-01-01", "time" : "08:15:39:736" }

　3)＄isoDayOfWeek：
　4)＄isoWeek：
　5)＄isoWeekYear：

8、条件类型聚合操作符

　1)＄cond
　　语法
　　　{ ＄cond: { if: ＜boolean-expression>, then: ＜true-case>, else: ＜false-case-> } } 或
　　　{ ＄cond: [ ＜boolean-expression>, ＜true-case>, ＜false-case> ] }
　　作用
　　　＄cond为mongoDB的三元运算符
　　示例

{ "_id" : 1, "item" : "abc1", qty: 300 }{ "_id" : 2, "item" : "abc2", qty: 200 }{ "_id" : 3, "item" : "xyz1", qty: 250 }查询如果qty大于250，则discount为30，否则为20db.inventory.aggregate(   [      {         $project:           {             item: 1,             discount:               {                 $cond: { if: { $gte: [ "$qty", 250 ] }, then: 30, else: 20 }               }           }      }   ])或db.inventory.aggregate(   [      {         $project:           {             item: 1,             discount:               {                 $cond: [ { $gte: [ "$qty", 250 ] }, 30, 20 ]               }           }      }   ])结果如下{ "_id" : 1, "item" : "abc1", "discount" : 30 }{ "_id" : 2, "item" : "abc2", "discount" : 20 }{ "_id" : 3, "item" : "xyz1", "discount" : 30 }

　2)＄ifNull
　　作用：非空判断操作，如果为空，则用默认的值替代，如果非空，则保持原值
　　语法：{ ＄ifNull: [ ＜expression>, ＜replacement-expression-if-null> ] }
　　示例

{ "_id" : 1, "item" : "abc1", description: "product 1", qty: 300 }{ "_id" : 2, "item" : "abc2", description: null, qty: 200 }{ "_id" : 3, "item" : "xyz1", qty: 250 }db.inventory.aggregate(   [      {         $project: {            item: 1,            description: { $ifNull: [ "$description", "Unspecified" ] }         }      }   ])结果如下{ "_id" : 1, "item" : "abc1", "description" : "product 1" }{ "_id" : 2, "item" : "abc2", "description" : "Unspecified" }{ "_id" : 3, "item" : "xyz1", "description" : "Unspecified" }

　3)＄switch
　　作用：
　　语法：

$switch: {       branches: [          { case: <expression>, then: <expression> },          { case: <expression>, then: <expression> },          ...       ],       default: <expression>    }

示例

{ "_id" : 1, "name" : "Susan Wilkes", "scores" : [ 87, 86, 78 ] }{ "_id" : 2, "name" : "Bob Hanna", "scores" : [ 71, 64, 81 ] }{ "_id" : 3, "name" : "James Torrelio", "scores" : [ 91, 84, 97 ] }db.grades.aggregate( [  {    $project:      {        "name" : 1,        "summary" :        {          $switch:            {              branches: [                {                  case: { $gte : [ { $avg : "$scores" }, 90 ] },                  then: "Doing great!"                },                {                  case: { $and : [ { $gte : [ { $avg : "$scores" }, 80 ] },                                   { $lt : [ { $avg : "$scores" }, 90 ] } ] },                  then: "Doing pretty well."                },                {                  case: { $lt : [ { $avg : "$scores" }, 80 ] },                  then: "Needs improvement."                }              ],              default: "No scores found."            }         }      }   }] )结果如下{ "_id" : 1, "name" : "Susan Wilkes", "summary" : "Doing pretty well." }{ "_id" : 2, "name" : "Bob Hanna", "summary" : "Needs improvement." }{ "_id" : 3, "name" : "James Torrelio", "summary" : "Doing great!" }

9、集合类型聚合操作符

　1)＄setEquals
　2)＄setIntersection
　　语法：
　　　{ ＄setIntersection: [ ＜array1>, ＜array2>, … ] }
　　作用
　　　获取两个或多个数组的交集
　　示例

{ "_id" : 4, "A" : [ "red", "blue" ], "B" : [ "green", "red" ] }db.experiments.aggregate([{ $project: { A: 1, B: 1, commonToBoth: { $setIntersection: [ "$A", "$B" ] }, _id: 0 } }])结果如下{ "A" : [ "red", "blue" ], "B" : [ "green", "red" ], "commonToBoth" : [ "red" ] }

　3)＄setUnion
　4)＄setDifference
　5)＄setIsSubset
　6)＄anyElementTrue
　7)＄allElementsTrue

10、文本搜索类型聚合操作符

　1)＄meta

11、变量类型聚合操作符

　1)＄let

12、文字类型聚合操作符

　1)＄literal

13、数据类型聚合操作符

　1)＄type