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

Mongo 聚合框架-Aggregate(二)

总点击46
简介：三表达式操作符 1、布尔类型聚合操作符 1)＄and 作用：mongo中的与操作符，如果表达式全部为true则结果为true，否则为false

三 表达式操作符

1、布尔类型聚合操作符

1)＄and


作用：mongo中的与操作符，如果表达式全部为true则结果为true，否则为false


语法：{ ＄and: [ ＜expression1>,＜expression2>,… ] }


示例：{ ＄and: [ 1,“green” ] } = true


2)＄or


作用：mongo中的或操作符，如果表达式全部为false则结果为false，否则为false


语法：{ ＄or: [ ＜expression1>,… ] }


示例：{ ＄or: [ [ false ],false ] } = true


3)＄not


作用：mongo中的非操作符，返回与表达式相反的boolean值


语法：{ ＄not: [ ] }


示例：{ ＄not: [ true ] } = false

2、比较类型聚合操作符

1)＄cmp (equal)


作用：比较两个值，如果第一个值小于第二个值，返回-1，如果大于第二个值，返回1，如果相等，返回0。＄cmp操作符会先按type比较，然后按value来比较。


语法：{ ＄cmp: [ ＜expression1>,＜expression2> ] }


2)＄eq (=)


作用：比较两个值的大小，如果相等则返回true，否则返回false


语法：{ ＄eq: [ ＜expression1>,＜expression2> ] }


3)＄gt (>)


作用：比较两个值的大小，如果第一个值大于第二个值则返回true，否则返回false


语法：{ ＄gt: [ ＜expression1>,＜expression2> ] }


4)＄gte (>=)


作用：比较两个值的大小，如果第一个值大于等于第二个值则返回true，否则返回false


语法：{ ＄gte: [ ＜expression1>,＜expression2> ] }


5)＄lt (<)


作用：比较两个值的大小，如果第一个值小于第二个值则返回true，否则返回false


语法：{ ＄lt: [ ＜expression1>,＜expression2> ] }


6)＄lte (=＜)


作用：比较两个值的大小，如果第一个值小于等于第二个值则返回true，否则返回false


语法：{ ＄lte: [ ＜expression1>,＜expression2> ] }


7)＄ne (!=)


作用：比较两个值的大小，如果不相等则返回true，否则返回false


语法：{ $ne: [ ＜expression1>,＜expression2> ] }


8)示例

{ "_id" : 1,"item" : "abc1",description: "product 1",qty: 300 }

{ "_id" : 2,"item" : "abc2",description: "product 2",qty: 200 }

{ "_id" : 3,"item" : "xyz1",description: "product 3",qty: 250 }

{ "_id" : 4,"item" : "VWZ1",description: "product 4",qty: 300 }

{ "_id" : 5,"item" : "VWZ2",description: "product 5",qty: 180 }

比较qty的值与250的大小

db.inventory.aggregate(

[

{

$project:

{

item: 1,

qty: 1,

cmpTo250: { $cmp: [ "$qty",250 ] },

qtyEq250: { $eq: [ "$qty",

qtyGt250: { $gt: [ "$qty",

qtyGte250: { $gte: [ "$qty",

qtyLt250: { $lt: [ "$qty",

qtyLte250: { $lte: [ "$qty",

qtyNe250: { $ne: [ "$qty",

_id: 0

}

}

]

)

结果如下

{ "item" : "abc1","qty" : 300,"cmpTo250" : 1,"qtyEq250" : false,"qtyGt250" : true,"qtyGte250" : true,"qtyLt250" : false,"qtyLte250" : false,"qtyNe250" : true }

{ "item" : "abc2","qty" : 200,"cmpTo250" :-1,"qtyGt250" : false,"qtyGte250" : false,"qtyLt250" : true,"qtyLte250" : true,"qtyNe250" : true }

{ "item" : "xyz1","qty" : 250,"cmpTo250" : 0,"qtyEq250" : true,"qtyNe250" : false }

{ "item" : "VWZ1","qtyNe250" : true }

{ "item" : "VWZ2","qty" : 180,"qtyNe250" : true }

3、分组类型聚合操作符

1)＄sum


作用


＄sum操作是对数进行求和操作，它会忽略非数字的值。


如果在一个即有数字值也有非数字值的字段上使用，＄sum只会返回数字值的和而将非数字的值忽略。


如果在一个所有文档中都不存在的字段上使用，返回0。


如果在一个所有值都不是数字类型的字段上使用，返回0


＄sum只在管道操作符＄group和＄project下有效


语法


{ ＄sum: ＜expression> }


＄project阶段还可能会有


{ ＄sum: [ ＜expression1>,＜expression2> … ] }


2)＄avg


作用


＄avg操作是对数进行求平均数操作，它会忽略非数字的值。＄avg只在管道操作符＄group和＄project下有效


语法


{ ＄avg: ＜expression> }


$project阶段还可能会有


{ ＄avg: [ ＜expression1>,＜expression2> … ] }

3)＄max：


作用


＄max是求最大值的一个操作，它会同时比较值的类型和大小，只在管道操作符＄group和＄project下有效。


语法


{ ＄max: ＜expression> }


$project阶段还可能会有


{ ＄max: [ ＜expression1>,＜expression2> … ] }

4)＄min：只在管道操作符＄group和＄project下有效


作用


＄max是求最小值的一个操作，它会同时比较值的类型和大小，只在管道操作符＄group和＄project下有效。


语法


{ ＄min: ＜expression> }


＄project阶段还可能会有


{ ＄min: [ ＜expression1>,＜expression2> … ] }


示例1($group stage)

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

{ "_id" : ObjectId("599e7c8c99657c350e726a64"),"item" : "jkl","price" : 20,"quantity" : 1,"date" : ISODate("2014-02-03T09:00:00Z") }

{ "_id" : ObjectId("599e7c8c99657c350e726a65"),"item" : "xyz","price" : 5,"quantity" : 5,"date" : ISODate("2014-02-03T09:05:00Z") }

{ "_id" : ObjectId("599e7c8c99657c350e726a66"),"quantity" : 10,"date" : ISODate("2014-02-15T08:00:00Z") }

{ "_id" : ObjectId("599e7c8e99657c350e726a67"),"date" : ISODate("2014-02-15T09:05:00Z") }

根据item分组，求分组的数量、总的金额、平均数量、最大的总金额、最小的数量值

db.sales.aggregate(

[

{

$group:

{

_id: "$item",

count: { $sum: 1 },

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

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

maxTotalAmount: { $max: { $multiply: [ "$price",

minQuantity: { $min: "$quantity" }

}

}

]

)

结果如下

{ "_id" : "xyz","count" : 2,"totalAmount" : 75,"avgQuantity" : 7.5,"maxTotalAmount" : 50,"minQuantity" : 5 }

{ "_id" : "jkl","count" : 1,"totalAmount" : 20,"avgQuantity" : 1,"maxTotalAmount" : 20,"minQuantity" : 1 }

{ "_id" : "abc","totalAmount" : 120,"avgQuantity" : 6,"maxTotalAmount" : 100,"minQuantity" : 2 }

示例2(＄project stage)

{ "_id" : 1,"quizzes" : [ 10,6,7 ],"final" : 80,"midterm" : 75 }

{ "_id" : 2,"quizzes" : [ 9,10 ],"final" : 95,"midterm" : 80 }

{ "_id" : 3,"quizzes" : [ 4,5,5 ],"final" : 78,"midterm" : 70 }

求quizzes数组的和、平均值、最大值、最小值，求final和midterm的和、平均值、最大值、最小值。

db.students.aggregate([

{

$project: {

"_id":0,

quizTotal: { $sum: "$quizzes"},

examTotal: { $sum: [ "$final","$midterm" ] },

quizAvg: { $avg: "$quizzes"},

examAvg: { $avg: [ "$final",

quizMax: { $max: "$quizzes"},

examMax: { $max: [ "$final",

quizMin: { $min: "$quizzes"},

examMin: { $min: [ "$final","$midterm" ] }

}

}

])

结果如下

{ "quizTotal" : 23,"examTotal" : 155,"quizAvg" : 7.666666666666667,"examAvg" : 77.5,"quizMax" : 10,"examMax" : 80,"quizMin" : 6,"examMin" : 75 }

{ "quizTotal" : 19,"examTotal" : 175,"quizAvg" : 9.5,"examAvg" : 87.5,"examMax" : 95,"quizMin" : 9,"examMin" : 80 }

{ "quizTotal" : 14,"examTotal" : 148,"quizAvg" : 4.666666666666667,"examAvg" : 74,"quizMax" : 5,"examMax" : 78,"quizMin" : 4,"examMin" : 70 }

5)＄first|＄last


作用：获取分组中第一个|最后一个文档产生的值，只在管道操作符＄group下有效。当＄group阶段使用＄first|＄last操作符的时候，＄group必须在＄sort管道之后从而得到有序的文档。


语法：{ ＄first: ＜expression> }|{ ＄last: ＜expression> }


示例

{ "item" : "abc","date" : ISODate("2014-01-01T08:00:00Z") }

{ "item" : "jkl","date" : ISODate("2014-02-03T09:00:00Z") }

{ "item" : "xyz","date" : ISODate("2014-02-03T09:05:00Z") }

{ "item" : "abc","date" : ISODate("2014-02-15T08:00:00Z") }

{ "item" : "xyz","date" : ISODate("2014-02-15T09:05:00Z") }

{ "item" : "xyz","date" : ISODate("2014-02-15T12:05:10Z") }

{ "item" : "xyz","date" : ISODate("2014-02-15T14:12:12Z") }

根据item和date字段进行排序，然后根据item进行分组，获取分组中的第一条和最后一条文档记录的date值

db.sales.aggregate(

[

{ $sort: { item: 1,date: 1 } },

{

$group:

{

_id: "$item",

firstSalesDate: { $first: "$date" },

lastSalesDate: { $last: "$date" }

}

}

]

)

结果如下

{ "_id" : "xyz","firstSalesDate" : ISODate("2014-02-03T09:05:00Z"),"lastSalesDate" : ISODate("2014-02-15T14:12:12Z") }

{ "_id" : "jkl","firstSalesDate" : ISODate("2014-02-03T09:00:00Z"),"lastSalesDate" : ISODate("2014-02-03T09:00:00Z") }

{ "_id" : "abc","firstSalesDate" : ISODate("2014-01-01T08:00:00Z"),"lastSalesDate" : ISODate("2014-02-15T08:00:00Z") }

6)＄push：


作用：＄push用来将表达式中的值按分组加到对应的数组中，只在管道操作符＄group下有效


语法：{ ＄push: ＜expression> }


7)＄addToSet：


作用：＄addToSet用来将表达式中的值按分组加到对应的数组中,数组中的值不可重复，只在管道操作符＄group下有效


语法：{ ＄addToSet: ＜expression> }


示例3

{ "item" : "abc","date" : ISODate("2014-02-15T14:12:12Z") }

根据年和天分组，获取改天所有的item集合以及所有的item名称集合

db.sales.aggregate(

[

{

$group:

{

_id: { day: { $dayOfYear: "$date"},year: { $year: "$date" } },

itemsSold: { $push: { item: "$item",quantity: "$quantity" } },

soldItems: { $addToSet: "$item" }

}

}

]

)

结果如下

{ "_id" : { "day" : 46,"year" : 2014 },

"itemsSold" : [

{ "item" : "abc","quantity" : 10 },

{ "item" : "xyz","quantity" : 5 },"quantity" : 10 }

],

"soldItems" : [ "xyz","abc" ] }

{ "_id" : { "day" : 34,

"itemsSold" : [

{ "item" : "jkl","quantity" : 1 },"quantity" : 5 }

],"jkl" ] }

{ "_id" : { "day" : 1,"quantity" : 2 }

],

"soldItems" : [ "abc" ] }

8)＄stdDevPop：


9)＄stdDevSamp：

4、算术类型聚合操作符

1)＄add


作用：将一组数进行相加操作，也可以对日期格式的字段增加毫秒数而改变时间


语法：{ ＄add: [ ＜expression1>,… ] }


示例

{ "_id" : 1,"fee" : 2 }

{ "_id" : 2,"fee" : 1 }

{ "_id" : 3,"fee" : 0 }

db.sales.aggregate(

[

{ $project: { item: 1,total: { $add: [ "$price","$fee" ] } } }

]

)

结果如下

{ "_id" : 1,"total" : 12 }

{ "_id" : 2,"total" : 21 }

{ "_id" : 3,"total" : 5 }

2)＄subtract


作用：将两个数进行相减


语法：{ ＄subtract: [ ＜expression1>,＜expression2> ] }


示例

{ "_id" : 1,"fee" : 2,"discount" : 5 }

{ "_id" : 2,"fee" : 1,"discount" : 2 }

db.sales.aggregate( [ { $project: {

item: 1,

total: {

$subtract: [ { $add: [ "$price","$fee" ] },"$discount" ]

} } } ] )

结果如下

{ "_id" : 1,"total" : 7 }

{ "_id" : 2,"total" : 19 }

3)＄multiply


作用：对一组数进行相乘操作


语法：{ ＄multiply: [ ＜expression1>,"quantity": 2 }

{ "_id" : 2,"quantity": 1 }

{ "_id" : 3,"quantity": 10 }

db.sales.aggregate(

[

{ $project: { date: 1,item: 1,total: { $multiply: [ "$price","$quantity" ] } } }

]

)

结果如下

{ "_id" : 1,"total" : 20 }

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

{ "_id" : 3,"total" : 50 }

4)$divide


作用：将两个数进行相除


语法：{ ＄divide: [ ＜expression1>,＜expression2> ] }


示例：

{ "_id" : 1,"name" : "A","hours" : 80,"resources" : 7 },

{ "_id" : 2,"name" : "B","hours" : 40,"resources" : 4 }

db.planning.aggregate(

[

{ $project: { name: 1,workdays: { $divide: [ "$hours",8 ] } } }

]

)

结果如下

{ "_id" : 1,"workdays" : 10 }

{ "_id" : 2,"workdays" : 5 }

5)＄mod


作用：对两个数进行求余操作


语法：{ ＄mod: [ ＜expression1>,"project" : "A","tasks" : 7 }

{ "_id" : 2,"project" : "B","tasks" : 4 }

db.planning.aggregate(

[

{ $project: { remainder: { $mod: [ "$hours","$tasks" ] } } }

]

)

结果如下

{ "_id" : 1,"remainder" : 3 }

{ "_id" : 2,"remainder" : 0 }

6)＄abs


作用：对数值做绝对值操作


语法：{ ＄abs: ＜number> }


示例

{ _id: 1,start: 5,end: 8 }

{ _id: 2,start: 4,end: 4 }

{ _id: 3,start: 9,end: 7 }

{ _id: 4,start: 6,end: 7 }

db.ratings.aggregate([

{

$project: { delta: { $abs: { $subtract: [ "$start","$end" ] } } }

}

])

结果如下

{ "_id" : 1,"delta" : 3 }

{ "_id" : 2,"delta" : 0 }

{ "_id" : 3,"delta" : 2 }

{ "_id" : 4,"delta" : 1 }

7)＄ceil|＄floor


作用：对小数进行取整运算


语法：{ ＄ceil: ＜number> }|{ ＄floor: ＜number> }


示例

{ _id: 1,value: 9.25 }

{ _id: 2,value: 8.73 }

{ _id: 3,value: 4.32 }

{ _id: 4,value: -5.34 }

db.samples.aggregate([

{ $project: { value: 1,ceilingValue: { $ceil: "$value" },floorValue: { $floor: "$value" } } }

])

结果如下

{ "_id" : 1,"value" : 9.25,"ceilingValue" : 10,"floorValue" : 9 }

{ "_id" : 2,"value" : 8.73,"ceilingValue" : 9,"floorValue" : 8 }

{ "_id" : 3,"value" : 4.32,"ceilingValue" : 5,"floorValue" : 4 }

{ "_id" : 4,"value" : -5.34,"ceilingValue" : -5,"floorValue" : -6 }

8)＄exp


9)＄pow


10)＄sqrt


11)＄ln


12)＄log


13)＄log10


14)＄trunc

5、数组类型聚合操作符

1)＄in


作用：查询某个特定的值是否存在指定的数组中


语法：{ ＄in: [ ＜expression>,＜array expression> ] }


示例

{ "_id" : 1,"location" : "24th Street","in_stock" : [ "apples","oranges","bananas" ] }

{ "_id" : 2,"location" : "36th Street","in_stock" : [ "bananas","pears","grapes" ] }

{ "_id" : 3,"location" : "82nd Street","in_stock" : [ "cantaloupes","watermelons","apples" ] }

db.fruit.aggregate([

{

$project: {

"store location" : "$location",

"has bananas" : {

$in: [ "bananas","$in_stock" ]

}

}

}

])

结果如下

{ "_id" : 1,"store location" : "24th Street","has bananas" : true }

{ "_id" : 2,"store location" : "36th Street","has bananas" : true }

{ "_id" : 3,"store location" : "82nd Street","has bananas" : false }

2)＄size


作用：用来获取数组的大小


语法：{ ＄size: ＜expression> }


示例

{ "_id" : 1,"item" : "ABC1","description" : "product 1",colors: [ "blue","black","red" ] }

{ "_id" : 2,"item" : "ABC2","description" : "product 2",colors: [ "purple" ] }

{ "_id" : 3,"item" : "XYZ1","description" : "product 3",colors: [ ] }

db.inventory.aggregate(

[

{

$project: {

item: 1,

numberOfColors: { $size: "$colors" }

}

}

]

)

结果如下

{ "_id" : 1,"numberOfColors" : 3 }

{ "_id" : 2,"numberOfColors" : 1 }

{ "_id" : 3,"numberOfColors" : 0 }

3)＄slice


作用：返回一个数组的子集


语法：{ ＄slice: [ ＜array>,＜position>,＜n> ] }


position：如果如正数，则返回从数组开始，到数组position下标截止的子数组；如果是负数，则返回数组下标为position绝对值开始到数组结尾的子数组。数组下标1开始。


n：正数，限制返回子数组的大小


示例

{ "_id" : 1,"name" : "dave123",favorites: [ "chocolate","cake","butter","apples" ] }

{ "_id" : 2,"name" : "li",favorites: [ "apples","pudding","pie" ] }

{ "_id" : 3,"name" : "ahn",favorites: [ "pears","pecans","chocolate","cherries" ] }

{ "_id" : 4,"name" : "ty",favorites: [ "ice cream" ] }

db.users.aggregate([

{ $project: { name: 1,threeFavorites: { $slice: [ "$favorites",3 ] } } }

])

结果如下

{ "_id" : 1,"threeFavorites" : [ "chocolate","butter" ] }

{ "_id" : 2,"threeFavorites" : [ "apples","threeFavorites" : [ "pears","chocolate" ] }

{ "_id" : 4,"threeFavorites" : [ "ice cream" ] }

4)＄isArray


作用：判断一个操作数是否是数组类型


语法：{ ＄isArray: [ ＜expression> ] }


示例

{ "_id" : 1,instock: [ "chocolate" ],ordered: [ "butter",instock: [ "apples",instock: [ "pears","pecans"],ordered: [ "cherries" ] }

{ "_id" : 4,instock: [ "ice cream" ],ordered: [ ] }

db.warehouses.aggregate([

{ $project:

{ items:

{ $cond:

{

if: { $and: [ { $isArray: "$instock" },{ $isArray: "$ordered" } ] },

then: { $concatArrays: [ "$instock","$ordered" ] },

else: "One or more fields is not an array."

}

}

}

}

])

结果如下

{ "_id" : 1,"items" : [ "chocolate","apples" ] }

5)＄reverseArray


作用：将数组中的元素倒置


语法：{ ＄reverseArray: ＜array expression> }


示例

{ "_id" : 1,"favorites" : [ "chocolate","favorites" : [ "apples","favorites" : [ ] }

{ "_id" : 4,"name" : "ty" }

db.users.aggregate([

{

$project:

{

name: 1,

reverseFavorites: { $reverseArray: "$favorites" }

}

}

])

结果如下

{ "_id" : 1,"reverseFavorites" : [ "apples","chocolate" ] }

{ "_id" : 2,"reverseFavorites" : [ "pie","apples" ] }

{ "_id" : 3,"reverseFavorites" : [ ] }

{ "_id" : 4,"reverseFavorites" : null }

6)＄arrayElemAt


作用：根据下标返回数组中指定元素


语法：{ ＄arrayElemAt: [ ＜array>,＜idx> ] }


示例

{ "_id" : 1,favorites: [ "ice cream" ] }

db.users.aggregate([

{

$project:

{

name: 1,

first: { $arrayElemAt: [ "$favorites",0 ] },

last: { $arrayElemAt: [ "$favorites",-1 ] }

}

}

])

结果如下

{ "_id" : 1,"first" : "chocolate","last" : "apples" }

{ "_id" : 2,"first" : "apples","last" : "pie" }

{ "_id" : 3,"first" : "pears","last" : "cherries" }

{ "_id" : 4,"first" : "ice cream","last" : "ice cream" }

7)＄arrayToObject


作用：将一组数组或者文档转为单个文档，数组必须大小为2，文档必须包含”k”和”v”字段


[ [ “item”,“abc123”],[ “qty”,25 ] ]或[ { “k”: “item”,“v”: “abc123”},{ “k”: “qty”,“v”: 25 } ]


语法：{ ＄arrayToObject: ＜expression> }


示例

{ "_id" : 1,dimensions: [ { "k": "l","v": 25},{ "k": "w","v": 10 },{ "k": "uom","v": "cm" } ] }

{ "_id" : 2,dimensions: [ [ "l",50],[ "w",25 ],[ "uom","cm" ] ] }

db.inventory.aggregate(

[

{

$project: {

item: 1,

dimensions: { $arrayToObject: "$dimensions" }

}

}

]

)

结果如下

{ "_id" : 1,"dimensions" : { "l" : 25,"w" : 10,"uom" : "cm" } }

{ "_id" : 2,"dimensions" : { "l" : 50,"w" : 25,"uom" : "cm" } }

8)＄concatArrays


作用：合并数组,如果表达式中有非数组类型的值，则返回null


语法：{ ＄concatArrays: [ ＜array1>,＜array2>,ordered: [ ] }

db.warehouses.aggregate([

{ $project: { items: { $concatArrays: [ "$instock","$ordered" ] } } }

])

结果如下

{ "_id" : 1,"items" : null }

{ "_id" : 3,"items" : [ "pears","items" : [ "ice cream" ] }

9)＄range


10)＄filter


11)＄indexOfArray


12)＄map


13)＄objectToArray


14)＄reduce


15)＄zip

0人推荐

推荐文章

热门标签

最新文章

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