review

Review of Related Articles

mongoDB Query Advancement--Aggregation Pipeline(1) Review
mongoDB Query Advancement--Aggregation Pipeline(2) Review
mongoDB Query Advancement--Aggregation Pipeline(3) Review

Classification of pipeline operators

Pipeline operators can be divided into three categories:

1. Stage Operators
2. Expression Operators
3. Accumulators

Refer to the MongoDB website: https://docs.mongodb.com/manual/reference/operator/aggregation

The main content of this article is the accumulator in pipeline operators.

Accumulators

Accumulators could only be used under $group, but with version 3.2 or above, some can also be used for $project.Accumulators are used for each group when used in $group and for each literal quantity when used in $project.

Common accumulators

Operator	Sketch
$sum	Sum operator, v3.2+can be used for $group or $poject
$avg	Mean operator, v3.2+can be used for $group or $poject
$first	Used to return the first value of a grouping, only for $group
$last	Used to return the last value of a group, only for $group
$max	For returning the maximum value of a grouping, v3.2+ can be used for $group or $poject
$min	For returning the minimum value of a grouping, v3.2+ can be used for $group or $poject

$sum operator

Usage:

• Use under $group

{ $sum: <expression> }

• Use under $project:

{ $sum: <expression> }
    or
{ $sum: [ <expression1>, <expression2> ... ]  }

Explain:

The Works of Liezi	value	Result
{ $sum : <field> }	Number only	Sum of all values
{ $sum : <field> }	Contains numbers and non-numbers	Sum of all numeric values
{ $sum : <field> }	Non-numeric or non-existent	0

Give an example:

Take a look at the comprehensive examples that follow

$avg mean operator

Usage:

• Use under $group

{ $avg: <expression> }

• Use under $project:

{ $avg: <expression> }
    or
{ $avg: [ <expression1>, <expression2> ... ]  }

Give an example:

Take a look at the comprehensive examples that follow

first evaluator

Usage:

{ $first: <expression> }

Give an example:
Assume data:

[
    { _id: 1, name: 'kate', class: 'a' },
    { _id: 2, name: 'jack', class: 'a' },
    { _id: 3, name: 'kent', class: 'b' },
]

Operation:

db.collection.aggregate(
   [
     {
       $group:
         {
           _id: "$class",
           firstPersonName: { $first: "$name" }
         }
     }
   ]
)

Result:

[
    { _id: 'a', firstPersonName: 'kate' },
    { _id: 'b', firstPersonName: 'kent' },
]

$last evaluation last operator

Usage:

{ $last: <expression> }

Give an example:
Assume data:

[
    { _id: 1, name: 'kate', class: 'a' },
    { _id: 2, name: 'jack', class: 'a' },
    { _id: 3, name: 'kent', class: 'b' },
]

Operation:

db.collection.aggregate(
   [
     {
       $group:
         {
           _id: "$class",
           firstPersonName: { $last: "$name" }
         }
     }
   ]
)

Result:

[
    { _id: 'a', firstPersonName: 'jack' },
    { _id: 'b', firstPersonName: 'kent' },
]

$max maximum operator

Usage:

• Use under $group

{ $max: <expression> }

• Use under $project:

{ $max: <expression> }
    or
{ $max: [ <expression1>, <expression2> ... ]  }

Give an example:

Take a look at the comprehensive examples that follow

$min minimum operator

Usage:

• Use under $group

{ $min: <expression> }

• Use under $project:

{ $min: <expression> }
    or
{ $min: [ <expression1>, <expression2> ... ]  }

Give an example:

Take a look at the comprehensive examples that follow

Comprehensive example

Suppose you have a collection of results

[
    { _id: 1, name: 'kate', score: 80, class: 'a', subject: 'A' },
    { _id: 2, name: 'kate', score: 60, class: 'a', subject: 'B' },
    { _id: 3, name: 'jack', score: 90, class: 'a', subject: 'A' },
    { _id: 4, name: 'jack', score: 60, class: 'a', subject: 'B' },
    { _id: 5, name: 'nick', score: 80, class: 'b', subject: 'A' },
    { _id: 6, name: 'nick', score: 90, class: 'b', subject: 'B' },
    { _id: 7, name: 'kent', score: 50, class: 'b', subject: 'A' },
    { _id: 7, name: 'kent', score: 30, class: 'b', subject: 'B' },
]

Example 1

Requirements:

• Get the highest score in each subject
• Get the lowest score for each subject
• Get average score for each subject

Operation:

db.collection.aggregate(
   [
     {
       $group:
         {
           _id: "$subject",
           maxScore: { $max: '$score' },
           minScore: { $min: '$score' },
           avgScore: { $avg: '$score' },
         }
     }
   ]
)

Result:

[
    { _id: 'A', maxScore: 90, minScore: 50, avgScore: 75 },
    { _id: 'B', maxScore: 90, minScore: 30, avgScore: 60 },
]

Example 2

Requirements:

• Get total points per person
• Sort high to low

Operation:

db.collection.aggregate(
   [
     {
       $group:
         {
           _id: "$name",
           totalScore: { $sum: '$score' }
         }
     },
     {
        $sort: { totalScore: -1 } 
     },
     {
        $project: {
            _id: 0,
            name: '$_id',
            totalScore: 1,
        }
     }
   ]
)

Result:

[
    { name: 'nick', totalScore: 170 },
    { name: 'jack', totalScore: 150 },
    { name: 'kate', totalScore: 140 },
    { name: 'kent', totalScore: 80 }
]

Thank you for reading~