Learning goals
  1. Understand the aggregation principle of mongodb
  2. Master mongdb’s pipe command
  3. Master the expression of mongdb

1 What is mongodb aggregation

Aggregate is an aggregate pipeline based on data processing. Each document passes through a pipeline consisting of multiple stages, and the pipelines at each stage can be grouped and filtered. Then, corresponding results can be output after a series of processing.

Aggregate ({pipe :{expression}}) aggregate({pipe :{expression}})

2 Common pipes and expressions of mongodb

Knowledge:

  • Learn the syntax of pipes in mongodb
  • Master mongodb pipeline commands
2.1 Common Pipe Commands

In mongodb, after the document is processed, the pipeline is used for the next processing. Common pipeline commands are as follows:

  • $group: Groups documents in a collection that can be used for statistical results
  • $match: Filters data and outputs only the documents that meet the conditions
  • $project: Modifies the structure of the input document, such as renaming, adding, deleting fields, and creating computed results
  • $sort: Sorts the input documents and outputs them
  • $limit: limits the number of documents returned by the aggregation pipe
  • $skip: Skips a specified number of documents and returns the remaining documents
2.2 Common Expressions

Expression: process input document and output syntax: expression :’$column name ‘

  • $sum$sum:1
  • $avg: Calculate the average value
  • $min: Gets the minimum value
  • $max: Get the maximum value
  • $push: Inserts values into an array in the result document

Pipe command$group

3.1 Grouping Groups By a Field

The $group command is the most common of all the aggregation commands and is used to group documents in a collection that can be used for statistical results

An example is as follows:

db.stu.aggregate(
    {$group:
        {
            _id:"$gender",
            counter:{$sum:1}
        }
    }
)
Copy the code

Points to note:

  • db.db_name.aggregateSyntax, where all pipe commands need to be written
  • _idIndicates the field by which groups are grouped$genderIndicates that the field is selected for grouping
  • $sum:1Indicates that each item of data is counted as 1. The number of items of data in this group is counted
3.2 group by null

Another use of $group when we need to count the entire document is to group the entire document for statistics

Examples are as follows:

db.stu.aggregate(
    {$group:
        {
            _id:null,
            counter:{$sum:1}
        }
    }
)
Copy the code

Points to note:

  • _id:nullIndicates a field that does not specify grouping, that is, the entire document is countedcounterRepresents the number of entire documents
3.3 Data Perspective

In normal circumstances, when collecting data of different genders, all names need to be known and observed item by item. If all names are put together in some way, it can be understood as data perspective at this time

The following is an example:

  1. Students of different genders are counted

    db.stu.aggregate(
    {$group:
        {
            _id:null,
            name:{$push:"$name"}
        }
    }
)
Copy the code

  2. Use? ROOT can put the entire document into an array

    db.stu.aggregate( {$group: { _id:null, name:{$push:"? ROOT"} } } )Copy the code
3.4 to start work

For the following data, the number of userids in each country/province needs to be counted.

{ "country" : "china"."province" : "sh"."userid" : "a" }  
{  "country" : "china"."province" : "sh"."userid" : "b" }  
{  "country" : "china"."province" : "sh"."userid" : "a" }  
{  "country" : "china"."province" : "sh"."userid" : "c" }  
{  "country" : "china"."province" : "bj"."userid" : "da" }  
{  "country" : "china"."province" : "bj"."userid" : "fa" }
Copy the code

Refer to the answer

db.tv3.aggregate(
  {$group:{_id:{country:'$country',province:'$province',userid:'$userid'}}},
  {$group:{_id:{country:'$_id.country',province:'$_id.province'},count:{$sum: 1}}}Copy the code

4 Pipeline command$match

$match is used to filter data and can be used in aggregation operations. It differs from find in that $match can pass results to the next pipe, while find cannot

The following is an example:

  1. Select * from student where age > 20

    db.stu.aggregate(
    {$match:{age:{$gt:20}}
    )
Copy the code

  2. Select * from student where age > 20

    db.stu.aggregate(
    {$match:{age:{$gt:20}}
    {$group:{_id:"$gender",counter:{$sum:1}}}
    )
Copy the code

5 Pipeline command$project

$project is used to modify the input/output structure of a document, such as renaming, adding, or deleting fields

The following is an example:

  1. Query student’s age, name, only age name

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

  2. Query male and female students life, output number

    db.stu.aggregate(
    {$group:{_id:"$gender",counter:{$sum:1}}}
    {$project:{_id:0,counter:1}}
    )
Copy the code
5.1 Hands-on practice

{country:””, province:””, counter:”*”}

{ "country" : "china"."province" : "sh"."userid" : "a" }  
{  "country" : "china"."province" : "sh"."userid" : "b" }  
{  "country" : "china"."province" : "sh"."userid" : "a" }  
{  "country" : "china"."province" : "sh"."userid" : "c" }  
{  "country" : "china"."province" : "bj"."userid" : "da" }  
{  "country" : "china"."province" : "bj"."userid" : "fa" }  
Copy the code

Refer to the answer

db.tv3.aggregate(
  {$group:{_id:{country:'$country',province:'$province',userid:'$userid'}}},
  {$group:{_id:{country:'$_id.country',province:'$_id.province'},count:{$sum: 1}}}, {$project:{_id:0,country:'$_id.country',province:'$_id.province',counter:'$count'}})Copy the code

Pipe command$sort

$sort is used to sort the input documents for output

The following is an example:

  1. Query student information in ascending order by age

    db.stu.aggregate({$sort:{age:1}})
Copy the code

  2. Query the number of men and women in descending order

    db.stu.aggregate(
    {$group:{_id:"$gender",counter:{$sum:1}}},
    {$sort:{counter:-1}}
)
Copy the code

7 Pipeline command$skip 和 $limit

  • $limitLimit the number of pieces of data returned
  • $skipSkip the specified number of documents and return the remaining number of documents
  • For simultaneous use, skip is used before limit is used

The following is an example:

  1. Example Query two student information

    db.stu.aggregate(
    {$limit:2}
)
Copy the code

  2. Query student information starting from item 3

    db.stu.aggregate(
    {$skip:3}
)
Copy the code

  3. Number of boys and girls, in ascending order, return the second data

    db.stu.aggregate(
    {$group:{_id:"$gender",counter:{$sum:1}}},
    {$sort:{counter:-1}},
    {$skip:1},
    {$limit:1}
)
Copy the code

8 subtotal

  1. What is it that understands the aggregation operation
  2. master$group.$match.$projectThe use of
  3. Be familiar with$sort.$limit.$skipThe use of
  4. Implement commonly used expressions