CODEWITHSUNDEEP
mongodb
user3833083
user3833083

Reputation: 305

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.

Upvotes: 9

Views: 9936

Answers (1)

buræquete
buræquete

Reputation: 14678

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.

Upvotes: 9

Related Questions