MongoDB group aggregation with conditions

MongoDB group aggregation with conditions

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.

Solutions/Answers:

Solution 1:

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.

Related:  Serialize to object using scala mongo driver?

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.

References