Shulou(Shulou.com)06/01 Report--

Aggregation Framework in Mongodb

1. Simple operation of pipe operator in Mongodb

The first curly braces in the data query statement db.collection.find ({}, {}) in Mongo are used to control the filter condition.

The second braces control the display of the key. If you want to repeat commands, averages, grouping and other operations on the fields in the queried result set

This find cannot be realized at the time.

To further process the queried result set, mongo provides a way to implement it, the aggregation Framework (aggreagate).

Let's further discuss how to use this aggreagate.

Before using aggregate to implement aggregation operations, let's first take a look at a few common aggregation operators.

Project:: can re-command the keys in the result set, control whether the keys are displayed, and calculate the columns.

$match: filter the result set

$group: grouping, aggregation, summation, average, etc.

$skip: skip the first few lines when displaying the results

$sort: sorts the result set to be displayed

$limit: controls the size of the result set

(1). $project:: can re-command the keys in the result set and control whether the keys are displayed or not. If in operation

Example 1:

> db.test.find ({}, {_ id:0}) .limit (2)

{"id": 1, "iname": "ToneyDeng", "iage": 21, "iadd": "ShengZheng", "mark": ""}

{"id": 2, "iname": "Jack", "iage": 24, "iadd": "ShangHai", "mark": ""}

> db.test.aggregate ({$project: {did: "$id", dname: "$iname", dage: "$iage", daddr: "$iadd", _ id:0}}, {$limit:2})

{

"result": [

{

"did": 1

"dname": "ToneyDeng"

"dage": 21

"daddr": "ShengZheng"

}

{

"did": 2

"dname": "Jack"

"dage": 24

"daddr": "ShangHai"

}

]

"ok": 1

}

In the above example, _ id is hidden, and then the names of all other key are reset.

It is important to note that after a re-command on key, if there is an index under the key, the query will not be able to use the index.

So try to repeat the order after the query.

(2) $match: the function of this function is actually similar to that of find ({}), mainly filtering the result set.

Example 2:

> db.test.find ({iage: {$lt:25}}, {}) .limit (2)

{"_ id": ObjectId ("54c1fe77570ca4579ca978e5"), "id": 1, "iname": "ToneyDeng", "iage": 21, "iadd": "ShengZheng", "mark": ""}

{"_ id": ObjectId ("54c1febb570ca4579ca978e6"), "id": 2, "iname": "Jack", "iage": 24, "iadd": "ShangHai", "mark": ""}

> db.test.aggregate ({$match: {iage: {$lt:25}, {$limit:2})

{

"result": [

{

"_ id": ObjectId ("54c1fe77570ca4579ca978e5")

"id": 1

"iname": "ToneyDeng"

"iage": 21

"iadd": "ShengZheng"

"mark":

}

{

"_ id": ObjectId ("54c1febb570ca4579ca978e6")

"id": 2

"iname": "Jack"

"iage": 24

"iadd": "ShangHai"

"mark":

}

]

"ok": 1

}

>

The result sets of the above two queries are the same, but they are presented in different ways.

It is best to execute match before aggregating group to reduce the amount of data and improve work efficiency. Also, if there is an index on the filtered key

Then the query will take the index.

(3)。 $group: grouping, aggregation, summation, average, etc.

Example 3:

> db.test.aggregate ({$group: {_ id: "$iname", ct: {$sum:1}, avg: {$avg: "$iage"}, {$limit:3})

{

"result": [

{

"_ id": "Tom"

"ct": 3

"avg": 25.6666666666668

}

{

"_ id": "Owen"

"ct": 2

"avg": 26

}

{

"_ id": "Smith"

"ct": 1

"avg": 27

}

]

"ok": 1

}

The above is a simple $group operation, and a few fields need to be explained here.

_ id: grouping field, the value of this field must be "$key", and _ id is fixed and cannot be changed at will. This column represents the fields that need to be grouped, either single-field grouping or multi-field grouping (shown later).

Ct: indicates total number of records

Avg: average.

(4). $skip: skip the first few lines when displaying the results

Example 4:

> db.test.find ({}) .sort ({id:1})

{"_ id": ObjectId ("54c1fe77570ca4579ca978e5"), "id": 1, "iname": "ToneyDeng", "iage": 21, "iadd": "ShengZheng", "mark": ""}

{"_ id": ObjectId ("54c1febb570ca4579ca978e6"), "id": 2, "iname": "Jack", "iage": 24, "iadd": "ShangHai", "mark": ""}

{"_ id": ObjectId ("54c1ff22570ca4579ca978e7"), "id": 3, "iname": "Smith", "iage": 27, "iadd": "ShangHai", "mark": ""}

{"_ id": ObjectId ("54c1ff36570ca4579ca978e8"), "id": 4, "iname": "Owen", "iage": 27, "iadd": "BeiJing", "mark": ""}

{"_ id": ObjectId ("54c1ff44570ca4579ca978e9"), "id": 5, "iname": "Owen", "iage": 25, "iadd": "BeiJing", "mark": ""}

{"_ id": ObjectId ("54c1ffc2570ca4579ca978ea"), "id": 6, "iname": "Tom", "iage": 25, "iadd": "WuHan", "mark": ""}

{"_ id": ObjectId ("54c1ffd3570ca4579ca978eb"), "id": 7, "iname": "Tom", "iage": 25, "iadd": "BeiJing", "mark": ""}

{"_ id": ObjectId ("54c2005e570ca4579ca978ec"), "id": 8, "iname": "Tom", "iage": 27, "iadd": "GuangZhou", "mark": "nihao"}

{"_ id": ObjectId ("54c20076570ca4579ca978ed"), "id": 9, "iname": "Jack", "iage": 23, "iadd": "GuangZhou", "mark": "Yahoo"}

> db.test.aggregate ({$sort: {id:1}}, {$skip:6})

{

"result": [

{

"_ id": ObjectId ("54c1ffd3570ca4579ca978eb")

"id": 7

"iname": "Tom"

"iage": 25

"iadd": "BeiJing"

"mark":

}

{

"_ id": ObjectId ("54c2005e570ca4579ca978ec")

"id": 8

"iname": "Tom"

"iage": 27

"iadd": "GuangZhou"

"mark": "nihao"

}

{

"_ id": ObjectId ("54c20076570ca4579ca978ed")

"id": 9

"iname": "Jack"

"iage": 23

"iadd": "GuangZhou"

"mark": "Yahoo"

}

]

"ok": 1

}

Through the comparison of the above two result sets, we can see that result two successfully skipped the first six rows.

(5) $sort: sort the result set to be displayed, and $limit: control the size of the result set

The use of these two pipe operators is relatively simple, which is used in examples 4 and 3.

Complex operation of pipe operators in 2.Mongodb

Example 5: rename the field after grouping.

> db.test.aggregate ({$group: {_ id: "$iname", count: {$sum:1}, avg: {$avg: "$iage"}, {$project: {id: "$_ id", ct: "$count", ag: "$avg", _ id:0}})

{

"result": [

{

"id": "Tom"

"ct": 3

"ag": 25.6666666666668

}

{

"id": "Owen"

"ct": 2

"ag": 26

}

{

"id": "Smith"

"ct": 1

"ag": 27

}

{

"id": "Jack"

"ct": 2

"ag": 23.5

}

{

"id": "ToneyDeng"

"ct": 1

"ag": 21

}

]

"ok": 1

}

Example 6: rename the field and then group it.

> db.test.aggregate ({$project: {_ id:0,aid: "$id", aname: "$iname", age: "$iage"}}, {$group: {_ id: "$aname", ct: {$sum:1}, ag: {$avg: "$age"}})

{

"result": [

{

"_ id": "Tom"

"ct": 3

"ag": 25.6666666666668

}

{

"_ id": "Owen"

"ct": 2

"ag": 26

}

{

"_ id": "Smith"

"ct": 1

"ag": 27

}

{

"_ id": "Jack"

"ct": 2

"ag": 23.5

}

{

"_ id": "ToneyDeng"

"ct": 1

"ag": 21

}

]

"ok": 1

}

Example 7: filter and then group.

> db.test.aggregate ({$match: {id: {$lt:6}}, {$group: {_ id: "$iname", ct: {$sum:1}, ag: {$avg: "$iage"})

{

"result": [

{

"_ id": "Owen"

"ct": 2

"ag": 26

}

{

"_ id": "Smith"

"ct": 1

"ag": 27

}

{

"_ id": "Jack"

"ct": 1

"ag": 24

}

{

"_ id": "ToneyDeng"

"ct": 1

"ag": 21

}

]

"ok": 1

}

Example 8: packet re-filtering

> db.test.aggregate ({$group: {_ id: "$iname", ct: {$sum:1}, ag: {$avg: "$iage"}, {$match: {ct: {$lte:2})

{

"result": [

{

"_ id": "Owen"

"ct": 2

"ag": 26

}

{

"_ id": "Smith"

"ct": 1

"ag": 27

}

{

"_ id": "Jack"

"ct": 2

"ag": 23.5

}

{

"_ id": "ToneyDeng"

"ct": 1

"ag": 21

}

]

"ok": 1

}

Summary: there is no strict order for the use of these aggregation operators, as long as they are used together according to certain rules, general aggregation can be achieved.

Question 1: how to count the total number of result sets in the aggregation framework.

Question 2: how to view the execution plan in the aggregation framework

Common expressions for pipe expressions in 3.Mongodb.

Pipe expression

Project: partial

# Mathematical expressions

$add: one or more expressions are added

$subtract: receives two expressions and returns the difference between the two expressions

Multiply: receives one or more expressions and returns the result set by which they are multiplied

$divide: receives two expressions and returns the quotient of the first divided by the second expression

Mod: receives two expressions and returns the remainder of the first expression divided by the second expression (model)

# date expression

$year: year

$month: month

$week: the week ordinal of the year

$dayOfMonth: the day of the month

$dayOfWeek: day ordinal of a week

$dayOfYear: the day of the year

$hour: get the hour part

$minute: get minutes

$second: number of seconds to get

# string expression

$substr: string intercepting function, equivalent to substr in mysql

$concat: string concatenation function

$toLower: lowercase string values

$toUpper: change string values to uppercase

# logical expression

$cpm: receives two expressions [exp1,exp2] and returns 1 if exp1exp2

$cond: [bool,exp1,exp2]. Return exp1 if bool is true, otherwise return exp2

$ifnull: [exp1,exp2]. If exp1 is null, exp2 is returned

$strcasecmp: compare the size of two strings

$eq/$ne/$gt/$gte/$lt/$lte: take this as (equal to / not equal to / greater than / greater than or equal to / less than / less than or equal to)

$and: and

$or: or

$not: no

$in: equivalent to in in mysql

$ne: not equal to.

$exists: the column exists.

# Mathematical expressions

(1) $add: one or more expressions are added

> db.test.aggregate ({$project: {iage: {$add: ["$iage", 1]}, _ id:0}}, {$limit:3})

{

"result": [

{

"iage": 22

}

{

"iage": 25

}

{

"iage": 28

}

]

"ok": 1

}

>

(2). $subtract: receives two expressions and returns the difference between two expressions

> db.test.aggregate ({$project: {iage: {$add: ["$iage", 1]}, subtr: {$subtract: ["$iage", 20]}, _ id:0}}, {$limit:3})

{

"result": [

{

"iage": 22

"subtr": 1

}

{

"iage": 25

"subtr": 4

}

{

"iage": 28

"subtr": 7

}

]

"ok": 1

}

(3) .$ multiply: receives one or more expressions and returns the result set by which they are multiplied

> db.test.aggregate ({$project: {multi: {$multiply: ["$iage", 2]}, _ id:0}}, {$limit:3})

{

"result": [

{

"multi": 42

}

{

"multi": 48

}

{

"multi": 54

}

]

"ok": 1

}

(4). $divide: receives two expressions and returns the quotient of the first divided by the second expression

> db.test.aggregate ({$project: {divide: {$divide: ["$iage", 11]}, _ id:0}}, {$limit:3})

{

"result": [

{

"divide": 1.9090909090909092

}

{

"divide": 2.181818181818181817

}

{

"divide": 2.454545454545454546

}

]

"ok": 1

}

(5) .$ mod: receives two expressions and returns the remainder of the first expression divided by the second expression (model)

> db.test.aggregate ({$project: {iage:1,mod: {$mod: ["$iage", 20]}, _ id:0}}, {$limit:3})

{

"result": [

{

"iage": 21

"mod": 1

}

{

"iage": 24

"mod": 4

}

{

"iage": 27

"mod": 7

}

]

"ok": 1

}

This expression is special and can be used in find, and match, but there seems to be something wrong with it.

> db.test.find ({id: {$mod: [21 mod 4]}}) .limit (3)

{"_ id": ObjectId ("54c1ff36570ca4579ca978e8"), "id": 4, "iname": "Owen", "iage": 27, "iadd": "BeiJing", "mark": ""}

> db.test.find ({id: {$mod: [7pr 4]}}) .limit (3)

{"_ id": ObjectId ("54c1ff36570ca4579ca978e8"), "id": 4, "iname": "Owen", "iage": 27, "iadd": "BeiJing", "mark": ""}

In the above two queries, under normal circumstances, the id should be 1, not 4.

(6). # date expression part.

$year: year

$month: month

$week: the week ordinal of the year

$dayOfMonth: the day of the month

$dayOfWeek: day ordinal of a week

$dayOfYear: the day of the year

$hour: get the hour part

$minute: get minutes

$second: number of seconds to get

The usage is very simple, so take $year as an example.

> db.test.aggregate ({$project: {_ id:0,iname:1,year: {$year: [new Date ()]}, {$limit:2})

{

"result": [

{

"iname": "ToneyDeng"

"year": 2015

}

{

"iname": "Jack"

"year": 2015

}

]

"ok": 1

}

(7). # string expression

$substr: string intercepting function, equivalent to substr in mysql

> db.test.aggregate ({$project: {_ id:0,id:1,iname:1,substr: {$substr: ["$iname", 0pr 4]}, {$limit:2})

{

"result": [

{

"id": 1

"iname": "ToneyDeng"

"substr": "Tone"

}

{

"id": 2

"iname": "Jack"

"substr": "Jack"

}

]

"ok": 1

}

(8). $concat: string concatenation function

> db.test.aggregate ({$project: {_ id:0,id:1,iname:1,iadd:1,concat: {$concat: ["$iname", "$iadd"]}, {$limit:2})

{

"result": [

{

"id": 1

"iname": "ToneyDeng"

"iadd": "ShengZheng"

"concat": "ToneyDeng ShengZheng"

}

{

"id": 2

"iname": "Jack"

"iadd": "ShangHai"

"concat": "Jack ShangHai"

}

]

"ok": 1

}

(9) .$ toLower: lowercase string values, $toUpper: uppercase string values

$toLower is used in the same way as $toUpper

> db.test.aggregate ({$project: {_ id:0,iname:1,toLower: {$toLower: ["$iname"]}, {$limit:2})

{

"result": [

{

"iname": "ToneyDeng"

"toLower": "toneydeng"

}

{

"iname": "Jack"

"toLower": "jack"

}

]

"ok": 1

}

(10). # logical expression

$cpm: receives two expressions [exp1,exp2] and returns if exp1exp2

> db.test.aggregate ({$project: {_ id:0,iname:1,iadd:1,cmp: {$cmp: ["$iname", "$iadd"]}, {$limit:2})

{

"result": [

{

"iname": "ToneyDeng"

"iadd": "ShengZheng"

"cmp": 1

}

{

"iname": "Jack"

"iadd": "ShangHai"

"cmp":-1

}

]

"ok": 1

}

(11). $strcasecmp: compare the size of two strings (I don't know exactly what's going on! But there is nothing wrong with the grammar. )

> db.test.aggregate ({$project: {_ id:0,iname:1,iadd:1,strcasecmp: {$strcasecmp: ["w", "w"]}, {$limit:2})

{

"result": [

{

"iname": "ToneyDeng"

"iadd": "ShengZheng"

"strcasecmp": 0

}

{

"iname": "Jack"

"iadd": "ShangHai"

"strcasecmp": 0

}

]

"ok": 1

}

(12) .$ cond: [bool,exp1,exp2]. Return exp1 if bool is true, exp2 otherwise

> db.test.aggregate ({$project: {_ id:0,iname:1, con: {$cond: [false, "$iage", "$iadd"]}}, {$limit:2})

{

"result": [

{

"iname": "ToneyDeng"

"con": "ShengZheng"

}

{

"iname": "Jack"

"con": "ShangHai"

}

]

"ok": 1

}

(14) evaluate expressions that can be used to filter the number of documents in find and match

$eq/$ne/$gt/$gte/$lt/$lte: take this as (equal to / not equal to / greater than / greater than or equal to / less than / less than or equal to)

$and: and

$or: or

$not: no

$in: equivalent to in in mysql

$exists: whether a key exists.

Equivalent to select * from tableName where iname "ToneyDeng" and iage db.test.aggregate ({$match: {$and: [{iname: {$ne: "ToneyDeng"}}, {iage: {$lt:25}]}})

{

"result": [

{

"_ id": ObjectId ("54c1febb570ca4579ca978e6")

"id": 2

"iname": "Jack"

"iage": 24

"iadd": "ShangHai"

"mark":

}

{

"_ id": ObjectId ("54c20076570ca4579ca978ed")

"id": 9

"iname": "Jack"

"iage": 23

"iadd": "GuangZhou"

"mark": "Yahoo"

}

]

"ok": 1

}

Equivalent to: select * from tableName where id not in or iname "ToneyDeng" and iage db.test.aggregate ({$match: {$or: [{id: {$not: {$in: [1 and: {iname: {$ne: "ToneyDeng"}, {iage: {$lt:25}]})

{

"result": [

{

"_ id": ObjectId ("54c1febb570ca4579ca978e6")

"id": 2

"iname": "Jack"

"iage": 24

"iadd": "ShangHai"

"mark":

}

{

"_ id": ObjectId ("54c1ff36570ca4579ca978e8")

"id": 4

"iname": "Owen"

"iage": 27

"iadd": "BeiJing"

"mark":

}

{

"_ id": ObjectId ("54c20076570ca4579ca978ed")

"id": 9

"iname": "Jack"

"iage": 23

"iadd": "GuangZhou"

"mark": "Yahoo"

}

]

"ok": 1

}

Whether the key iname exists

> db.test.aggregate ({$match: {iname: {$exists:true}, {$limit:2})

{

"result": [

{

"_ id": ObjectId ("54c1fe77570ca4579ca978e5")

"id": 1

"iname": "ToneyDeng"

"iage": 21

"iadd": "ShengZheng"

"mark":

}

{

"_ id": ObjectId ("54c1febb570ca4579ca978e6")

"id": 2

"iname": "Jack"

"iage": 24

"iadd": "ShangHai"

"mark":

}

]

"ok": 1

}

(15) .group: aggregation operator

# arithmetic operator

$sum: summation

$avg: average

$max: maximum

$min: minimum

$first: the first value

$last: last value

> db.test.aggregate ({$group: {_ id: "$iname", count: {$sum:1}, sum: {$sum: "$iage"}, avg: {$avg: "$iage"}, max: {$max: "$iage"}, min: {$min: "$iage"}, {$limit:2})

{

"result": [

{

"_ id": "Tom"

"count": 3

"sum": 77

"avg": 25.6666666666668

"max": 27

"min": 25

}

{

"_ id": "Owen"

"count": 2

"sum": 52

"avg": 26

"max": 27

"min": 25

}

]

"ok": 1

}

Get the first and last values in the group

> db.test.aggregate ({$group: {_ id: "$iname", ct: {$sum:1}, first: {$first: "$iage"}, min: {$min: "$iage"}, last: {$last: "$iage"}}, {$limit:2})

{

"result": [

{

"_ id": "Tom"

"ct": 3

"first": 25

"min": 25

"last": 27

}

{

"_ id": "Owen"

"ct": 2

"first": 27

"min": 25

"last": 25

}

]

"ok": 1

}

4.# Array operator

AddTOSet:expx, if the current array does not contain expx, add it to the array.

$push:expx, regardless of the value of expx, expx is added to the array.

5.mongo aggregation operations can also be implemented in the following ways

But it seems to be writing a js script, which is a little complicated.

MapReduce

Db.runCommand (): execute database commands

Reference link:

Http://www.cnblogs.com/shanyou/p/3494854.html

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.