Project can be download from
MongoDB Aggregation
This groups values from multiple documents together and can perform variety of operations on that grouped data.it is going to return single result after the aggregation.
Stages In Aggregation:
Those operation are used in group of documents (Used inside aggregation stages).
Unary Operation - Unary operator perform operation for each documents.
Type - Given the type of the filed.
In the SQL we query select count and in the bracket Astrix this count for aggregation. It return the number of rows. This is same in mongoDB.
db.employee.aggregate([
{ $group: { _id: "$gender", genderCount: { $sum: 1 } } }
]) // $sum: 1 :1 - true
db.employee.aggregate([
{ $group: { _id: "$gender", maxAge: { $max: "$age" } } }
])
Bellow example is from https://docs.mongodb.com/manual/aggregation/
Single Purpose Aggregation Operations
db.employee.estimatedDocumentCount()
db.employee.count()
db.employee.distinct("salary")
Stages In Aggregation Example Query:
01.$match and $group
_id is mandatory and group by done by it.
When we place $match after the $group, Then it should be given as bellow image. But to get better performance match should be used first place.
02.Projection - $project.
Selecting only necessary data rather than selection the all data from a document.
These can be divided in to 2 types.
Type One:
// Syntax - db.COLLECTION.remove({},{KEY:1 OR 0})
// first one should be empty {}. 1 - we want that data , 0 - we do not want that data
db.employee.find({},{"age":1,"_id":0})
Type Two:
03.$count
04.$sort
1 - ascending , -1 descending.
Sort by age and if there are multiple results with same age, then those result will be sort by county.
05.$limit, $skip, $sort
// employee collection එකෙන් document 2k පමණක් fetch කරන්න
db.employee.find({},{"age":1,"_id":0,"code":1, "addrss":1,"salary":1}).limit(2)
// employee collection එකෙන් document වල පළමු 2k අත්හැර ඉතුරු ටික පමණක් fetch කරන්න db.employee.find({},{"age":1,"_id":0,"code":1, "addrss":1,"salary":1}).skip(2)
db.employee.find({},{"age":1,"_id":0,"code":1, "addrss":1,"salary":1}).limit(2).skip(1)
// Both are same.පළමුවෙනි document එක අතහැර ඊලග document දෙක ගන්න
db.employee.find({},{"age":1,"_id":0,"code":1, "addrss":1,"salary":1}).skip(1).limit(2)
// get document according acceding order of salary.[23000,290000,33000]db.employee.find({},{"age":1,"_id":0,"code":1, "addrss":1,"salary":1}).sort({"salary":1})
// get document according descending order of salary.[33000,290000,23000]
db.employee.find({},{"age":1,"_id":0,"code":1, "addrss":1,"salary":1}).sort({"salary":-1})
06.$unwind
07.$out
Example 1: Bellow both queries are same.
db.student.find({array:{$size:3}});
db.student.aggregate({$match:{array:{$size:3}}})
Example 2:
db.transaction.aggregate([
{
$match: {
"clientId":{$exists: true,$eq: "080c99df-22c4-4744-86e4-bfdfde2a5794"},
"checks.customer.incentivioId":{$exists: true},
"businessDate":{$gt: new Date('2019-07-08 00:00:00.000Z'),$lt: new Date('2023-07-08 00:00:00.000Z')}
}
},
{
$group: {
_id: "$checks.customer.incentivioId",
noOfTransactions: { $sum: 1 },
totalBasketValue: { $sum: "$checks.total.total" },
averageBasketValue: { $avg: "$checks.total.total" },
lastPurchaseDate: { $max: "$businessDate" },
firstPurchaseDate: { $min: "$businessDate" },
clientId: { $first: "clientId" },
uniqueExtTxIdSet: { $addToSet: "extTxId" }
}
},
{ $limit: 100 },
{ $sort: {noOfTransactions: 1,totalBasketValue:-1}},
{
$project: {
_id: 0,
info:{
noOfTransactions:"noOfTransactions",
averageBasketValue:"averageBasketValue"
}
}
},
{ $skip: 10 }
])
Comments
Post a Comment