Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what is the meaning of MongoDB aggregation". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

The background of MongoDB is in the environment of big data, and the so-called big data is actually a collection of information. Then there must be statistical operations for information, and such statistical operations are called aggregations (grouping statistics is an aggregation operation).

1. Get the amount of data in the collection

For the amount of data in the collection, you can do it by using the count () function directly in MongoDB.

Example: statistics of the amount of data in the emp table

> db.emp.count ()

eight

Example: fuzzy query

> db.emp.count ({"name": / Sun / I})

one

2. Deduplicate data

When learning SQL, you can use "DISTINCE" for duplicate data, so this operation is still supported in MongoDB.

Example: query all name information

There is no direct function support for this operation, you can only use the Command () instruction.

> db.runCommand ({"distinct": "emp", "key": "name"})

{

"values": [

Zhao Yi

"Qian er"

"Sun San"

"Li Si"

"Friday."

Wu Liu

Zheng Qi

"bastards"

]

"ok": 1

}

At this point, the filtering of duplicate values of name data is implemented.

3. Group operation

Data can be grouped using the "group" operation, where collections are grouped according to the specified key in MongoDB. And each group produces a processed document result.

Example: query all people over the age of 30 and group them by age

> db.runCommand ({"group": {

... "ns": "emp"

... "key": {"age": true}

... "initial": {"count": 0}

... "condition": {"age": {"$gte": 30}}

... "$reduce": function (doc,prev) {prev.count++;}

.}})

{

"retval": [

{

"age": 30

"count": 4

}

{

"age": 40

"count": 1

}

{

"age": 50

"count": 1

}

{

"age": 35

"count": 2

}

]

"count": NumberLong (8)

Keys: NumberLong (4)

"ok": 1

}

The above operation code belongs to a kind of MapReduce, which is rarely written in this way. This is only according to the traditional database design ideas, to achieve a so-called grouping operation. But the final result of this grouping operation is limited.

4 、 MapReduce

MapReduce is the essence of big data (don't use it in practice). The so-called MapReduce is actually divided into two steps to process data:

● Map: take the data out separately

● Reduce: responsible for the final processing of data

However, if you want to implement MapReduce processing in MongoDB, the complexity is quite high.

Example: create a set of personnel data

Db.emps.insert ({"name": "Zhao Yi", "age": 30, "sex": "female", "job": "CLERK", "sal": 1000})

Db.emps.insert ({"name": "Qian er", "age": 22, "sex": "male", "job": "ACCOUNT", "sal": 2000})

Db.emps.insert ({"name": "Sun San", "age": 28, "sex": "female", "job": "CLERK", "sal": 3000})

Db.emps.insert ({"name": "Li Si", "age": 35, "sex": "female", "job": "IT", "sal": 4000})

Db.emps.insert ({"name": "Friday", "age": 31, "sex": "male", "job": "SEC", "sal": 5000})

Db.emps.insert ({"name": "Wu Liu", "age": 40, "sex": "female", "job": "MANAGER", "sal": 6000})

Db.emps.insert ({"name": "Zheng Qi", "age": 44, "sex": "male", "job": "CLERK", "sal": 1500})

Db.emps.insert ({"name": "son of a bitch", "age": 55, "sex": "male", "job": "SAL", "sal": 5500})

Using the MapReduce operation will eventually save the processing result in a separate collection, and the final processing effect is as follows:

Example: group by position and get the name of each position

Step 1: write the definition of the grouping:

Var jobMapFun=function () {

Emit (this.job,this.name)

}

Step 2: write the Reduce operation:

> var jobReduceFun=function (key,values) {

... Return {"job": key, "name": values}

...}

Step 3: you can actually perform a final processing on the data that has been processed by MapReduce.

Var jobFinalizeFun=function (key,values) {

If (key== "MANAGER") {

Return {"job": key, "name": values, "info": "boss of the company"};}

Return {"job": key, "name": values}

}

Integration of operations:

Db.runCommand ({

"mapreduce": "emps"

"map": jobMapFun

"reduce": jobReduceFun

"out": "t_emps_job"

"finalize": jobFinalizeFun})

{

"result": "t_emps_job"

"timeMillis": 28

"counts": {

"input": 8

"emit": 8

"reduce": 1

"output": 6

}

"ok": 1

}

Now, after execution, all the processing results are saved in the "t_emps_job" collection.

> db.t_emps_job.find ()

{"_ id": "ACCOUNT", "value": {"job": "ACCOUNT", "name": "Qian er"}}

{"_ id": "CLERK", "value": {"job": "CLERK", "name": {"job": "CLERK", "name": ["Zhao Yi", "Sun San", "Zheng Qi"]}

{"_ id": "IT", "value": {"job": "IT", "name": "Li Si"}}

{"_ id": "MANAGER", "value": {"job": "MANAGER", "name": "Wu Liu", "info": "company boss"}}

{"_ id": "SAL", "value": {"job": "SAL", "name": "bastards"}}

{"_ id": "SEC", "value": {"job": "SEC", "name": "Friday"}}

Example: figure out the number of people of each gender, average wage, minimum wage, employee name

Var sexMapFun=function () {

Emit (this.sex, {"ccount": 1, "csal": this.sal, "cmax": this.sal, "cmin": this.sal, "cname": this.name}); / / define the conditions for grouping and the content to be fetched from each collection

}

Var sexReduceFun=function (key,values) {

Var total=0; / / Statistics

Var sum=0; / / calculate the total salary

Var max=values [0] .cmax; / / assume that the first data is the maximum wage

Var min=values [0] .cmin; / / assume that the first data is the minimum wage

Var names=new Array (); / / define array contents

For (var x in values) {/ / cycle to retrieve the data in it

Total + = values [x] .ccount; / / increase in the number of people

Sum + = values [x] .csal; / / cycle takes out all salaries and accumulates

If (maxvalues [x] .cmin) {

Min= values [x] .cmin; / / find the minimum wage

}

Names [x] = values [x] .cname; / / Save name

}

Var avg= (sum/total) .tofixed (2)

Return {"count": total, "avg": avg, "max": max, "min": min, "name": names}

}; / / return the result of data processing

Db.runCommand ({

"mapreduce": "emps"

"map": sexMapFun

"reduce": sexReduceFun

"out": "t_emps_sex"

})

{

"result": "t_emps_sex"

"timeMillis": 31

"counts": {

"input": 8

"emit": 8

"reduce": 2

"output": 2

}

"ok": 1

}

> db.t_emps_sex.find () .pretty ()

{

"_ id": "female"

"value": {

"count": 4

"avg": "3500.00"

"max": 6000

"min": 1000

"name": [

Zhao Yi

"Sun San"

"Li Si"

Wu Liu

]

}

}

{

"_ id": "male"

"value": {

"count": 4

"avg": "3500.00"

"max": 5500

"min": 1500

"name": [

"Qian er"

"Friday."

Zheng Qi

"bastards"

]

}

}

Although big data era provides the strongest MapReduce support, but in terms of real development, it is really impossible to use it.

5. Aggregation framework

MapReduce is powerful, but it is too complex. Most of the time, you need MapReduce, but you don't want to make the code too complex. So after the MongoDB 2.x release, the aggregation framework was introduced and the aggregation function: aggregate () was provided.

5.1USD group

Group is mainly used for grouping data operations.

Example: implement the function of aggregate query-find out the number of people in each position

> db.emps.aggregate ([{"$group": {"_ id": "$job", job_count: {"$sum": 1}])

{"_ id": "MANAGER", "job_count": 1}

{"_ id": "ACCOUNT", "job_count": 1}

{"_ id": "IT", "job_count": 1}

{"_ id": "SAL", "job_count": 1}

{"_ id": "CLERK", "job_count": 3}

{"_ id": "SEC", "job_count": 1}

This operation is more in line with the traditional operation of the group by clause.

Example: find the total salary for each position

> db.emps.aggregate ([{"$group": {"_ id": "$job", job_sal: {"$sum": "$sal"}])

{"_ id": "MANAGER", "job_sal": 6000}

{"_ id": "ACCOUNT", "job_sal": 2000}

{"_ id": "IT", "job_sal": 4000}

{"_ id": "SAL", "job_sal": 5500}

{"_ id": "CLERK", "job_sal": 5500}

{"_ id": "SEC", "job_sal": 5000}

In the entire aggregation framework, if you want to reference each row of data, use: "$field name".

Example: calculate the average salary for each position

> db.emps.aggregate ([{"$group": {"_ id": "$job", "job_sal": {"$sum": "$sal"}, "job_avg": {"$avg": "$sal"}}])

{"_ id": "MANAGER", "job_sal": 6000, "job_avg": 6000}

{"_ id": "ACCOUNT", "job_sal": 2000, "job_avg": 2000}

{"_ id": "IT", "job_sal": 4000, "job_avg": 4000}

{"_ id": "SAL", "job_sal": 5500, "job_avg": 5500}

{"_ id": "CLERK", "job_sal": 5500, "job_avg": 1833.33333333333}

{"_ id": "SEC", "job_sal": 5000, "job_avg": 5000}

Example: find the maximum and minimum wages

> db.emps.aggregate ([{"$group": {"_ id": "$job", "max_sal": {"$max": "$sal"}, "min_avg": {"$min": "$sal"}}])

{"_ id": "MANAGER", "max_sal": 6000, "min_avg": 6000}

{"_ id": "ACCOUNT", "max_sal": 2000, "min_avg": 2000}

{"_ id": "IT", "max_sal": 4000, "min_avg": 4000}

{"_ id": "SAL", "max_sal": 5500, "min_avg": 5500}

{"_ id": "CLERK", "max_sal": 3000, "min_avg": 1000}

{"_ id": "SEC", "max_sal": 5000, "min_avg": 5000}

The above several operations similar to SQL have been successfully implemented.

Example: calculate the salary data for each position (array display)

> db.emps.aggregate ([{"$group": {"_ id": "$job", "sal_data": {"$push": "$sal"}])

{"_ id": "MANAGER", "sal_data": [6000]}

{"_ id": "ACCOUNT", "sal_data": [2000]}

{"_ id": "IT", "sal_data": [4000]}

{"_ id": "SAL", "sal_data": [5500]}

{"_ id": "CLERK", "sal_data": [1000, 3000, 1500]}

{"_ id": "SEC", "sal_data": [5000]}

Example: find out the personnel for each position

> db.emps.aggregate ([{"$group": {"_ id": "$job", "sal_data": {"$push": "$name"}])

{"_ id": "MANAGER", "sal_data": ["Wu Liu"]}

{"_ id": "ACCOUNT", "sal_data": ["Qian er"]}

{"_ id": "IT", "sal_data": ["Li Si"]}

{"_ id": "SAL", "sal_data": ["bastards"]}

{"_ id": "CLERK", "sal_data": ["Zhao Yi", "Sun San", "Zheng Qi"]}

{"_ id": "SEC", "sal_data": ["Friday"]}

It is true that using "$push" can change the data into an array to save, but there is a problem, duplicate content will also be saved, so there is an unduplicated setting in MongoDB.

> db.emps.insert ({"name": "Wu Liu", "age": 40, "sex": "female", "job": "MANAGER", "sal": 6000})

WriteResult ({"nInserted": 1})

> db.emps.insert ({"name": "Zheng Qi", "age": 44, "sex": "male", "job": "CLERK", "sal": 1500})

WriteResult ({"nInserted": 1})

> db.emps.insert ({"name": "son of a bitch", "age": 55, "sex": "male", "job": "SAL", "sal": 5500})

WriteResult ({"nInserted": 1})

> db.emps.aggregate ([{"$group": {"_ id": "$job", "sal_data": {"$push": "$name"}])

{"_ id": "MANAGER", "sal_data": ["Wu Liu", "Wu Liu"]}

{"_ id": "ACCOUNT", "sal_data": ["Qian er"]}

{"_ id": "IT", "sal_data": ["Li Si"]}

{"_ id": "SAL", "sal_data": ["bastards", "bastards"]}

{"_ id": "CLERK", "sal_data": ["Zhao Yi", "Sun San", "Zheng Qi", "Zheng Qi"]}

{"_ id": "SEC", "sal_data": ["Friday"]}

Example: canceling duplicate data

> db.emps.aggregate ([{"$group": {"_ id": "$job", "sal_data": {"$addToSet": "$name"}])

{"_ id": "MANAGER", "sal_data": ["Wu Liu"]}

{"_ id": "ACCOUNT", "sal_data": ["Qian er"]}

{"_ id": "IT", "sal_data": ["Li Si"]}

{"_ id": "SAL", "sal_data": ["bastards"]}

{"_ id": "CLERK", "sal_data": ["Zheng Qi", "Sun San", "Zhao Yi"]}

{"_ id": "SEC", "sal_data": ["Friday"]}

By default, all the data is saved, but now you only want to keep the first or last one.

Example: save the first content

> db.emps.aggregate ([{"$group": {"_ id": "$job", "sal_data": {"$first": "$name"}])

{"_ id": "MANAGER", "sal_data": "Wu Liu"}

{"_ id": "ACCOUNT", "sal_data": "Qian er"}

{"_ id": "IT", "sal_data": "Li Si"}

{"_ id": "SAL", "sal_data": "bastards"}

{"_ id": "CLERK", "sal_data": "Zhao Yi"}

{"_ id": "SEC", "sal_data": "Friday"}

Example: save the last content

> db.emps.aggregate ([{"$group": {"_ id": "$job", "sal_data": {"$last": "$name"}])

{"_ id": "MANAGER", "sal_data": "Wu Liu"}

{"_ id": "ACCOUNT", "sal_data": "Qian er"}

{"_ id": "IT", "sal_data": "Li Si"}

{"_ id": "SAL", "sal_data": "bastards"}

{"_ id": "CLERK", "sal_data": "Zheng Qi"}

{"_ id": "SEC", "sal_data": "Friday"}

Although it is convenient to implement grouping processing, it should be noted that all grouped data is unordered and completed in memory, so it is impossible to support a large amount of data.

$5.2$ project

You can use $project to control the display rules of data columns, as follows:

● normal column ({member: 1 | true}): indicates the content to be displayed

● "_ id" column ({"_ id": 0 | false}): indicates whether the "_ id" column is displayed

● conditional filter column ({member: expression}): the data that satisfies the expression can be displayed

Example: show only name and job columns, not _ id column

> db.emps.aggregate ([{"$project": {"_ id": 0, "name": 1}}])

{"name": "Zhao Yi"}

{"name": "Qian er"}

{"name": "Sun San"}

{"name": "Li Si"}

{"name": "Friday"}

{"name": "Wu Liu"}

{"name": "Zheng Qi"}

{"name": "son of a bitch"}

{"name": "Wu Liu"}

{"name": "Zheng Qi"}

{"name": "son of a bitch"}

At this point, only the columns that are set can be displayed, while the other columns cannot be displayed. In fact, this belongs to the database projection mechanism.

In fact, four operations are supported in the process of data projection: addition ("$add"), subtraction ("$subtract"), multiplication ("$mulitipy"), division ("$devided), and modulus (" $mod ").

Example: four operations

> db.emps.aggregate ([{"$project": {"_ id": 0, "name": 1, "position": "$job", "sal": 1}}])

{"name": "Zhao Yi", "sal": 1000, "position": "CLERK"}

{"name": "Qian er", "sal": 2000, "position": "ACCOUNT"}

{"name": "Sun San", "sal": 3000, "position": "CLERK"}

{"name": "Li Si", "sal": 4000, "position": "IT"}

{"name": "Friday", "sal": 5000, "position": "SEC"}

{"name": "Wu Liu", "sal": 6000, "position": "MANAGER"}

{"name": "Zheng Qi", "sal": 1500, "position": "CLERK"}

{"name": "son of a bitch", "sal": 5500, "position": "SAL"}

{"name": "Wu Liu", "sal": 6000, "position": "MANAGER"}

{"name": "Zheng Qi", "sal": 1500, "position": "CLERK"}

{"name": "son of a bitch", "sal": 5500, "position": "SAL"}

> db.emps.aggregate ([{"$project": {"_ id": 0, "name": 1, "job": 1, "annual salary": {"$multiply": ["$sal", 12]}])

{"name": "Zhao Yi", "job": "CLERK", "Annual salary": 12000}

{"name": "Qian er", "job": "ACCOUNT", "Annual salary": 24000}

{"name": "Sun San", "job": "CLERK", "Annual salary": 36000}

{"name": "Li Si", "job": "IT", "Annual salary": 48000}

{"name": "Friday", "job": "SEC", "Annual salary": 60000}

{"name": "Wu Liu", "job": "MANAGER", "Annual salary": 72000}

{"name": "Zheng Qi", "job": "CLERK", "Annual salary": 18000}

{"name": "son of a bitch", "job": "SAL", "Annual salary": 66000}

{"name": "Wu Liu", "job": "MANAGER", "Annual salary": 72000}

{"name": "Zheng Qi", "job": "CLERK", "Annual salary": 18000}

{"name": "son of a bitch", "job": "SAL", "Annual salary": 66000}

In addition to the four operations, the following operators are also supported:

● relational operations: size comparison ("$cmp"), equal to ("$eq"), greater than ("$gt"), greater than or equal to ("$gte"), less than ("$lt"), less than or equal to ("$lte"), not equal to ("$ne"), judge NULL ("$ifNull"), these operations return Boolean data.

● logic operation: with ("$and"), or ("$or"), not ("$not")

● string operations: concatenation ("$concat"), intercept ("$substr"), turn lowercase ("$toLower"), resize ("$toUpper"), case-insensitive comparison ("$strcasecmp")

Example: find out the names, positions and salaries of all people whose salary is greater than or equal to 2000

> db.emps.aggregate ([{"$project": {"_ id": 0, "name": 1, "job": 1, "salary": "$sal", "whether it is greater than 2000": {"$gte": ["$sal", 2000]}])

{"name": "Zhao Yi", "job": "CLERK", "salary": 1000, "whether it is greater than 2000": false}

{"name": "Qian er", "job": "ACCOUNT", "salary": 2000, "whether it is greater than 2000": true}

{"name": "Sun San", "job": "CLERK", "salary": 3000, "whether it is greater than 2000": true}

{"name": "Li Si", "job": "IT", "salary": 4000, "whether it is greater than 2000": true}

{"name": "Friday", "job": "SEC", "salary": 5000, "whether it is greater than 2000": true}

{"name": "Wu Liu", "job": "MANAGER", "salary": 6000, "whether it is greater than 2000": true}

{"name": "Zheng Qi", "job": "CLERK", "salary": 1500, "whether it is greater than 2000": false}

{"name": "son of a bitch", "job": "SAL", "salary": 5500, "whether it is greater than 2000": true}

{"name": "Wu Liu", "job": "MANAGER", "salary": 6000, "whether it is greater than 2000": true}

{"name": "Zheng Qi", "job": "CLERK", "salary": 1500, "whether it is greater than 2000": false}

{"name": "son of a bitch", "job": "SAL", "salary": 5500, "whether it is greater than 2000": true}

Example: query information that the position is manager

> db.emps.aggregate ([{"$project": {"_ id": 0, "name": 1, "job": 1, "position": "$job", "job": {"$eq": ["$job", "MANAGER"]}]))

{"name": "Zhao Yi", "job": false, "position": "CLERK"}

{"name": "Qian er", "job": false, "position": "ACCOUNT"}

{"name": "Sun San", "job": false, "position": "CLERK"}

{"name": "Li Si", "job": false, "position": "IT"}

{"name": "Friday", "job": false, "position": "SEC"}

{"name": "Wu Liu", "job": true, "position": "MANAGER"}

{"name": "Zheng Qi", "job": false, "position": "CLERK"}

{"name": "son of a bitch", "job": false, "position": "SAL"}

{"name": "Wu Liu", "job": true, "position": "MANAGER"}

{"name": "Zheng Qi", "job": false, "position": "CLERK"}

{"name": "son of a bitch", "job": false, "position": "SAL"}

> db.emps.aggregate ([{"$project": {"_ id": 0, "name": 1, "job": 1, "position": "$job", "job": {"$eq": ["$job", {"$toUpper": "manager"}]))

{"name": "Zhao Yi", "job": false, "position": "CLERK"}

{"name": "Qian er", "job": false, "position": "ACCOUNT"}

{"name": "Sun San", "job": false, "position": "CLERK"}

{"name": "Li Si", "job": false, "position": "IT"}

{"name": "Friday", "job": false, "position": "SEC"}

{"name": "Wu Liu", "job": true, "position": "MANAGER"}

{"name": "Zheng Qi", "job": false, "position": "CLERK"}

{"name": "son of a bitch", "job": false, "position": "SAL"}

{"name": "Wu Liu", "job": true, "position": "MANAGER"}

{"name": "Zheng Qi", "job": false, "position": "CLERK"}

{"name": "son of a bitch", "job": false, "position": "SAL"}

> db.emps.aggregate ([{"$project": {"_ id": 0, "name": 1, "job": 1, "position": "$job", "job": {"$strcasecmp": ["$job", "manager"]}]))

{"name": "Zhao Yi", "job":-1, "position": "CLERK"}

{"name": "Qian er", "job":-1, "position": "ACCOUNT"}

{"name": "Sun San", "job":-1, "position": "CLERK"}

{"name": "Li Si", "job":-1, "position": "IT"}

{"name": "Friday", "job": 1, "position": "SEC"}

{"name": "Wu Liu", "job": 0, "position": "MANAGER"}

{"name": "Zheng Qi", "job":-1, "position": "CLERK"}

{"name": "son of a bitch", "job": 1, "position": "SAL"}

{"name": "Wu Liu", "job": 0, "position": "MANAGER"}

{"name": "Zheng Qi", "job":-1, "position": "CLERK"}

{"name": "son of a bitch", "job": 1, "position": "SAL"}

Example: using string interception

> db.emps.aggregate ([{"$project": {"_ id": 0, "name": 1, "job": 1, "position": "$job", "job": {"first three digits": {"$substr": ["$job", 0re3]}])

{"name": "Zhao Yi", "job": {"Top three": "CLE"}, "position": "CLERK"}

{"name": "Qian er", "job": {"Top three": "ACC"}, "position": "ACCOUNT"}

{"name": "Sun San", "job": {"Top three": "CLE"}, "position": "CLERK"}

{"name": "Li Si", "job": {"Top three": "IT"}, "position": "IT"}

{"name": "Friday", "job": {"Top three": "SEC"}, "position": "SEC"}

{"name": "Wu Liu", "job": {"Top three": "MAN"}, "position": "MANAGER"}

{"name": "Zheng Qi", "job": {"Top three": "CLE"}, "position": "CLERK"}

{"name": "bastards", "job": {"top three": "SAL"}, "position": "SAL"}

{"name": "Wu Liu", "job": {"Top three": "MAN"}, "position": "MANAGER"}

{"name": "Zheng Qi", "job": {"Top three": "CLE"}, "position": "CLERK"}

{"name": "bastards", "job": {"top three": "SAL"}, "position": "SAL"}

The projection operation implemented with "$project" is so powerful that almost all possible operations can be used.

5. 3. $sort

Sorting can be achieved using "$sort", setting 1 for ascending order and-1 for descending order.

Example: implementing sorting

> db.emps.aggregate ([{"$project": {"_ id": 0, "age": 1, "sal": 1}}, {"$sort": {"age":-1, "sal": 1}}])

{"age": 55, "sal": 5500}

{"age": 55, "sal": 5500}

{"age": 44, "sal": 1500}

{"age": 44, "sal": 1500}

{"age": 40, "sal": 6000}

{"age": 40, "sal": 6000}

{"age": 35, "sal": 4000}

{"age": 31, "sal": 5000}

{"age": 30, "sal": 1000}

{"age": 28, "sal": 3000}

{"age": 22, "sal": 2000}

> db.emps.aggregate ([{"$match": {"$gte": 3000, "$lte": 5000}}, {"$project": {"_ id": 0, "age": 1, "sal": 1}, {"$sort": {"age":-1, "sal": 1}])

{"age": 35, "sal": 4000}

{"age": 31, "sal": 5000}

{"age": 28, "sal": 3000}

> db.emps.aggregate ([

. {"$match": {"sal": {"$gte": 3000, "$lte": 6000}

. {"$project": {"_ id": 0, "age": 1, "sal": 1}}

. {"$group": {"_ id": "$age", "count": {"$sum": 1}

... {"$sort": {"count":-1}}])

{"_ id": 55, "count": 2}

{"_ id": 40, "count": 2}

{"_ id": 28, "count": 1}

{"_ id": 35, "count": 1}

{"_ id": 31, "count": 1}

> db.emps.aggregate ([

. {"$match": {"sal": {"$gte": 3000, "$lte": 6000}

. {"$project": {"_ id": 0, "name": 1, "sal": 1, "job": 1}}

. {"$group": {"_ id": "$job", "count": {"$sum": 1}, "avg": {"$avg": "$sal"}

... {"$sort": {"count":-1}}])

{"_ id": "SAL", "count": 2, "avg": 5500}

{"_ id": "MANAGER", "count": 2, "avg": 6000}

{"_ id": "IT", "count": 1, "avg": 4000}

{"_ id": "CLERK", "count": 1, "avg": 3000}

{"_ id": "SEC", "count": 1, "avg": 5000}

5.4.Paging processing: $limit, $skip

"$limit": the number of data fetched

"$skip": the number of data spans

Example: use the "$limit" setting to take out the number

> db.emps.aggregate ([

. {"$project": {"_ id": 0, "name": 1, "sal": 1}}

... {"$limit": 2}

.])

{"name": "Zhao Yi", "sal": 1000}

{"name": "Qian er", "sal": 2000}

Example: across 3 rows of data

> db.emps.aggregate ([

. {"$project": {"_ id": 0, "name": 1, "sal": 1}}

... {"$skip": 3}

... {"$limit": 2}

.])

{"name": "Li Si", "sal": 4000}

{"name": "Friday", "sal": 5000}

> db.emps.aggregate ([

. {"$match": {"sal": {"$gte": 3000, "$lte": 6000}

. {"$project": {"_ id": 0, "name": 1, "sal": 1, "job": 1}}

. {"$group": {"_ id": "$job", "count": {"$sum": 1}, "avg": {"$avg": "$sal"}

... {"$sort": {"count":-1}}

... {"$skip": 3}

... {"$limit": 2}

.])

{"_ id": "IT", "count": 1, "avg": 4000}

{"_ id": "CLERK", "count": 1, "avg": 3000}

$5.50 unwind

Array information is often returned when querying data, but arrays are not easy to browse, so "$unwind" is provided to turn array data into opposite string contents.

Example: add some information

Db.depts.insert ({"title": "Technical Department", "busi": ["Research and Development", "production", "training"]})

Db.depts.insert ({"title": "Technical Department", "busi": ["salary", "tax"]})

Example: transforming information

> db.depts.aggregate ([

. {"$project": {"_ id": 0, "title": 1, "busi": 1}}

... {"$unwind": "$busi"}

.])

{"title": "Technology Department", "busi": "Research and Development"}

{"title": "Technical Department", "busi": "production"}

{"title": "Technical Department", "busi": "training"}

{"title": "Technical Department", "busi": "salary"}

{"title": "Technical Department", "busi": "tax"}

This is equivalent to turning the data of the array into a single row of data.

$5.6$ geoNear

Use "$geoNear" to get the coordinate points of the attachment.

Example: prepare test data

Db.shop.drop ()

Db.shop.insert ({loc: [10jue 10]})

Db.shop.insert ({loc: [20jue 10]})

Db.shop.insert ({loc: [10pm 20]})

Db.shop.insert ({loc: [200.20]})

Db.shop.insert ({loc: [100100]})

Db.shop.insert ({loc: [80pr 30]})

Db.shop.insert ({loc: [30pm 50]})

Db.shop.createIndex ({"loc": "2d"})

Example: set up query

> db.shop.aggregate ([

. {"$geoNear": {"near": [30jue 30], "distanceField": "loc", "maxDistance": 20, "num": 2}}

.])

{"_ id": ObjectId ("5994ff7c0184ff511bf02bdc"), "loc": 14.142135623730951}

{"_ id": ObjectId ("5994ff7d0184ff511bf02bdf"), "loc": 20}

The retrieval of geographic information must be supported by index.

5.7USD out

"$out" can output the query results to a specified collection.

Example: output the result of the projection to the collection

> db.emps.aggregate ([

. {"$project": {"_ id": 0, "name": 1, "sal": 1}}

... {"$out": "emps_infos"}

.])

> db.emps_infos.find ()

{"_ id": ObjectId ("599501eeca6455d4a46874e0"), "name": "Zhao Yi", "sal": 1000}

{"_ id": ObjectId ("599501eeca6455d4a46874e1"), "name": "Qian er", "sal": 2000}

{"_ id": ObjectId ("599501eeca6455d4a46874e2"), "name": "Sun San", "sal": 3000}

{"_ id": ObjectId ("599501eeca6455d4a46874e3"), "name": "Li Si", "sal": 4000}

{"_ id": ObjectId ("599501eeca6455d4a46874e4"), "name": "Friday", "sal": 5000}

{"_ id": ObjectId ("599501eeca6455d4a46874e5"), "name": "Wu Liu", "sal": 6000}

{"_ id": ObjectId ("599501eeca6455d4a46874e6"), "name": "Zheng Qi", "sal": 1500}

{"_ id": ObjectId ("599501eeca6455d4a46874e7"), "name": "bastards", "sal": 5500}

{"_ id": ObjectId ("599501eeca6455d4a46874e8"), "name": "Wu Liu", "sal": 6000}

{"_ id": ObjectId ("599501eeca6455d4a46874e9"), "name": "Zheng Qi", "sal": 1500}

{"_ id": ObjectId ("599501eeca6455d4a46874ea"), "name": "bastards", "sal": 5500}

It is equivalent to realizing the copy operation of the data table.

This is the end of the content of "what does the aggregation of MongoDB mean?" Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.