CODEWITHSUNDEEP
mongodbmongodb-query
jgrogan
jgrogan

Reputation: 13

mongodb - want to sum values over time

I have a bunch of documents in Mongo that represent sales transactions - 1 document per transaction. Within each document, I have the value of the transaction and the date&time. I want to run a query that sums up the total value of all transactions for each day.

I have looked at the aggregation framework, but I can't find any examples of using a timestamp/date to do the filtering. Can anyone give me some pointers on how to do this?

Upvotes: 1

Views: 1913

Answers (1)

yellowB
yellowB

Reputation: 3020

The aggregation framework is the right direction.

Before using aggregation, let's prepare some data:

var data = [
    {
        value: 10,
        dateTime: ISODate("2016-06-01T06:00:00.000Z")
    },{
        value: 20,
        dateTime: ISODate("2016-06-01T08:00:00.000Z")
    },{
        value: 30,
        dateTime: ISODate("2016-06-02T06:00:00.000Z")
    },{
        value: 40,
        dateTime: ISODate("2016-06-02T08:00:00.000Z")
    },{
        value: 50,
        dateTime: ISODate("2016-06-03T06:00:00.000Z")
    },{
        value: 60,
        dateTime: ISODate("2016-06-03T08:00:00.000Z")
    },
];

db.saleTransaction.insert(data);

We have 6 sales transaction records now, 2 records for each day. The 'value' field is a number, 'dateTime' field is a Date.

The Date field can be compare by $gt/$lt/... operators, so if you want data for 2016-06-01~2016-06-02, the query is like this:

{ dateTime: 
        { $gte: ISODate("2016-06-01T00:00:00.000Z"), 
          $lt: ISODate("2016-06-03T00:00:00.000Z")}}
}

Then you can use $match operator to filter them before summarize, For example if you want to know the total value by each day on 2016-06-01~2016-06-02, you can use this query:

db.saleTransaction.aggregate(
[
    { $match: { dateTime: 
        { $gte: ISODate("2016-06-01T00:00:00.000Z"), $lt: ISODate("2016-06-03T00:00:00.000Z")}}
    },
    {$group: { _id : {
            year:{$year:"$dateTime"},
            month:{$month:"$dateTime"},
            day:{$dayOfMonth:"$dateTime"}
        },
        count:{$sum: "$value" }
    }
}
]);

The result is like this:

{ "_id" : { "year" : 2016, "month" : 6, "day" : 2 }, "count" : 70 }
{ "_id" : { "year" : 2016, "month" : 6, "day" : 1 }, "count" : 30 }

Upvotes: 3

Related Questions