Aggregation function
Aggregate
The pipe
expression
The pipe
Aggregation function
expression
Db. COLLECTION_NAME. Aggregate (OPERATION, the CALLBACK)Copy the code
The pipe
$project: Modifies the structure of the input document. It can be used to rename, add, or delete domains, create computed results, and nest documents. The correspondingproject()methods$match: Filters data and outputs only the documents that meet the conditions. $match uses MongoDB’s standard query operations. The correspondingmatch().$limit: limits the number of documents returned by the MongoDB aggregation pipeline. The correspondinglimit()methods$skip: Skips a specified number of documents in the aggregation pipe and returns the remaining documents. The correspondingskip().$unwind: Splits an array type field in a document into multiple fields, each containing a value from the array. The correspondingunwind()methods$group: Groups documents in a collection that can be used for statistical results. The correspondinggroup()methods$sort: Sorts the input documents and outputs them. The correspondingsort()methods$geoNear: Outputs ordered documents close to a geographic location. The correspondingnear().
v3.2
$sample: Select N randomly$lookupThe populate operator connects to another collection in the same database and obtains the specified document, similar to the populate operator
Docs.mongodb.com/manual/refe…
$group
$sumCalculate the sum.$avgCalculate the average$minGets the minimum value corresponding to all documents in the collection.$maxGets the maximum value corresponding to all documents in the collection.$pushInserts values into an array in the resulting document.$addToSetInserts values into an array in the resulting document, but does not create a copy.$firstGet the first document data according to the ordering of the resource documents.$lastGets the last document data according to the ordering of the resource documents
Docs.mongodb.com/manual/meta…
mongodb-pratice
citys
{province: 'guangdong', city: 'guangzhou', person: 600, industry: [{name: 'IT', person: 200}, {name: 'teacher', person: 400}]}
{province: 'guangdong', city: 'shenzhen', person: 700, industry: [{name: 'IT', person: 300}, {name: 'teacher', person: 400}]}
{province: 'beijing', city: 'beijing', person: 600, industry: [{name: 'IT', person: 350}, {name: 'teacher', person: 250}]}
Copy the code
Instance (1)
// modules/citys/citys.controller.js exports.getCityGtThousand = (req, res) => { CityModel.aggregate([ {$group: {_id: '$province', total: {$sum: '$person'}}}, {$match: {total: {$gt: 1000}}} ], (err, result) => { ... })}Copy the code $province
province
Example (2)
$group
_id
The results are as follows:
[{"_id":"guangdong","total":1300}]
Copy the code
SELECT province, SUM(person) AS total FROM citys GROUP BY province HAVING total > 1000
Copy the code
In fact, the above query can be divided into two steps (two pipes) :
1
province
_id
CityModel.aggregate([
{$group: {_id: '$province', total: {$sum: '$person'}}}
])
Copy the codeThe result is:
[{"_id":"beijing","total":600},{"_id":"guangdong","total":1300}]
Copy the code
2
CityModel.aggregate([
{$group: {_id: '$province', total: {$sum: '$person'}}},
{$match: {total: {$gt: 1000}}}
])
Copy the code
[{"_id":"guangdong","total":1300}]
Copy the code
Example (2)
Instance (1)
1. Calculate the TOTAL IT population of each province
2. IT population is greater than 400
// modules/citys/citys.controller.js exports.getITPerson = (req, res) => { CityModel.aggregate([ {$unwind: '$industry'}, {$match: {'industry.name': 'IT'}}, { $group: { _id: {province: '$province'}, itTotal: {$sum: '$industry.person'}, city: {$push: '$city'} } }, {$match: {itTotal: {$gt: 400}}} ]).exec((err, result) => { ... })}Copy the code
(1)
$unwind
CityModel.aggregate([
{$unwind: '$industry'}
])
Copy the code
[
{
"_id": "5a0e83e3aea7e7fba7ff0ab2",
"province": "guangdong",
"city": "guangzhou",
"person": 600,
"industry": {
"name": "IT",
"person": 200
}
},
{
"_id": "5a0e83e3aea7e7fba7ff0ab2",
"province": "guangdong",
"city": "guangzhou",
"person": 600,
"industry": {
"name": "teacher",
"person": 400
}
}
...
]
Copy the code industry
(2)
$match
{$match: {'industry.name': 'IT'}}
Copy the code
(3)
$group
$group: {
_id: {province: '$province'}, itTotal: {$sum: '$industry.person'}, city: {$push: '$city'}
}
Copy the code
(4)
{$match: {itTotal: {$gt: 400}}}
Copy the codeReturns data with a total IT population greater than 400.
Details of other pipe operators
(1)
Grammar:
{ $sort: { <field1>: <sort order>, <field2>: <sort order> ... }}Copy the code1 is positive, minus 1 is negative
Example: Sort by city population and return city/province/number of people:
// modules/citys/citys.controller.js
CityModel.aggregate([
{ $sort: {person: 1} },
{
$project: {
_id: 0,
province: 1,
person: 1,
city: 1
}
}
])
Copy the codeNote: Before Mongoose 3.4, only _id(displayed by default) can be specified as 0 or false. Other fields do not enter the next pipe by default, but can be set to 1 or true to enter the next pipe. (Version 3.4 can set other fields to 0 or false)
(2) Limit and skip
Grammar:
{ $limit: <positive integer> }
{ $skip: <positive integer> }
Copy the codeExample: Starting with the second data, return one piece of data:
// modules/citys/citys.controller.js
CityModel.aggregate([
{$skip: 1},
{$limit: 1}
])
Copy the code(3) randomly
Grammar:
{ $sample: { size: <positive integer> } }
Copy the codeExample: Return a random piece of data:
// modules/citys/citys.controller.js
CityModel.aggregate({$sample: {size: 1}})
Copy the codeNote: Only after Mongoose 3.2
(4) league table
In Mongoose: Progressions, we explained how the spreadsheet query went away. After Mongoose 3.2, we can connect tables more conveniently.
Grammar:
{
$lookup:
{
from: <collection to join>,
localField: <field from the input documents>,
foreignField: <field from the documents of the "from" collection>,
as: <output array field>
}
}
Copy the codeParameter Description:
- From Specifies the name of the collection to associate
- LocalField Specifies the field to look for in the collection
- ForeignField The field to be associated with in another collection
- As Specifies the output field name
The instance
We insert three pieces of data into the Users set:
{name: 'zhang SAN ', age: '22', sex: 'male', phone: '13123123123', address: {city:' Guangzhou '}}, {name: 'Li Si ', age: '19', sex: 'male', phone: '13123123123', address: {city: 'Beijing'}}, {name: 'wang 5', age: '25', sex: 'male', phone: '13123123123', address: {city:' Beijing '}}, {name: 'Wang 5', age: '25', sex: 'male', phone: '13123123123', address: { city: 'guangzhou' }}Copy the codeInsert three more data into the article:
{title: 'Mongoose development actual combat: basic article ', content: 'explain how to connect database, build documents, etc. ', author:' Zhang SAN '}, {title: 'Mongoose development actual combat: Advanced article ', content: 'Explain index building, add validator, etc. ', author:' Li Si '}, {title: 'Mongoose Development Practice: Advanced ', content: 'Explain aggregation function ', author:' Wang Wu '}Copy the codeNow to query the article published by Zhang SAN:
// modules/users/users.controller.js ... UsersModel.aggregate([ { $lookup: { from: 'articles', localField: 'name', foreignField: 'author', as: 'userArticle' } }, { $project: { _id: 0, name: 1, 'userArticle.title': 1, 'userArticle.author': 1 } }, { $match: {name: 'Zhang SAN '}}])... / / / {" name ":" zhang ", "userArticle" : [{" title ":" Mongoose development of actual combat: basis ", "author" : "* *"}]}]Copy the codeRelated articles:
- Mongoose Development: Basics
- Mongoose Development Practice – Advanced chapter
If you have any questions or questions, feel free to leave them in the comments section below!