The Mongo aggregation framework is used by Aggregate

The Mongo aggregation framework is used by Aggregate

data

Db. Emps. Insert ({” name “:” zhang “, “age” : 30, “sex”, “male”, “job” : “CLERK”, “salary” : 1000}) Db. Emps. Insert ({” name “:” bill “, “age” : 28, “sex”, “female”, “job” : “CLERK”, “salary” : 2000}) Db. Emps. Insert ({” name “:” detective “, “age” : 26, “sex”, “male”, “job” : “MANAGER”, “salary” : 5000}) Db. Emps. Insert ({” name “:” Daisy “, “age” : 32, “sex”, “female”, “job” : “MANAGER”, “salary” : 6000}) Db. Emps. Insert ({” name “:” sun qi “, “age” : 31, “sex”, “male”, “job” : “CLERK”, “salary” : 4000}) Db. Emps. Insert ({” name “:” tortoise “, “age” : 35, “sex”, “female”, “job” : “PRESIDENT”, “salary” : 7000})

The query

db.emps.find()
Copy the code

$group

Mainly for sub-data operation query

Realize the function of aggregation query

 db.emps.aggregate([{"$group": {"_id":"$job",j_count:{"$sum":1}}}]) // The number of people per position is equivalent to group byCopy the code

// 1 { “_id”: “MANAGER”, “j_count”: 4 }

// 2 { “_id”: “PRESIDENT”, “j_count”: 3 }

// 3 { “_id”: “CLERK”, “j_count”: 7 }

In [], the first field _id indicates what to do, followed by what to do (statistics)

Figure out the total salary for each position _

db.emps.aggregate([{"$group": {"_id":"$job",job_salary:{"$sum":"$salary"}}}])
Copy the code

// 1 { “_id”: “MANAGER”, “job_salary”: 22000 }

// 2 { “_id”: “PRESIDENT”, “job_salary”: 21000 }

// 3 { “_id”: “CLERK”, “job_salary”: 18000 }

$salary

References to each row of data use the $field name

Figure out the average salary for each position

 db.emps.aggregate([{"$group": {"_id":"$job" ,"p_count": {"$sum": 1}."job_total_salary": {"$sum":"$salary"},"avg_salary": {"$avg":"$salary"}}}])
Copy the code

// 1 { “_id”: “MANAGER”, “p_count”: 4, “job_total_salary”: 22000, “avg_salary”: 5500 }

// 2 { “_id”: “PRESIDENT”, “p_count”: 3, “job_total_salary”: 21000, “avg_salary”: 7000 }

// 3 {“_id”: “CLERK”, “p_count”: 7, “job_total_salary”: 18000, “avg_salary”: 2571.42857142857}

Figure out the highest and lowest salary for each position

db.emps.aggregate([{"$group": {"_id":"$job"."max": {"$max":"$salary"},"min": {"$min":"$salary"}}}])
Copy the code

// 1 { “_id”: “MANAGER”, “max”: 6000, “min”: 5000 }

// 2 { “_id”: “PRESIDENT”, “max”: 7000, “min”: 7000 }

// 3 { “_id”: “CLERK”, “max”: 4000, “min”: 1000 }

Figure out the salary for each position

db.emps.aggregate([{"$group": {"_id":"$job"."salary": {"$push":"$salary"}}}])
Copy the code

// 1 { “_id”: “MANAGER”, “salary”: [ 5000, 6000, 5000, 6000 ] }

// 2 { “_id”: “PRESIDENT”, “salary”: [ 7000, 7000 ] }

// 3 { “_id”: “CLERK”, “salary”: [ 1000, 2000, 4000, 1000, 2000, 4000 ] }

$push

You can save data as an array, but there is a problem with duplicate data

db.emps.aggregate([{"$group": {"_id":"$job"."salary": {"$push":"$salary"}}}])
Copy the code

Insert two more data

Db. Emps. Insert ({” name “:” sun qi “, “age” : 31, “sex”, “male”, “job” : “CLERK”, “salary” : 4000}) Db. Emps. Insert ({” name “:” tortoise “, “age” : 35, “sex”, “female”, “job” : “PRESIDENT”, “salary” : 7000})

The query

The query results

// 1 { “_id”: “MANAGER”, “salary”: [ 5000, 6000, 5000, 6000 ] }

// 2 { “_id”: “PRESIDENT”, “salary”: [ 7000, 7000, 7000 ] }

// 3 { “_id”: “CLERK”, “salary”: [ 1000, 2000, 4000, 1000, 2000, 4000, 4000 ] }

The solution is to use $addToSet

$addToSet

Mongo provides de-duplicating data, and $addToSet displays all data by default

db.emps.aggregate([{"$group": {"_id":"$job"."salary": {"$addToSet":"$salary"}}}])</pre>
Copy the code

// 1 { “_id”: “MANAGER”, “salary”: [ 6000, 5000 ] }

// 2 { “_id”: “PRESIDENT”, “salary”: [ 7000 ] }

// 3 { “_id”: “CLERK”, “salary”: [ 4000, 2000, 1000 ] }

The other way around, the first one is the last one. Take the first one:

db.emps.aggregate([{"$group": {"_id":"$job"."salary": {"$first":"$salary"}}}])</pre>
Copy the code

// 1 { “_id”: “MANAGER”, “salary”: 5000 }

// 2 { “_id”: “PRESIDENT”, “salary”: 7000 }

// 3 { “_id”: “CLERK”, “salary”: 1000 }

Take the last one

db.emps.aggregate([{"$group": {"_id":"$job"."salary": {"$last":"$salary"}}}])
Copy the code

// 1 { “_id”: “MANAGER”, “salary”: 6000 }

// 2 { “_id”: “PRESIDENT”, “salary”: 7000 }

// 3 { “_id”: “CLERK”, “salary”: 4000 }

All data is unordered and it is impossible to support large data volumes.

$project

Can be used to control the display rules of data columns

Common columns: {member: 1 or true} : indicates the * ID column to be implemented. {“* ID “:0 or false} indicates whether the _ID column is displayed

Conditional filter {member: expression} after the expression can be displayed:

The name column is displayed, and the ID column is not displayed in the JOB column

db.emps.aggregate({"$project": {"name": 1,"_id": 0}})Copy the code

// 1 { “name”: “张三” }

// 2 { “name”: “李四” }

// 3 { “name”: “王五” }

// 4 {“name”: “zhao liu”}

// 5 {“name”: “sun”}

// 6 {“name”: “b”}

// 7 {“name”: “zhang SAN”}

// 8 { “name”: “李四” }

// 9 { “name”: “王五” }

// 10 {“name”: “zhao liu”}

// 11 {“name”: “sun”}

// 12 {“name”: “b”}

// 13 {“name”: “sun”}

// 14 {“name”: “b”}

Only columns that are set in will be displayed, other columns will not be displayed.

Four operations:

$add	add
$subtract	subtraction
$divide	division
$multiply	The multiplication

Annual salary calculation:

 db.emps.aggregate([{"$project": {"name": 1,"_id": 0."salary": 1,"salary": {"Salary": {"$multiply": ["$salary"12]}}}}]),Copy the code

// 1 {“name”: “zhang “, “salary”: {“salary”: 12000}}

// 2 {“name”: “salary”: {“salary”: 24000}}

// 3 {“name”: “salary”: {“salary”: 60000}}

// 4 {“name”: “zhao “, “salary”: {“salary”: 72000}}

// 5 {“name”: “name”, “salary”: {“salary”: 48000}}

// 4 {“name”: “salary”: {“salary”: 81000}}

// 7 {“name”: “zhang “, “salary”: {“salary”: 12000}}

// 4 {“name”: “name”, “salary”: {“salary”: 24000}}

// 4 {“name”: “wang 5 “, “salary”: {“salary”: 60000}}

// 10 {“name”: “zhao “, “salary”: {“salary”: 72000}}

// 11 {“name”: “name”, “salary”: {“salary”: 48000}}

// 12 {“name”: “salary”: {“salary”: 81000}}

// 13 {“name”: “salary”: {“salary”: 48000}}

// 14 {“name”: “salary”: {“salary”: 81000}}

Relational operation:

The size of the comparison	$cmp
Is greater than	$gt
Less than	$lt
Greater than or equal to	$gte
Less than or equal to	$lte
Is not equal to	$ne
Determine the null	$ifNull
Is equal to the	$eq

The result returns a Boolean

Data employees with monthly salary over 3000

"$project" : {" _id ": false," name ": 1," job ": 1," wage ":" $salary ", "salary" : {" $gt: "[" $salary, 3000]}}}]);Copy the code

/ / 1 {” name “:” zhang “, “job” : “CLERK”, “salary” : 1000, “salary” : false}

/ / 2 {” name “:” bill “, “job” : “CLERK”, “salary” : 2000, “salary” : false}

/ / 3 {” name “:” detective “, “job” : “MANAGER”, “salary” : 5000, “salary” : true}

/ / 4 {” name “:” Daisy “, “job” : “MANAGER”, “salary” : 6000, “salary” : true}

/ / 5 {” name “:” sun qi “, “job” : “CLERK”, “salary” : 4000, “salary” : true}

/ / 6 {” name “:” tortoise “, “job” : “PRESIDENT”, “salary” : 7000, “salary” : true}

7 / / {” name “:” zhang “, “job” : “CLERK”, “salary” : 1000, “salary” : false}

/ / 8 {” name “:” bill “, “job” : “CLERK”, “salary” : 2000, “salary” : false}

9 / / {” name “:” detective “, “job” : “MANAGER”, “salary” : 5000, “salary” : true}

/ / 10 {” name “:” Daisy “, “job” : “MANAGER”, “salary” : 6000, “salary” : true}

11 / / {” name “:” sun qi “, “job” : “CLERK”, “salary” : 4000, “salary” : true}

/ / 12 {” name “:” tortoise “, “job” : “PRESIDENT”, “salary” : 7000, “salary” : true}

/ / 13 {” name “:” sun qi “, “job” : “CLERK”, “salary” : 4000, “salary” : true}

/ / 14 {” name “:” tortoise “, “job” : “PRESIDENT”, “salary” : 7000, “salary” : true}

Logical operations

with	$and
or	$or
non	$not

String handling

The connection	$concat
The interception	$substr
Turn to lowercase	$toLower
Case comparison	$strcsecmp
equal	$eq

Query the position is MANAGER information lowercase

 db.emps.aggregate([{  "$project": {"_id":false."name": 1,"job": 1,"是否": {"$eq": ["$job"."MANAGER"]},   "Wages":"$salary"."salary": {"$gt": ["$salary", 3000]}}}]);Copy the code

/ / 1 {” name “:” zhang “, “job” : “CLERK”, “whether” : false, “wage” : 1000, “salary” : false}

/ / 2 {” name “:” bill “, “job” : “CLERK”, “whether” : false, “wage” : 2000, “salary” : false}

/ / 3 {” name “:” detective “, “job” : “MANAGER”, “whether” : true, “wage” : 5000, “salary” : true}

/ / 4 {” name “:” Daisy “, “job” : “MANAGER”, “whether” : true, “wage” : 6000, “salary” : true}

/ / 5 {” name “:” sun qi “, “job” : “CLERK”, “whether” : false, “wage” : 4000, “salary” : true}

/ / 6 {” name “:” tortoise “, “job” : “PRESIDENT”, “whether” : false, “wage” : 7000, “salary” : true}

7 / / {” name “:” zhang “, “job” : “CLERK”, “whether” : false, “wage” : 1000, “salary” : false}

/ / 8 {” name “:” bill “, “job” : “CLERK”, “whether” : false, “wage” : 2000, “salary” : false}

9 / / {” name “:” detective “, “job” : “MANAGER”, “whether” : true, “wage” : 5000, “salary” : true}

/ / 10 {” name “:” Daisy “, “job” : “MANAGER”, “whether” : true, “wage” : 6000, “salary” : true}

11 / / {” name “:” sun qi “, “job” : “CLERK”, “whether” : false, “wage” : 4000, “salary” : true}

/ / 12 {” name “:” tortoise “, “job” : “PRESIDENT”, “whether” : false, “wage” : 7000, “salary” : true}

/ / 13 {” name “:” sun qi “, “job” : “CLERK”, “whether” : false, “wage” : 4000, “salary” : true}

/ / 14 {” name “:” tortoise “, “job” : “PRESIDENT”, “whether” : false, “wage” : 7000, “salary” : true}

Turn the capital

Uppercase first, then compare equals

db.emps.aggregate([{  "$project": {"_id":false."name": 1,"job": 1,"是否": {"$eq": ["$job", {"$toUpper":"manager"}},"Wages":"$salary"}}]);Copy the code

/ / 1 {” name “:” zhang “, “job” : “CLERK”, “whether” : false, “wage” : 1000}

/ / 2 {” name “:” bill “, “job” : “CLERK”, “whether” : false, “wage” : 2000}

/ / 3 {” name “:” detective “, “job” : “MANAGER”, “whether” : true, “wage” : 5000}

/ / 4 {” name “:” Daisy “, “job” : “MANAGER”, “whether” : true, “wage” : 6000}

/ / 5 {” name “:” sun qi “, “job” : “CLERK”, “whether” : false, “wage” : 4000}

/ / 6 {” name “:” tortoise “, “job” : “PRESIDENT”, “whether” : false, “wage” : 7000}

7 / / {” name “:” zhang “, “job” : “CLERK”, “whether” : false, “wage” : 1000}

/ / 8 {” name “:” bill “, “job” : “CLERK”, “whether” : false, “wage” : 2000}

9 / / {” name “:” detective “, “job” : “MANAGER”, “whether” : true, “wage” : 5000}

/ / 10 {” name “:” Daisy “, “job” : “MANAGER”, “whether” : true, “wage” : 6000}

11 / / {” name “:” sun qi “, “job” : “CLERK”, “whether” : false, “wage” : 4000}

/ / 12 {” name “:” tortoise “, “job” : “PRESIDENT”, “whether” : false, “wage” : 7000}

/ / 13 {” name “:” sun qi “, “job” : “CLERK”, “whether” : false, “wage” : 4000}

/ / 14 {” name “:” tortoise “, “job” : “PRESIDENT”, “whether” : false, “wage” : 7000}

Comparison string

Lowercase value 0 is used to indicate that the return is correct

 db.emps.aggregate([{  "$project": {"_id":false."name": 1,"job": 1,"是否": {"$strcasecmp": ["$job"."manager"]}, "Wages":"$salary"}}]);Copy the code

/ / 1 {” name “:” zhang “, “job” : “CLERK”, “whether” : NumberInt (” 1 “), “wage” : 1000}

/ / 2 {” name “:” bill “, “job” : “CLERK”, “whether” : NumberInt (” 1 “), “wage” : 2000}

/ / 3 {” name “:” detective “, “job” : “MANAGER”, “whether” : NumberInt (” 0 “), “wage” : 5000}

/ / 4 {” name “:” Daisy “, “job” : “MANAGER”, “whether” : NumberInt (” 0 “), “wage” : 6000}

/ / 5 {” name “:” sun qi “, “job” : “CLERK”, “whether” : NumberInt (” 1 “), “wage” : 4000}

/ / 6 {” name “:” tortoise “, “job” : “PRESIDENT”, “whether” : NumberInt (” 1 “), “wage” : 7000}

7 / / {” name “:” zhang “, “job” : “CLERK”, “whether” : NumberInt (” 1 “), “wage” : 1000}

/ / 8 {” name “:” bill “, “job” : “CLERK”, “whether” : NumberInt (” 1 “), “wage” : 2000}

9 / / {” name “:” detective “, “job” : “MANAGER”, “whether” : NumberInt (” 0 “), “wage” : 5000}

/ / 10 {” name “:” Daisy “, “job” : “MANAGER”, “whether” : NumberInt (” 0 “), “wage” : 6000}

11 / / {” name “:” sun qi “, “job” : “CLERK”, “whether” : NumberInt (” 1 “), “wage” : 4000}

/ / 12 {” name “:” tortoise “, “job” : “PRESIDENT”, “whether” : NumberInt (” 1 “), “wage” : 7000}

/ / 13 {” name “:” sun qi “, “job” : “CLERK”, “whether” : NumberInt (” 1 “), “wage” : 4000}

/ / 14 {” name “:” tortoise “, “job” : “PRESIDENT”, “whether” : NumberInt (” 1 “), “wage” : 7000}

Intercept string

 db.emps.aggregate([{  "$project": {"_id":false."name": 1,"job":"$job"."job1": {"Top three": {"$substr": ["$job", 0, 3]}},"job2": {"$substr": ["$job"0, 3]}}}]);Copy the code

/ / 1 {” name “:” zhang “, “job” : “CLERK”, “job1 is” : {” top three “:” CLE “}, “job2” : “CLE}”

/ / 2 {” name “:” bill “, “job” : “CLERK”, “job1 is” : {” top three “:” CLE “}, “job2” : “CLE}”

/ / 3 {” name “:” detective “, “job” : “MANAGER”, “job1 is” : {” top three “:” MAN “}, “job2” : “MAN”}

/ / 4 {” name “:” Daisy “, “job” : “MANAGER”, “job1 is” : {” top three “:” MAN “}, “job2” : “MAN”}

/ / 5 {” name “:” sun qi “, “job” : “CLERK”, “job1 is” : {” top three “:” CLE “}, “job2” : “CLE}”

/ / 6 {” name “:” tortoise “, “job” : “PRESIDENT”, “job1 is” : {” top three “:” PRE “}, “job2” : “PRE”}

7 / / {” name “:” zhang “, “job” : “CLERK”, “job1 is” : {” top three “:” CLE “}, “job2” : “CLE}”

/ / 8 {” name “:” bill “, “job” : “CLERK”, “job1 is” : {” top three “:” CLE “}, “job2” : “CLE}”

9 / / {” name “:” detective “, “job” : “MANAGER”, “job1 is” : {” top three “:” MAN “}, “job2” : “MAN”}

/ / 10 {” name “:” Daisy “, “job” : “MANAGER”, “job1 is” : {” top three “:” MAN “}, “job2” : “MAN”}

11 / / {” name “:” sun qi “, “job” : “CLERK”, “job1 is” : {” top three “:” CLE “}, “job2” : “CLE}”

/ / 12 {” name “:” tortoise “, “job” : “PRESIDENT”, “job1 is” : {” top three “:” PRE “}, “job2” : “PRE”}

/ / 13 {” name “:” sun qi “, “job” : “CLERK”, “job1 is” : {” top three “:” CLE “}, “job2” : “CLE}”

/ / 14 {” name “:” tortoise “, “job” : “PRESIDENT”, “job1 is” : {” top three “:” PRE “}, “job2” : “PRE”}