数据库 \ MongoDB \ Mongo 聚合框架-Aggregate(三)

Mongo 聚合框架-Aggregate(三)

总点击127
简介:三表达式操作符 6、字符串类型聚合操作符 1)$concat 作用:连接字符串,如果某个表达式字段不存在或字段值为null,则返回null

三 表达式操作符

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,"description" : "PRODUCT 1" }

{ "_id" : 2,"item" : "abc2","description" : "Product 2" }

{ "_id" : 3,"item" : "xyz1","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,"qty" : 233 }

{ "_id" : 4,"city" : "Eugene,"qty" : 842 }

{ "_id" : 5,"city" : "Reno,NV","qty" : 655 }

{ "_id" : 6,"city" : "Portland,"qty" : 408 }

{ "_id" : 7,"city" : "Sacramento,"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,"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,qty: 200 }

{ "_id" : 3,qty: 250 }

查询如果qty大于250,则discount为30,否则为20

db.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",30,20 ]

}

}

}

]

)

结果如下

{ "_id" : 1,"discount" : 30 }

{ "_id" : 2,"discount" : 20 }

{ "_id" : 3,"discount" : 30 }

2)$ifNull


作用:非空判断操作,如果为空,则用默认的值替代,如果非空,则保持原值


语法:{ $ifNull: [ <expression>,<replacement-expression-if-null> ] }


示例

{ "_id" : 1,description: "product 1",description: null,qty: 250 }

db.inventory.aggregate(

[

{

$project: {

item: 1,

description: { $ifNull: [ "$description","Unspecified" ] }

}

}

]

)

结果如下

{ "_id" : 1,"description" : "Unspecified" }

{ "_id" : 3,"description" : "Unspecified" }

3)$switch


作用:


语法:

$switch: {

branches: [

{ case: <expression>,then: <expression> },

{ case: <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" },

then: "Needs improvement."

}

],

default: "No scores found."

}

}

}

}

] )

结果如下

{ "_id" : 1,"summary" : "Doing pretty well." }

{ "_id" : 2,"summary" : "Needs improvement." }

{ "_id" : 3,"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","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

0人推荐

推荐文章

热门标签

意见反馈 常见问题 官方微信 返回顶部