Menu
I've the following sample collection:
{
"_id" : ObjectId("59007c230c16863f9ae8ea00"),
"user_id" : 1,
"transaction_time" : ISODate("2017-04-26T10:52:33.000Z"),
"type" : "data_plan",
"amount" : 540.0,
"updated_at" : ISODate("2017-04-26T10:53:23.389Z"),
"created_at" : ISODate("2017-04-26T10:53:23.389Z")
}
This is the equivalent of what I wanna do in SQL:
SELECT user_id, SUM(amount) as total_amount
FROM user_transactions
WHERE type = 'data_plan'
AND transaction_time BETWEEN '2017-04-14' AND '2017-04-20'
GROUP BY user_id
HAVING total_amount >= 2000
and this is my current query to perform the same operation;
db.user_transactions.aggregate([{
'$group': {
'_id': {
'user_id': '$user_id'
},
'amount': {
'$sum': '$amount'
},
'user_id': {
'$first': '$user_id'
}
}
},
{
'$match': {
'amount': {
'$gte': 2000
}
'type': {
'$eq': 'data_plan'
},
'transaction_time': {
$gte: ISODate("2017-04-14T00:00:00.000Z"),
$lt: ISODate("2017-04-20T00:00:00.000Z")
}
}
}
])
It is returning no result, but when I remove transaction_time and type from $match it does.
664
The use of the $cond operator here determines whether the "status" is actually a defect or not and the result is a conditional $sum where only the "defect" values are counted. Once those are grouped per day you simply $divide the result, with another check with $cond to make sure you are not dividing by zero.
We can group by single as well as multiple field from the collection, we can use $group operator in MongoDB to group fields from the collection and returns the new document as result. We are using $avg, $sum, $max, $min, $push, $last, $first and $addToSet operator with group by in MongoDB.
The _id expression specifies the group key. If you specify an _id value of null, or any other constant value, the $group stage returns a single document that aggregates values across all of the input documents.
You can use $addToSet with the aggregation framework to count distinct objects. Not a generic solution, if you have a large number of unique zip codes per result, this array would be very large. The question was to get the city with MOST zip codes for each state, not to get the actual zip codes.
I think I got it;
db.user_transactions.aggregate([{
$match: {
type: {
$eq: "data_plan"
},
transaction_time: {
$gte: ISODate("2017-04-14T00:00:00.000Z"),
$lt: ISODate("2017-04-20T00:00:00.000Z")
}
}
}, {
$group: {
_id: "$user_id",
amount: {
$sum: "$amount"
},
user_id: {
$first: "$user_id"
}
}
}, {
$match: {
amount: {
$gte: 2000
}
}
}])
The problem with your query is that, you were trying to do $match logic all at once at the end of the $group stage, but the fields type and transaction_time are not there after grouping, therefore I moved them before the grouping, and it worked. Tested on Online MongoDB Shell.
If you have trouble with aggregation, since it is an array of operations, which are creating a pipeline, it is better to test each operation by itself, just checking the $group operations result was enough to resolve your issue.
111
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
