The aggregation operation of MongoDB learning

The basic operation of MongoDB learning

The analogy of mongo's aggregation operation and mysql's query

SQL operations/functions	mongodb aggregation operation
where	$match
group by	$group
having	$match
select	$project
order by	$sort
limit	$limit
sum()	$sum
count()	$sum
join	$lookup

The following examples are compared with sql

The following is a basic structure for using the database

data link (extraction code: gqh2)

{ 
  _id: ObjectId("5dbe7a545368f69de2b4d36e"),
  street: '493 Hilll Curve',
  city: 'Champlinberg',
  state: 'Texas',
  country: 'Malaysia',
  zip: '24344-1715',
  phone: '425.956.7743 x4621',
  name: 'Destinee Schneider',
  userId: 3573,
  orderDate: 2019-03-26T03:20:08.805Z,
  status: 'created',
  shippingFee: Decimal128("8.00"),
  orderLines: [ 
     { 
       product: 'Refined Fresh Tuna',
       sku: '2057',
       qty: 25,
       price: Decimal128("56.00"),
       cost: Decimal128("46.48") },
     {
       product: 'Refined Concrete Ball',
       sku: '1732',
       qty: 61,
       price: Decimal128("47.00"),
       cost: Decimal128("47") 
     },
  ],
  total: Decimal128("407") 
}

First some operation cases

select sum(total) from orders

db.orders.aggregate({$group:{_id:null,total:{$sum:"$total"}}})
结果：{ _id: null, 'total': Decimal128("44019609") }

select count(1) from orders

db.orders.aggregate({$group:{_id:null,total:{$sum:1}}})
结果：{ _id: null, total: 100000 }

select count(1) from orders group by status

db.orders.aggregate({$group:{_id:"$status",total:{$sum:1}}})
结果：
{ _id: 'created', total: 20087 }
{ _id: 'shipping', total: 20017 }
{ _id: 'cancelled', total: 19978 }
{ _id: 'completed', total: 20015 }
{ _id: 'fulfilled', total: 19903 }

select count(1) from orders group by status having count(1) > 20000

db.orders.aggregate([
    {$group:{_id:{status:'$status'},total:{$sum:1}}},
    {$match:{total:{$gte:20000}}}
 ])
结果：
{ _id: { status: 'created' }, total: 20087 }
{ _id: { status: 'shipping' }, total: 20017 }
{ _id: { status: 'completed' }, total: 20015 }

select count(1) total
from orders 
group by status,year(orderDate),month(orderDate)
order by year(orderDate),month(orderDate)

db.orders.aggregate([
  {
    $group:{
      _id:{
         status:'$status',
         orderDate:{
           year:{$year:"$orderDate"},
           month:{$month:"$orderDate"}
         }
       },
       total:{$sum:1}
     }
  },{
    $sort:{"_id.orderDate.year":1,"_id.orderDate.month":1}
  }
])
结果：
{ _id: { status: 'cancelled', orderDate: { year: 2019, month: 1 } }, total: 2066 }
{ _id: { status: 'shipping', orderDate: { year: 2019, month: 1 } }, total: 2058 }
{ _id: { status: 'completed', orderDate: { year: 2019, month: 1 } }, total: 2068 }
{ _id: { status: 'created', orderDate: { year: 2019, month: 1 } }, total: 2047 }
{ _id: { status: 'fulfilled', orderDate: { year: 2019, month: 1 } }, total: 2076 }
{ _id: { status: 'cancelled', orderDate: { year: 2019, month: 2 } }, total: 1816 }
{ _id: { status: 'created', orderDate: { year: 2019, month: 2 } }, total: 1817 }
{ _id: { status: 'shipping', orderDate: { year: 2019, month: 2 } }, total: 1844 }
{ _id: { status: 'completed', orderDate: { year: 2019, month: 2 } }, total: 1813 }
{ _id: { status: 'fulfilled', orderDate: { year: 2019, month: 2 } }, total: 1913 }
......

select *
from(
    select month(orderDate) month,name,status
    from orders
) order
where month = 2

db.orders.aggregate([{$project:{month:{$month:"$orderDate"},name:1,status:1}},{$match:{month:2}}]) 
结果：
{ _id: ObjectId("5dbe7a542411dc9de6429190"),name: 'Kris Hansen',status: 'cancelled',month: 2 }
{ _id: ObjectId("5dbe7a542411dc9de6429191"),name: 'Constantin Wuckert',status: 'completed',month: 2 }
{ _id: ObjectId("5dbe7a545368f69de2b4d375"),name: 'Reed Jerde',status: 'fulfilled',month: 2 }
{ _id: ObjectId("5dbe7a54cd023b9de4efc1d2"),name: 'Lyric Hodkiewicz',status: 'cancelled',month: 2 }
.....

select count(*) from orders where month(orderDate) >= 3 group by month(orderDate)

db.orders.aggregate([
    {$match:{$expr:{$gte:[{$month:"$orderDate"},3]}}},
    {$group:{_id:{month:{$month:"$orderDate"}},count:{$sum:1}}}
 ]);
#结果
{ _id: { month: 6 }, count: 9915 }
{ _id: { month: 4 }, count: 10083 }
{ _id: { month: 10 }, count: 9928 }
{ _id: { month: 5 }, count: 10142 }
{ _id: { month: 8 }, count: 10194 }
{ _id: { month: 9 }, count: 9779 }
{ _id: { month: 7 }, count: 10240 }
{ _id: { month: 3 }, count: 10201 }

MongoDB Aggregate (Aggregate)

MongoDB aggregate (aggregate) is mainly used to process data (such as statistical average, sum, etc.), and return the calculated data result

A aggregate composed of multiple stages. The result of the previous stage will be used as the input of the next stage, so it will also be vividly called the pipeline (Pipeline).

Expression: Process the input document and output. Expressions are stateless and can only be used to calculate documents in the current aggregation pipeline, and cannot process other documents.

Here we introduce several commonly used operations in the aggregation framework:

• $project : Modify the structure of the input document. It can be used to rename, add or delete fields, and can also be used to create calculation results and nested documents.
• $match : Used to filter data, and only output documents that meet the conditions. $match uses the standard query operation of MongoDB
• $limit : Used to limit the number of documents returned by the MongoDB
• $skip : Skip the specified number of documents in the aggregation pipeline and return the remaining documents.
• $unwind : Split a certain array type field in the document into multiple pieces, each containing one value in the array.
• $group : Group the documents in the collection, which can be used for statistical results.
• $sort : Sort the input documents and output them.
• $geoNear : Output ordered documents close to a certain geographic location.

The following is a basic processing flow of aggregate

• db.collection.aggregate() can use multiple components to create a pipeline to process a series of documents. These components include: filtering operation match , mapping operation project , the grouping operation group , sort operations sort , limiting operation of limit , and a skip operation skip .
• db.collection.aggregate() uses the MongoDB , the aggregation efficiency is very high, and it supports functions similar to the operation of SQL Group By
• Each stage pipeline is limited to the memory 100MB If a node pipeline exceeds this limit, MongoDB will generate an error. In order to be able to process large data sets, you can set allowDiskUse to true to write data to a temporary file at the aggregation pipeline node. This can solve the memory limitation of 100MB
• db.collection.aggregate() can act on the shard collection, but the result cannot be lost in the MapReduce collection, 061a844ce2e90b can act on the shard collection, and the result can also be lost in the shard collection.
• db.collection.aggregate() method can return a pointer, and the data is stored in memory for direct manipulation. Same pointer operation as Mongo shell
• db.collection.aggregate() output result of 061a844ce2e97d can only be saved in one file, and the BSON Document is limited to 16M . It can be solved by returning a pointer. In version 2.6 later: The db.collect.aggregate() method returns a pointer, which can return the size of any result set.

$count

Return document statistics

First look at the use count some non-aggregation operations

#对应查询出来的是orders这个集合中的所有数据总和
db.orders.count();
#结果:{"result": 100000}
#对应查出来自Malaysia这个国家的订单总和
db.orders.find({country:"Malaysia"}).count()
#结果:{"result": 392}

Use $count in the aggregation operation to aggregate the number of rows

#使用聚合查出来自Malaysia这个国家的订单总和,并且返回给counts字段
db.orders.aggregate([
    {$match:{country:"Malaysia"}},
    {$count:"counts"}
])
#结果:{"counts": 392}
#下面是两种不同的写法只是在 $match的时候有所区别(可以先体验以下)
db.orders.aggregate([
    {$match:{country:{$eq:"Malaysia"}}},
    {$count:"counts"}
])
db.orders.aggregate([
    {$match:{$expr:{$eq:["$country","Malaysia"]}}},
    {$count:"counts"}
])
#结果:{"counts": 392}

In addition, you can flexibly use group + $sum to achieve $count

#对应查询出来的是orders这个集合中的所有数据总和,并且返回给counts字段
db.orders.aggregate({$group:{_id:null,counts:{$sum:1}}})
#结果:{"_id": null,"counts": 100000}
#使用聚合查出来自Malaysia这个国家的订单总和,并且返回给counts字段
db.orders.aggregate([
    {$match:{country:{$eq:"Malaysia"}}},
    {$group:{_id:null,counts:{$sum:1}}}
])
#结果:{"_id": null,"counts": 392}

$group

Group documents according to the specified expression

The basic syntax used by $group

{ $group: { _id: <expression>, <field1>: { <accumulator1> : <expression1> }, ... } }

1. _id + expression used for grouping condition, i.e. _id content behind the sql in group by same purpose behind the expression
2. _id field after + accumulator operator and sql the finish group by after select later use the same aggregation function, for example: sum() , avg() , max() , min()

E.g:

db.orders.aggregate({$group:{_id:"$country",total:{$sum:"$total"}}})
#结果
{ _id: 'Guam', total: Decimal128("182335") }
{ _id: 'El Salvador', total: Decimal128("159475") }
{ _id: 'Saint Martin', total: Decimal128("163267") }
{ _id: 'Botswana', total: Decimal128("189330") }
{ _id: 'San Marino', total: Decimal128("174200") }
{ _id: 'Czech Republic', total: Decimal128("178602") }
{ _id: 'Estonia', total: Decimal128("172816") }
.......
#上面的mql相当于sql中的
select sum(total) from orders group by country

$group memory limit stage is 100M . By default, if stage exceeds this limit, $group will generate an error. However, to allow processing of large data sets, set the allowDiskUse option to true to enable the $group operation to write temporary files.

name	describe	Analogy sql
$avg	Calculate the mean	avg
$first	Return the first document of each group, if there is a sort, according to the sort, if not according to the default storage order of the first document.	limit 0,1
$last	Return the last document of each group, if there is a sort, according to the sort, if not according to the default storage order of the last document.	-
$max	According to the grouping, obtain the maximum value of all the documents in the collection.	max
$min	According to the grouping, obtain the minimum value of all the documents in the collection.	min
$push	Add the value of the specified expression to an array.	-
$addToSet	Add the value of the expression to a set (no duplicate values, unordered).	-
$sum	Calculate the sum	sum
$stdDevPop	Returns the population standard deviation of the input value (population standard deviation)	-
$stdDevSamp	Returns the sample standard deviation of the input value (the sample standard deviation)	-

Below we use each expression in turn according to the above document

1. $avg calculate the average

   --计算每个国家的每个订单的平均消费
   db.orders.aggregate({$group:{
           _id:"$country",
           avgMoney:{$avg:"$total"}
       }})
   --结果    
   { _id: 'Saudi Arabia',avgMoney: Decimal128("433.4898419864559819413092550790068") }
   { _id: 'New Caledonia',avgMoney: Decimal128("441.9833729216152019002375296912114") }
   { _id: 'Congo',avgMoney: Decimal128("451.8834951456310679611650485436893") }
   { _id: 'Turkey',avgMoney: Decimal128("425.7422434367541766109785202863962") }
   { _id: 'Cuba',avgMoney: Decimal128("437.2074074074074074074074074074074") }
   { _id: 'Uruguay',avgMoney: Decimal128("434.1564792176039119804400977995110") }
   ......

2. $first returns the first document

   --根据国家分组，每组第一笔订单的订单商品列表
   db.orders.aggregate({$group:{
       _id:"$country",
       firstOrderLines:{$first:"$orderLines"}
   }})
   --结果
   { _id: 'Malta',firstOrderLines: [ 
     { product: 'Sleek Frozen Table',sku: '368',qty: 30,price: Decimal128("24.00"),cost:Decimal128("19.44") },
     { product: 'Intelligent Metal',sku: '179',qty: 62,price: Decimal128("91.00"),cost: Decimal128("90.09") },
     { product: 'Intelligent Granite',sku: '9',qty: 31,price: Decimal128("68.00"),cost: Decimal128("61.88") },
     { product: 'Licensed Cotton',sku: '6846',qty: 9,price: Decimal128("16.00"),cost: Decimal128("15.68") } 
   ] }
   { _id: 'Papua New Guinea',firstOrderLines: [ 
     { product: 'Fantastic Wooden',sku: '4333',qty: 32,price: Decimal128("58.00"),cost: Decimal128("57.42") }
     ...
   ] }
   ......
   
   --根据国家分组，每组第一笔订单的订单商品列表里面的第一条商品信息
   db.orders.aggregate({$group:{
       _id:"$country",
       firstOrder:{$first:{$first:"$orderLines"}}
   }})
   ---结果
   { _id: 'Malta',firstOrder:  
    { product: 'Sleek Frozen Table',sku: '368',qty: 30,price: Decimal128("24.00"),cost:Decimal128("19.44") }
   }
   { _id: 'Papua New Guinea',firstOrder:
     { product: 'Fantastic Wooden',sku: '4333',qty: 32,price: Decimal128("58.00"),cost: Decimal128("57.42") }
   }
   ......

3. $last returns the last document

   --根据每个国家分组，每笔最后一个订单的orderDate
   db.orders.aggregate([{$group:{
       _id:"$country",
       lastOrderDate:{$last:"$orderDate"}
   }}])
   --结果
   { _id: 'Micronesia', lastOrderDate: 2019-01-15T07:23:18.002Z }
   { _id: 'Malaysia', lastOrderDate: 2019-05-15T20:16:56.644Z }
   { _id: 'San Marino', lastOrderDate: 2019-09-29T06:10:07.292Z }

4. $max and $min : maximum and minimum

   --根据年月分组，查出每组第一笔订单时间和最后一组订单时间
   db.orders.aggregate({$group:{
       _id:{year:{$year:"$orderDate"},month:{$month:"$orderDate"}},
       maxDate:{$max:"$orderDate"},
       minDate:{$min:"$orderDate"}
   }})
   --结果
   { _id: { year: 2019, month: 1 }, maxDate: 2019-01-31T23:53:57.308Z, minDate: 2019-01-01T00:03:59.661Z }
   { _id: { year: 2019, month: 4 }, maxDate: 2019-04-30T23:57:03.352Z, minDate: 2019-04-01T00:02:12.224Z }
   { _id: { year: 2019, month: 3 }, maxDate: 2019-03-31T23:55:10.312Z, minDate: 2019-03-01T00:13:53.761Z }
   { _id: { year: 2019, month: 7 }, maxDate: 2019-07-31T23:55:51.718Z, minDate: 2019-07-01T00:00:07.540Z }

5. $push adds the specified value to an array can be pushed to an existing array, if it does not exist, it will create such an array

   --根据城市、年、月分组，将每组的下单时间push到一个新的 orderDates 数组当中
   db.orders.aggregate({$group:{
       _id:{city:"$city",year:{$year:"$orderDate"},month:{$month:"$orderDate"}},
       orderDates:{$push:"$orderDate"},
   }})
   --结果
   { _id: { city: 'Kennedifurt', year: 2019, month: 9 }, orderDates: [ 2019-09-30T10:25:19.763Z ] }
   { _id: { city: 'South Jewelstad', year: 2019, month: 1 }, orderDates: [ 2019-01-06T19:59:03.380Z ] }
   { _id: { city: 'Germanmouth', year: 2019, month: 9 }, orderDates: [ 2019-09-25T07:45:54.260Z ] }
   { _id: { city: 'Fayebury', year: 2019, month: 8 }, orderDates: [ 2019-08-12T11:08:37.815Z ] }
   { _id: { city: 'New Lailaport', year: 2019, month: 1 }, orderDates: [ 2019-01-19T12:28:56.978Z ] }
   { _id: { city: 'Port Bennyside', year: 2019, month: 2 }, orderDates: [ 2019-02-25T01:18:21.657Z ] }
   { _id: { city: 'Abernathymouth', year: 2019, month: 6 }, orderDates: 
      [ 2019-06-03T18:03:21.149Z,
        2019-06-13T23:35:32.994Z,
        2019-06-18T11:32:22.229Z ] 
   }

6. $addToSet Add the specified value to a set set is unordered and will de-

   --根据月份分组，将每个月都下单过的国家都添加到 countrySet 中去
   db.orders.aggregate({
       $group:{
           _id:{year:{$year:"$orderDate"},month:{$month:"$orderDate"}},
           countrySet:{$addToSet:"$country"}
       }
   })
   --结果  
   {
       "_id": {
         "year": 2019,
         "month": 1
       },
       "countrySet": ["French Guiana", "Germany", "Poland", "Comoros", "Portugal", "Fiji", "France", "Benin", "Greece", "Belarus", "Vietnam", "Ireland", "Vanuatu", "Netherlands Antilles", "Iceland", "Palestinian Territory", "Malawi", "Brazil", "Libyan Arab Jamahiriya", "Kuwait", "Liechtenstein", "Suriname", "Uganda", "New Caledonia", "Bolivia", "Nicaragua", "Burundi", "Uzbekistan", "Jamaica", "South Georgia and the South Sandwich Islands", "Tajikistan", "Mexico", "Singapore", "Sri Lanka", "Antarctica (the territory South of 60 deg S)", "Myanmar", "Tonga", "Slovenia", "Latvia", "Ukraine", "Oman", "Saint Helena", "Bosnia and Herzegovina", "Hungary", "Aruba", "Jordan", "Solomon Islands", "Mozambique", "Svalbard & Jan Mayen Islands", "Taiwan", "Cyprus", "Thailand", "Equatorial Guinea", "Belize", "Niger", "Israel", "Hong Kong", "Senegal", "Costa Rica", "Sierra Leone", "Kiribati", "Lesotho", "Nepal", "Serbia", "Barbados", "Spain", "Czech Republic", "Saint Martin", "Saint Pierre and Miquelon", "Togo", "Somalia", "Northern Mariana Islands", "Maldives", "British Indian Ocean Territory (Chagos Archipelago)", "Montenegro", "Cote d'Ivoire", "United Arab Emirates", "Guernsey", "Bulgaria", "Netherlands", "Greenland", "Niue", "Colombia", "Egypt", "Madagascar", "Brunei Darussalam", "Iraq", "Mauritius", "French Polynesia", "Jersey", "Canada", "Grenada", "Honduras", "New Zealand", "Cocos (Keeling) Islands", "Mayotte", "Virgin Islands, British", "Finland", "Macedonia", "Cook Islands", "Micronesia", "Christmas Island", "Turks and Caicos Islands", "Falkland Islands (Malvinas)", "El Salvador", "Estonia", "Eritrea", "Afghanistan", "San Marino", "Malaysia", "Cambodia", "Anguilla", "Philippines", "Zambia", "Republic of Korea", "Mauritania", "Yemen", "South Africa", "Gambia", "Namibia", "Peru", "Samoa", "Qatar", "Guinea", "Monaco", "Mongolia", "Cayman Islands", "Bouvet Island (Bouvetoya)", "Romania", "Sweden", "Guam", "Guyana", "Japan", "Bangladesh", "Djibouti", "Reunion", "Central African Republic", "Martinique", "Sudan", "Norway", "Guadeloupe", "Malta", "Papua New Guinea", "Macao", "Tunisia", "Iran", "Ghana", "Trinidad and Tobago", "Syrian Arab Republic", "French Southern Territories", "Russian Federation", "Botswana", "Pakistan", "Luxembourg", "Ethiopia", "Austria", "Rwanda", "Holy See (Vatican City State)", "American Samoa", "Tanzania", "Morocco", "Lao People's Democratic Republic", "Faroe Islands", "Bahrain", "China", "Indonesia", "Ecuador", "Tuvalu", "Panama", "Algeria", "Gibraltar", "Nigeria", "Kyrgyz Republic", "Chile", "Cape Verde", "Palau", "Armenia", "Dominican Republic", "Bhutan", "Liberia", "India", "Mali", "Switzerland", "Isle of Man", "Argentina", "Virgin Islands, U.S.", "Swaziland", "Timor-Leste", "Azerbaijan", "Bahamas", "Guatemala", "Saint Lucia", "Sao Tome and Principe", "United States Minor Outlying Islands", "Australia", "Italy", "Paraguay", "Tokelau", "Gabon", "Wallis and Futuna", "Cameroon", "Norfolk Island", "Guinea-Bissau", "Chad", "Zimbabwe", "Nauru", "Pitcairn Islands", "Georgia", "Kenya", "Bermuda", "Kazakhstan", "Democratic People's Republic of Korea", "Puerto Rico", "Croatia", "Antigua and Barbuda", "Seychelles", "Marshall Islands", "Burkina Faso", "Denmark", "United Kingdom", "Dominica", "Albania", "Angola", "Slovakia (Slovak Republic)", "Western Sahara", "Belgium", "Saudi Arabia", "Turkey", "Congo", "Cuba", "Uruguay", "Montserrat", "United States of America", "Lebanon", "Saint Vincent and the Grenadines", "Saint Kitts and Nevis", "Saint Barthelemy", "Haiti", "Moldova", "Heard Island and McDonald Islands", "Lithuania", "Turkmenistan", "Venezuela", "Andorra"]
     },
     {
       "_id": {
         "year": 2019,
         "month": 9
       },
       "countrySet": ["Germany", "Poland", "French Guiana", "Fiji", "France", "Comoros", "Portugal", "Benin", "Greece", "Belarus", "Ireland", "Vietnam", "Brazil", "Malawi", "Vanuatu", "Netherlands Antilles", "Palestinian Territory", "Iceland", "Kuwait", "Libyan Arab Jamahiriya", "Liechtenstein", "New Caledonia", "Suriname", "Uganda", "Bolivia", "Uzbekistan", "Burundi", "Nicaragua", "Tajikistan", "Jamaica", "South Georgia and the South Sandwich Islands", "Sri Lanka", "Mexico", "Singapore", "Antarctica (the territory South of 60 deg S)", "Tonga", "Myanmar", "Slovenia", "Latvia", "Oman", "Saint Helena", "Ukraine", "Bosnia and Herzegovina", "Aruba", "Jordan", "Hungary", "Mozambique", "Solomon Islands", "Svalbard & Jan Mayen Islands", "Thailand", "Taiwan", "Cyprus", "Equatorial Guinea", "Belize", "Niger", "Senegal", "Hong Kong", "Israel", "Kiribati", "Costa Rica", "Sierra Leone", "Lesotho", "Saint Martin", "Spain", "Barbados", "Nepal", "Togo", "Maldives", "Czech Republic", "Somalia", "Saint Pierre and Miquelon", "Serbia", "Northern Mariana Islands", "Montenegro", "British Indian Ocean Territory (Chagos Archipelago)", "Cote d'Ivoire", "United Arab Emirates", "Guernsey", "Niue", "Bulgaria", "Netherlands", "Egypt", "Colombia", "Greenland", "Brunei Darussalam", "Madagascar", "Mauritius", "Iraq", "Canada", "French Polynesia", "Jersey", "Grenada", "Cocos (Keeling) Islands", "New Zealand", "Honduras", "Virgin Islands, British", "Mayotte", "Cook Islands", "Finland", "Macedonia", "Micronesia", "Turks and Caicos Islands", "Christmas Island", "Estonia", "Falkland Islands (Malvinas)", "El Salvador", "Eritrea", "Malaysia", "San Marino", "Afghanistan", "Anguilla", "Cambodia", "Zambia", "Republic of Korea", "Mauritania", "Philippines", "South Africa", "Gambia", "Yemen", "Qatar", "Peru", "Namibia", "Guinea", "Samoa", "Cayman Islands", "Monaco", "Mongolia", "Bouvet Island (Bouvetoya)", "Romania", "Sweden", "Guam", "Guyana", "Djibouti", "Japan", "Bangladesh", "Reunion", "Central African Republic", "Sudan", "Norway", "Martinique", "Guadeloupe", "Papua New Guinea", "Malta", "Tunisia", "Macao", "Iran", "Ghana", "Syrian Arab Republic", "Trinidad and Tobago", "French Southern Territories", "Botswana", "Luxembourg", "Russian Federation", "Pakistan", "Ethiopia", "Holy See (Vatican City State)", "Panama", "Austria", "Rwanda", "American Samoa", "Faroe Islands", "Tanzania", "Morocco", "Lao People's Democratic Republic", "Ecuador", "China", "Indonesia", "Bahrain", "Algeria", "Tuvalu", "Gibraltar", "Nigeria", "Kyrgyz Republic", "Chile", "Palau", "Cape Verde", "Bhutan", "Dominican Republic", "Armenia", "Mali", "Isle of Man", "Liberia", "India", "Switzerland", "Argentina", "Virgin Islands, U.S.", "Timor-Leste", "Swaziland", "Azerbaijan", "United States Minor Outlying Islands", "Saint Lucia", "Bahamas", "Guatemala", "Australia", "Sao Tome and Principe", "Tokelau", "Paraguay", "Italy", "Wallis and Futuna", "Gabon", "Cameroon", "Guinea-Bissau", "Chad", "Norfolk Island", "Zimbabwe", "Nauru", "Georgia", "Kenya", "Pitcairn Islands", "Bermuda", "Kazakhstan", "Democratic People's Republic of Korea", "Croatia", "Puerto Rico", "Antigua and Barbuda", "Seychelles", "Marshall Islands", "Burkina Faso", "Dominica", "Denmark", "Albania", "United Kingdom", "Angola", "Slovakia (Slovak Republic)", "Western Sahara", "Belgium", "Turkey", "Congo", "Saudi Arabia", "Uruguay", "Cuba", "United States of America", "Montserrat", "Lebanon", "Saint Kitts and Nevis", "Saint Vincent and the Grenadines", "Saint Barthelemy", "Haiti", "Moldova", "Lithuania", "Heard Island and McDonald Islands", "Turkmenistan", "Venezuela", "Andorra"]
     }
   .......

7. $sum calculate the sum

   --根据月份分组，获取每组的收入总和 sumTotal
   db.orders.aggregate({
       $group:{
           _id:{year:{$year:"$orderDate"},month:{$month:"$orderDate"}},
           sumTotal:{$sum:"$total"}
       }
   })
   --结果
   { _id: { year: 2019, month: 2 }, sumTotal: Decimal128("4072808") }
   { _id: { year: 2019, month: 10 }, sumTotal: Decimal128("4356471") }
   { _id: { year: 2019, month: 5 }, sumTotal: Decimal128("4460433") }
   ......

8. $stdDevPop returns the overall standard deviation of the input value

   

   --根据月份分组,计算总体准偏差计算
   db.orders.aggregate({
       $group:{
           _id:{year:{$year:"$orderDate"},month:{$month:"$orderDate"}},
           popTotal:{$stdDevPop:"$total"}
       }
   })
   --结果
   { _id: { year: 2019, month: 2 }, popTotal: 189.3064965965138 }
   { _id: { year: 2019, month: 10 }, popTotal: 187.19676293125292 }
   { _id: { year: 2019, month: 5 }, popTotal: 189.54277980510432 }
   { _id: { year: 2019, month: 8 }, popTotal: 189.52305549485735 }
   { _id: { year: 2019, month: 6 }, popTotal: 189.99641948294692 }
   { _id: { year: 2019, month: 1 }, popTotal: 188.89723701416594 }
   { _id: { year: 2019, month: 4 }, popTotal: 189.33635941008336 }
   { _id: { year: 2019, month: 3 }, popTotal: 190.39465578257668 }
   { _id: { year: 2019, month: 7 }, popTotal: 189.01641050584374 }
   { _id: { year: 2019, month: 9 }, popTotal: 188.10379143822877 }

9. $stdDevSamp returns the sample standard deviation of the input value

   

   --根据月份分组,计算样本准偏差计算
   db.orders.aggregate({
       $group:{
           _id:{year:{$year:"$orderDate"},month:{$month:"$orderDate"}},
           sampTotal:{$stdDevSamp:"$total"}
       }
   })
   --结果
   { _id: { year: 2019, month: 2 }, sampTotal: 189.31678247750685 }
   { _id: { year: 2019, month: 9 }, sampTotal: 188.1134099175866 }
   { _id: { year: 2019, month: 7 }, sampTotal: 189.02564049879336 }
   { _id: { year: 2019, month: 3 }, sampTotal: 190.40398862519802 }
   { _id: { year: 2019, month: 5 }, sampTotal: 189.55212494401323 }
   { _id: { year: 2019, month: 4 }, sampTotal: 189.34574899869335 }
   { _id: { year: 2019, month: 1 }, sampTotal: 188.90639411415503 }
   { _id: { year: 2019, month: 8 }, sampTotal: 189.53235199281477 }
   { _id: { year: 2019, month: 6 }, sampTotal: 190.00600146946147 }
   { _id: { year: 2019, month: 10 }, sampTotal: 187.20619136123352 }

$match

Accept a document with specified query conditions. The query syntax is the same as the read operation query syntax.

Basic grammar { $match: { <query> } }

$match in the front position pipeline as much as possible in actual application. This has two advantages:

1. It can quickly filter out unwanted documents to reduce the workload of the pipeline
2. $match executed before projection and grouping, query can use index .

--类似于in查询
db.orders.aggregate({
    $match:{
        country:{$in:["Romania", "Sweden", "Guam", "Guyana"]}
    }
})
--结果：查出这几个国家的订单

--范围查询
db.orders.aggregate({
    $match:{
        orderDate:{$gte:ISODate("2019-02-01"),$lt:ISODate("2019-02-04")}
    }
})
--结果:查出 2019-02-01 到 2019-02-03这三天的所有订单

$expr Use aggregate expressions

--查询3月份以及往后的数据 - 根据月份分组 - sum订单数
db.orders.aggregate([
    {$match:{$expr:{$gte:[{$month:"$orderDate"},3]}}},
    {$group:{_id:{month:{$month:"$orderDate"}},count:{$sum:1}}}
 ]);
--结果
{ _id: { month: 7 }, count: 10240 }
{ _id: { month: 5 }, count: 10142 }
{ _id: { month: 6 }, count: 9915 }
{ _id: { month: 4 }, count: 10083 }
{ _id: { month: 10 }, count: 9928 }
{ _id: { month: 9 }, count: 9779 }
{ _id: { month: 3 }, count: 10201 }
{ _id: { month: 8 }, count: 10194 }

$mod Use modulo operator

--查询total属性后面是00结尾的订单
db.orders.aggregate([
    {$match:{total:{$mod:[100,0]}}}
 ]);
--结果
{ _id: ObjectId("5dbe7a575368f69de2b4d4db"),
  street: '5929 Elroy Points',
  city: 'Retaberg',
  state: 'Utah',
  country: 'Cote d\'Ivoire',
  zip: '73722-0034',
  phone: '113.509.1520',
  name: 'Sanford Runte',
  userId: 7843,
  orderDate: 2019-02-21T20:26:32.458Z,
  status: 'completed',
  shippingFee: Decimal128("7.00"),
  orderLines: 
   [ { product: 'Fantastic Steel Shoes',
       sku: '1374',
       qty: 82,
       price: Decimal128("15.00"),
       cost: Decimal128("13.35") },
     { product: 'Sleek Frozen Salad',
       sku: '2698',
       qty: 79,
       price: Decimal128("41.00"),
       cost: Decimal128("33.21") },
     { product: 'Intelligent Granite Mouse',
       sku: '17',
       qty: 55,
       price: Decimal128("54.00"),
       cost: Decimal128("50.76") },
     { product: 'Handcrafted Wooden Chicken',
       sku: '2079',
       qty: 4,
       price: Decimal128("17.00"),
       cost: Decimal128("17") } ],
  total: Decimal128("500") }
{ _id: ObjectId("5dbe7a575368f69de2b4d50c"),
  street: '6159 Vandervort Camp',
  city: 'South Bobby',
  state: 'Montana',
  country: 'Guernsey',
  zip: '55141',
  phone: '173.672.8440 x661',
  name: 'Jovan Rice',
  userId: 3526,
  orderDate: 2019-09-14T21:05:45.049Z,
  status: 'shipping',
  shippingFee: Decimal128("9.00"),
  orderLines: 
   [ { product: 'Small Metal Sausages',
       sku: '8130',
       qty: 11,
       price: Decimal128("80.00"),
       cost: Decimal128("67.2") },
     { product: 'Intelligent Rubber Chicken',
       sku: '3775',
       qty: 61,
       price: Decimal128("10.00"),
       cost: Decimal128("8") },
     { product: 'Generic Rubber Table',
       sku: '7102',
       qty: 36,
       price: Decimal128("10.00"),
       cost: Decimal128("8.5") } ],
  total: Decimal128("100") }
......

$regex uses regular expression matching

--以184开头的手机号的订单数量
db.orders.aggregate([
    {$match:{ phone: { $regex: /^184/ }}},
    {$count:"counts"}
 ]);
--结果
{"counts": 55}

$unwind

Split the array into separate documents

Format

{
  $unwind:
  {
      path: <field path>,
      includeArrayIndex: <string>,  
      preserveNullAndEmptyArrays: <boolean> 
  } 
}

includeArrayIndex : Optional, the name of a new field is used to store the array index of the element. The name cannot start with $.

preserveNullAndEmptyArrays : Optional, the default is false, if it is true, if the path has no corresponding field or the corresponding array size is 0, then $unwind output the document, and the default false will not output.

--筛选一条数据，将数组拆分
db.orders.aggregate([
    {$match:{_id:ObjectId("5dbe7aa650fc769de3e1b551")}},
    {$unwind:"$orderLines"},
 ]);
--结果
{ _id: ObjectId("5dbe7aa650fc769de3e1b551"),
  street: '3340 Marielle Manors',
  city: 'New Maymie',
  state: 'Connecticut',
  country: 'Malawi',
  zip: '22434-3104',
  phone: '184.544.4826 x4858',
  name: 'Annette Langworth',
  userId: 9830,
  orderDate: 2019-01-23T11:56:14.972Z,
  status: 'shipping',
  shippingFee: Decimal128("8.00"),
  orderLines: 
   { product: 'Sleek Granite Gloves',
     sku: '6176',
     qty: 31,
     price: Decimal128("74.00"),
     cost: Decimal128("71.04") },
  total: Decimal128("313") }
{ _id: ObjectId("5dbe7aa650fc769de3e1b551"),
  street: '3340 Marielle Manors',
  city: 'New Maymie',
  state: 'Connecticut',
  country: 'Malawi',
  zip: '22434-3104',
  phone: '184.544.4826 x4858',
  name: 'Annette Langworth',
  userId: 9830,
  orderDate: 2019-01-23T11:56:14.972Z,
  status: 'shipping',
  shippingFee: Decimal128("8.00"),
  orderLines: 
   { product: 'Licensed Soft Cheese',
     sku: '2702',
     qty: 70,
     price: Decimal128("55.00"),
     cost: Decimal128("53.9") },
  total: Decimal128("313") }
......

$project

Specify desired and undesired fields from the document

Format

{ $project: { <specification(s)> } }

specifications有以下形式：

<field>: <1 or true> 包含该字段
<field>: <0 or false> 不包含该字段

_id: <0 or false>        是否指定_id字段

--如果有一个属性为或几个属性为1，那么只显示这一个或几个属性 + _id
db.orders.aggregate({
    $project:{name:1}
})
--结果
{ _id: ObjectId("5dbe7a545368f69de2b4d36e"), name: 'Destinee Schneider' }
{ _id: ObjectId("5dbe7a545368f69de2b4d36f"), name: 'Ashlynn Sipes' }
{ _id: ObjectId("5dbe7a54cd023b9de4efc1cc"), name: 'Genoveva Bauch' }
{ _id: ObjectId("5dbe7a542411dc9de6429190"), name: 'Kris Hansen' }

--如果有一个属性为或几个属性为0，那么显示除了这个一个或几个属性的其它所有属性
db.orders.aggregate({
    $project:{orderLines:0}
})
--结果
{
    "_id": {"$oid": "5dbe7a545368f69de2b4d36e"},
    "city": "Champlinberg",
    "country": "Malaysia",
    "name": "Destinee Schneider",
    "orderDate": {"$date": "2019-03-26T03:20:08.805Z"},
    "phone": "425.956.7743 x4621",
    "shippingFee": {"$numberDecimal": 8.00},
    "state": "Texas",
    "status": "created",
    "street": "493 Hilll Curve",
    "total": {"$numberDecimal": 407},
    "userId": 3573,
    "zip": "24344-1715"
},
{
    "_id": {"$oid": "5dbe7a545368f69de2b4d36f"},
    "city": "Linwoodburgh",
    "country": "United States of America",
    "name": "Ashlynn Sipes",
    "orderDate": {"$date": "2019-07-18T07:21:53.530Z"},
    "phone": "508.326.5494 x1218",
    "shippingFee": {"$numberDecimal": 7.00},
    "state": "Indiana",
    "status": "shipping",
    "street": "39476 Lacey Harbor",
    "total": {"$numberDecimal": 439},
    "userId": 2500,
    "zip": "84551"
}
......

--只展示嵌套属性
db.orders.aggregate({
    $project:{"orderLines.price":1}
})
或者
db.orders.aggregate({
    $project:{orderLines:{price:1}}
})
--结果
{ _id: ObjectId("5dbe7a542411dc9de6429193"),
  orderLines: 
   [ { price: Decimal128("75.00") },
     { price: Decimal128("64.00") },
     { price: Decimal128("34.00") },
     { price: Decimal128("98.00") },
     { price: Decimal128("88.00") },
     { price: Decimal128("20.00") },
     { price: Decimal128("59.00") },
     { price: Decimal128("20.00") },
     { price: Decimal128("90.00") },
     { price: Decimal128("45.00") },
     { price: Decimal128("42.00") },
     { price: Decimal128("28.00") } ] }
{ _id: ObjectId("5dbe7a5450fc769de3e19d20"),
  orderLines: 
   [ { price: Decimal128("51.00") },
     { price: Decimal128("10.00") },
     { price: Decimal128("63.00") },
     { price: Decimal128("12.00") },
     { price: Decimal128("37.00") },
     { price: Decimal128("43.00") },
     { price: Decimal128("39.00") },
     { price: Decimal128("68.00") },
     { price: Decimal128("21.00") } ] }
......

$cond - if-then-else use equivalent SQL in case-when-then-else

$$REMOVE removes this attribute when this condition is met

--不是7月的文档，移除这个属性
db.orders.aggregate({
    $project:{
        name:1,
        orderDate:{
            $cond: {
                   if: { $ne: [ {"$month":"$orderDate"}, 7 ] },
                   then: "$$REMOVE",
                   else: "$orderDate"
            }
        }
    }
})
--结果
{ _id: ObjectId("5dbe7a545368f69de2b4d36e"), name: 'Destinee Schneider' }
{ _id: ObjectId("5dbe7a545368f69de2b4d36f"), name: 'Ashlynn Sipes', orderDate: 2019-07-18T07:21:53.530Z }
{ _id: ObjectId("5dbe7a54cd023b9de4efc1cc"), name: 'Genoveva Bauch' }
{ _id: ObjectId("5dbe7a542411dc9de6429190"), name: 'Kris Hansen' }

Mapping to a property contains multiple properties

--使用substr截取第一个字母，使用strLenCP取name的长度
db.orders.aggregate({
    $project:{
        _id: 0,
        nm:{
            name:"$name",
            firstLetter:{$substr:["$name",0,1]},
            nameLenth:{$strLenCP:"$name"}
        }
    }
})
--结果
{ nm: { name: 'Destinee Schneider', firstLetter: 'D', nameLenth: 18 } }
{ nm: { name: 'Ashlynn Sipes', firstLetter: 'A', nameLenth: 13 } }
{ nm: { name: 'Genoveva Bauch', firstLetter: 'G', nameLenth: 14 } }
{ nm: { name: 'Kris Hansen', firstLetter: 'K', nameLenth: 11 } }
{ nm: { name: 'Dudley Kertzmann', firstLetter: 'D', nameLenth: 16 } }
......

Map the values of multiple attributes to an array

db.orders.aggregate({
    $project:{
        _id: 0,
        msg:[
            "$name","$orderDate","$orderLines.price"
        ]
    }
})
--结果
{msg: 
   [ 
     'Gudrun Stamm',
     2019-09-10T01:00:00.679Z,
     [ 
       Decimal128("17.00"),
       Decimal128("91.00"),
       Decimal128("51.00"),
       Decimal128("10.00"),
       Decimal128("18.00"),
       Decimal128("46.00"),
       Decimal128("69.00"),
       Decimal128("18.00"),
       Decimal128("89.00"),
       Decimal128("99.00") 
    ] 
  ] 
}
{ msg: 
   [ 
     'Jalon Erdman',
     2019-03-06T08:30:55.042Z,
     [ 
       Decimal128("37.00"),
       Decimal128("91.00"),
       Decimal128("88.00"),
       Decimal128("20.00"),
       Decimal128("75.00"),
       Decimal128("46.00") 
    ] 
  ] 
}
{ msg: 
   [ 
     'Mossie Ankunding',
     2019-05-25T09:40:13.662Z,
     [ 
       Decimal128("14.00"),
       Decimal128("49.00"),
       Decimal128("38.00"),
       Decimal128("55.00"),
       Decimal128("20.00") 
     ] 
   ] 
}
{ msg: 
   [ 
     'Jorge Toy',
     2019-09-28T23:07:35.137Z,
     [ 
       Decimal128("71.00"),
       Decimal128("62.00"),
       Decimal128("59.00"),
       Decimal128("43.00"),
       Decimal128("55.00"),
       Decimal128("65.00"),
       Decimal128("57.00") 
     ] 
   ] 
}
......

$limit

Limit the number of items, get the first n items of data

db.orders.aggregate({
    $limit:2
})
--结果
{ _id: ObjectId("5dbe7a545368f69de2b4d36e"),
  street: '493 Hilll Curve',
  city: 'Champlinberg',
  state: 'Texas',
  country: 'Malaysia',
  zip: '24344-1715',
  phone: '425.956.7743 x4621',
  name: 'Destinee Schneider',
  userId: 3573,
  orderDate: 2019-03-26T03:20:08.805Z,
  status: 'created',
  shippingFee: Decimal128("8.00"),
  orderLines: 
   [ { product: 'Refined Fresh Tuna',
       sku: '2057',
       qty: 25,
       price: Decimal128("56.00"),
       cost: Decimal128("46.48") },
     { product: 'Intelligent Wooden Towels',
       sku: '5674',
       qty: 72,
       price: Decimal128("84.00"),
       cost: Decimal128("68.88") },
     { product: 'Refined Steel Bacon',
       sku: '5009',
       qty: 8,
       price: Decimal128("53.00"),
       cost: Decimal128("50.35") } ],
  total: Decimal128("407") }
{ _id: ObjectId("5dbe7a545368f69de2b4d36f"),
  street: '39476 Lacey Harbor',
  city: 'Linwoodburgh',
  state: 'Indiana',
  country: 'United States of America',
  zip: '84551',
  phone: '508.326.5494 x1218',
  name: 'Ashlynn Sipes',
  userId: 2500,
  orderDate: 2019-07-18T07:21:53.530Z,
  status: 'shipping',
  shippingFee: Decimal128("7.00"),
  orderLines: 
   [ { product: 'Fantastic Soft Soap',
       sku: '6274',
       qty: 71,
       price: Decimal128("91.00"),
       cost: Decimal128("89.18") },
     { product: 'Intelligent Steel Chair',
       sku: '8278',
       qty: 13,
       price: Decimal128("67.00"),
       cost: Decimal128("62.31") },
     { product: 'Small Rubber Shoes',
       sku: '3534',
       qty: 60,
       price: Decimal128("76.00"),
       cost: Decimal128("71.44") } ],
  total: Decimal128("439") }
......

$skip

Skip the first n rows of data query

--查询第2、3条
db.orders.aggregate([{
        $skip:1
    },{
        $limit:2
}])
--结果
{ _id: ObjectId("5dbe7a545368f69de2b4d36f"),
  street: '39476 Lacey Harbor',
  city: 'Linwoodburgh',
  state: 'Indiana',
  country: 'United States of America',
  zip: '84551',
  phone: '508.326.5494 x1218',
  name: 'Ashlynn Sipes',
  userId: 2500,
  orderDate: 2019-07-18T07:21:53.530Z,
  status: 'shipping',
  shippingFee: Decimal128("7.00"),
  orderLines: 
   [ { product: 'Fantastic Soft Soap',
       sku: '6274',
       qty: 71,
       price: Decimal128("91.00"),
       cost: Decimal128("89.18") },
     { product: 'Incredible Concrete Chips',
       sku: '3756',
       qty: 6,
       price: Decimal128("18.00"),
       cost: Decimal128("15.12") },
     { product: 'Intelligent Steel Chair',
       sku: '8278',
       qty: 13,
       price: Decimal128("67.00"),
       cost: Decimal128("62.31") },
     { product: 'Small Rubber Shoes',
       sku: '3534',
       qty: 60,
       price: Decimal128("76.00"),
       cost: Decimal128("71.44") } ],
  total: Decimal128("439") }
{ _id: ObjectId("5dbe7a54cd023b9de4efc1cc"),
  street: '699 Harvey Row',
  city: 'Electamouth',
  state: 'South Dakota',
  country: 'Burundi',
  zip: '61826',
  phone: '[phone] x58095',
  name: 'Genoveva Bauch',
  userId: 8302,
  orderDate: 2019-03-15T13:53:48.925Z,
  status: 'shipping',
  shippingFee: Decimal128("5.00"),
  orderLines: 
   [ { product: 'Intelligent Soft Salad',
       sku: '3711',
       qty: 85,
       price: Decimal128("86.00"),
       cost: Decimal128("76.54") },
     { product: 'Generic Cotton Ball',
       sku: '2112',
       qty: 44,
       price: Decimal128("21.00"),
       cost: Decimal128("19.32") },
     { product: 'Rustic Plastic Keyboard',
       sku: '6451',
       qty: 19,
       price: Decimal128("81.00"),
       cost: Decimal128("77.76") } ],
  total: Decimal128("341") }

$sort

Sort documents in ascending order: 1 descending order: -1

--用名字排序
db.orders.aggregate([{
        $sort:{name:1}
    },{
        $project:{_id:0,name:1}
    }
])
--结果
{ name: 'Aaliyah Bruen' }
{ name: 'Aaliyah Erdman' }
{ name: 'Aaliyah Fahey' }
{ name: 'Aaliyah Gerhold' }
{ name: 'Aaliyah Graham' }
{ name: 'Aaliyah Greenfelder' }
{ name: 'Aaliyah Konopelski' }
{ name: 'Aaliyah Kovacek' }
{ name: 'Aaliyah Kuphal' }
{ name: 'Aaliyah Lueilwitz' }
{ name: 'Aaliyah Maggio' }
......

$sortByCount

Calculate the count value according to a field grouping and then sort it in descending order according to this value

db.orders.aggregate({
    $sortByCount:"$status"
})
--结果
{ _id: 'created', count: 20087 }
{ _id: 'shipping', count: 20017 }
{ _id: 'completed', count: 20015 }
{ _id: 'cancelled', count: 19978 }
{ _id: 'fulfilled', count: 19903 }