Mongodb aggregate

Time:2021-6-14

 

https://www.cnblogs.com/wt7018/p/11929359.html

Mongodb aggregate

 

1、 Foundation

1. What is aggregation?

Aggregation is an aggregation pipeline based on data processing. Each document passes through a pipeline composed of multiple stages
It can group and filter the pipelines in each stage, and then output the corresponding results after a series of processing

DB. Set name. Aggregate ({pipeline: {expression})

It’s a bit like the syntax of ORM aggregation in Django

2. Common pipeline

$group: group the documents in the collection for statistical results
$match: filter data and output only qualified documents
$project: modify the structure of the input document, such as renaming, adding, deleting fields, and creating calculation results

$sort: sort the input documents and output them
$limit: limits the number of documents returned by the aggregation pipeline
$skip: skips the specified number of documents and returns the remaining documents
$unwind(): splits fields of list (array) type

3. Common expressions

Process the input document and output it
Syntax: expression: '$column name'
Common expressions
$sum: calculate the sum$ sum:1  Indicates to count by one time
$AVG: count average
$Min: get the minimum
$Max: get the maximum
$push: inserts values into an array in the result document
$first: get the first document data according to the sorting of resource documents
$last: get the last document data according to the sorting of resource documents

2、 Common pipe usage

1、$group

Function: group the documents in the collection, which can be used for statistical results
_ ID represents the basis of grouping. The format of a field is' $field '
format
DB. Set name. Aggregate ({$group: {_ ID: '$field', custom field: {expression: '$field'})

db.stu.aggregate({$group: {_id: '$gender'}})
db.stu.aggregate({$group: {_id: '$gender', count: {$sum: 1}}})
db.stu.aggregate({$group: {_id: '$gender', avg_age: {$avg: '$age'}}})
db.stu.aggregate({$group: {_id: '$hometown', min_age: {$min: '$age'}, count: {$sum: 1}}})

be careful: 
    _id后面的值,表示按照什么分组,format'$字段'
    count, avg_ Count is a custom field
    The value of the expression is' $field '

Group by null

Divide the documents used in the collection into a group, that is, the collection is a group
#Find the total number and average age of students
db.stu.aggregate({$group: {_id: null, count:{$sum: 1}, avg_age:{$avg: '$age'}}})

supplement

#Insert data

db.test.insert({country: "china", province: "sh", userid: "a"})
db.test.insert({country: "china", province: "sh", userid: "b"})
db.test.insert({country: "china", province: "sh", userid: "a"})
db.test.insert({country: "china", province: "sh", userid: "c"})
db.test.insert({country: "china", province: "bj", userid: "da"})
db.test.insert({country: "china", province: "bj", userid: "fa"})

#1. Weight removal
It can be grouped by multiple keys at the same time. If each field in the document is grouped, it can realize the function of de duplication
db.test.aggregate({$group: {_id: {country: '$country', province: '$province'}}})
#2. Take the values in the dictionary nested in the dictionary 
_id: {contry: '$_id.country'}
example
db.test.aggregate(
{$group: {_id: {country: '$country', province: '$province', userid: '$userid'}}},  
{$group: {_id: {country: '$_id.country', province: '$_id.province'}, count: {$sum: 1}}}, 
{$project: {country: '$_id.country', province: '$_id.province', count: '$count', _id: 0}}
)

2、$project

Function: modify the structure of input document, such as rename, add (show), delete (hide) fields, create calculation results
1. Show and hide
Format: 
DB. Set name. Aggregate ({$project:{_ ID: 0, field: 1})
A value of 0 is hidden
If the value is 1, it is displayed
Examples
db.stu.aggregate({$project: {_id: 0, name: 1, hometown: 1, age: 1, gender: 1}})
Note: showing and hiding fields are similar to projecting
2. Rename
example
db.stu.aggregate({$group: {_id: '$gender', count: {$sum: 1}, avg: {$avg: '$age'}}}, {$project: {_id: 0,gender: '$_id', counter: '$count', avg_age: '$avg'}})
be careful: 
Rename field format {new field name: '$old field name'}
Pipe characters are separated by commas

3、$match

Function: used to filter data and output only qualified documents
Note: match is a pipeline command, which can give the result to the next pipeline, but not find
example
db.stu.aggregate({$match: {age: {$lte: 18}}})
#Filter, group, rename and display
db.stu.aggregate({$match: {age: {$lte: 18}}}, {$group: {_id: '$gender', count: {$sum: 1}}}, {$project: {gender: '$_id', _id: 0, count: 1}})

4. $limit and $skip

$limit
Limits the number of documents returned by the aggregation pipeline
example
db.stu.aggregate({$limit: 2})

$skip
Skip the specified number of aggregate pipeline documents. And return the remaining documents
example
db.stu.aggregate({$skip: 2})
db.stu.aggregate({$limit: 2}, {$skip: 3})
Note the order: skip first, then limit

5、$unwind

#Untwist, untwist
Function: split an array type field in the document into several pieces, each containing a value in the array
Format:
DB. Set name. Aggregate ({$unwind: '$field name'})
example
db.t2.insert({_id: 1, item:'t-shirt', size: ['S', 'M', 'L']})
db.t2.aggregate({$unwind: '$size'})
result:
{ "_id" : 1, "item" : "t-shirt", "size" : "S" }
{ "_id" : 1, "item" : "t-shirt", "size" : "M" }
{ "_id" : 1, "item" : "t-shirt", "size" : "L" }

#Supplement

DB. Set name. Aggregate ({
    $unwind: {
        Path: '$field name',
        Preservenullandemptyarrays: # prevent data loss
    }
})
Property preservenullandemptyarrays value
False means discarding documents with null property values
True means to keep the document with empty property value

example
db.t3.aggregate({$unwind: {path: '$size', preserveNullAndEmptyArrays: false}})

,

1、 Foundation

1. What is aggregation?

Aggregation is an aggregation pipeline based on data processing. Each document passes through a pipeline composed of multiple stages
It can group and filter the pipelines in each stage, and then output the corresponding results after a series of processing

DB. Set name. Aggregate ({pipeline: {expression})

It’s a bit like the syntax of ORM aggregation in Django

2. Common pipeline

$group: group the documents in the collection for statistical results
$match: filter data and output only qualified documents
$project: modify the structure of the input document, such as renaming, adding, deleting fields, and creating calculation results

$sort: sort the input documents and output them
$limit: limits the number of documents returned by the aggregation pipeline
$skip: skips the specified number of documents and returns the remaining documents
$unwind(): splits fields of list (array) type

3. Common expressions

Process the input document and output it
Syntax: expression: '$column name'
Common expressions
$sum: calculate the sum$ sum:1  Indicates to count by one time
$AVG: count average
$Min: get the minimum
$Max: get the maximum
$push: inserts values into an array in the result document
$first: get the first document data according to the sorting of resource documents
$last: get the last document data according to the sorting of resource documents

2、 Common pipe usage

1、$group

Function: group the documents in the collection, which can be used for statistical results
_ ID represents the basis of grouping. The format of a field is' $field '
format
DB. Set name. Aggregate ({$group: {_ ID: '$field', custom field: {expression: '$field'})

db.stu.aggregate({$group: {_id: '$gender'}})
db.stu.aggregate({$group: {_id: '$gender', count: {$sum: 1}}})
db.stu.aggregate({$group: {_id: '$gender', avg_age: {$avg: '$age'}}})
db.stu.aggregate({$group: {_id: '$hometown', min_age: {$min: '$age'}, count: {$sum: 1}}})

be careful: 
    _id后面的值,表示按照什么分组,format'$字段'
    count, avg_ Count is a custom field
    The value of the expression is' $field '

Group by null

Divide the documents used in the collection into a group, that is, the collection is a group
#Find the total number and average age of students
db.stu.aggregate({$group: {_id: null, count:{$sum: 1}, avg_age:{$avg: '$age'}}})

supplement

#Insert data

db.test.insert({country: "china", province: "sh", userid: "a"})
db.test.insert({country: "china", province: "sh", userid: "b"})
db.test.insert({country: "china", province: "sh", userid: "a"})
db.test.insert({country: "china", province: "sh", userid: "c"})
db.test.insert({country: "china", province: "bj", userid: "da"})
db.test.insert({country: "china", province: "bj", userid: "fa"})

#1. Weight removal
It can be grouped by multiple keys at the same time. If each field in the document is grouped, it can realize the function of de duplication
db.test.aggregate({$group: {_id: {country: '$country', province: '$province'}}})
#2. Take the values in the dictionary nested in the dictionary 
_id: {contry: '$_id.country'}
example
db.test.aggregate(
{$group: {_id: {country: '$country', province: '$province', userid: '$userid'}}},  
{$group: {_id: {country: '$_id.country', province: '$_id.province'}, count: {$sum: 1}}}, 
{$project: {country: '$_id.country', province: '$_id.province', count: '$count', _id: 0}}
)

2、$project

Function: modify the structure of input document, such as rename, add (show), delete (hide) fields, create calculation results
1. Show and hide
Format: 
DB. Set name. Aggregate ({$project:{_ ID: 0, field: 1})
A value of 0 is hidden
If the value is 1, it is displayed
Examples
db.stu.aggregate({$project: {_id: 0, name: 1, hometown: 1, age: 1, gender: 1}})
Note: showing and hiding fields are similar to projecting
2. Rename
example
db.stu.aggregate({$group: {_id: '$gender', count: {$sum: 1}, avg: {$avg: '$age'}}}, {$project: {_id: 0,gender: '$_id', counter: '$count', avg_age: '$avg'}})
be careful: 
Rename field format {new field name: '$old field name'}
Pipe characters are separated by commas

3、$match

Function: used to filter data and output only qualified documents
Note: match is a pipeline command, which can give the result to the next pipeline, but not find
example
db.stu.aggregate({$match: {age: {$lte: 18}}})
#Filter, group, rename and display
db.stu.aggregate({$match: {age: {$lte: 18}}}, {$group: {_id: '$gender', count: {$sum: 1}}}, {$project: {gender: '$_id', _id: 0, count: 1}})

4. $limit and $skip

$limit
Limits the number of documents returned by the aggregation pipeline
example
db.stu.aggregate({$limit: 2})

$skip
Skip the specified number of aggregate pipeline documents. And return the remaining documents
example
db.stu.aggregate({$skip: 2})
db.stu.aggregate({$limit: 2}, {$skip: 3})
Note the order: skip first, then limit

5、$unwind

#Untwist, untwist
Function: split an array type field in the document into several pieces, each containing a value in the array
Format:
DB. Set name. Aggregate ({$unwind: '$field name'})
example
db.t2.insert({_id: 1, item:'t-shirt', size: ['S', 'M', 'L']})
db.t2.aggregate({$unwind: '$size'})
result:
{ "_id" : 1, "item" : "t-shirt", "size" : "S" }
{ "_id" : 1, "item" : "t-shirt", "size" : "M" }
{ "_id" : 1, "item" : "t-shirt", "size" : "L" }

#Supplement

DB. Set name. Aggregate ({
    $unwind: {
        Path: '$field name',
        Preservenullandemptyarrays: # prevent data loss
    }
})
Property preservenullandemptyarrays value
False means discarding documents with null property values
True means to keep the document with empty property value

example
db.t3.aggregate({$unwind: {path: '$size', preserveNullAndEmptyArrays: false}})

Recommended Today

[database] MySQL exercises and answers (educational administration management system)_ MySQL_ 5.7)

✨ statement The answer to the exercise is not guaranteed to be completely correct, which is for reference only MySQL:5.7 Recommended software: Navicat premium Learning SQL statements requires diligent practice! ✨ Field description of each table course CId Course serial number, primary key PCId Prerequisite course DId Opening department number, foreign key CName Course name […]