Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MongoDB group aggregation with conditions

Tags:

mongodb

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.

like image 664
user3833083 Avatar asked Apr 26 '17 11:04

user3833083


People also ask

How do I write a group condition in MongoDB?

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.

How can you group by a particular value in MongoDB?

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.

What is _ID in Group 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.

How do I use distinct aggregation in MongoDB?

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.


1 Answers

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.

like image 111
buræquete Avatar answered Oct 12 '22 04:10

buræquete