CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-frameworkmongodb-java
fidz81
fidz81

Reputation: 3

Group based on discrete date ranges

I am new to MongoDB and I've been struggling to get a specific query to work without any luck. I have a collection with millions of documents having a date and an amount, I want to get the aggregations for specific periods of time. For example, I want to get the count, amount summations for the periods between 1/1/2015 - 15/1/2015 and between 1/2/2015 - 15/2/2015

A sample collection is

{  "_id" : "148404972864202083547392254",  "account" : "3600",   "amount" : 50, "date" : ISODate("2017-01-01T12:02:08.642Z")}

{  "_id" : "148404972864202085437392254",  "account" : "3600",   "amount" : 50, "date" : ISODate("2017-01-03T12:02:08.642Z")}

{  "_id" : "148404372864202083547392254",  "account" : "3600",   "amount" : 70, "date" : ISODate("2017-01-09T12:02:08.642Z")}

{  "_id" : "148404972864202083547342254",  "account" : "3600",   "amount" : 150, "date" : ISODate("2017-01-22T12:02:08.642Z")}

{  "_id" : "148404922864202083547392254",  "account" : "3600",   "amount" : 200, "date" : ISODate("2017-02-02T12:02:08.642Z")}

{  "_id" : "148404972155502083547392254",  "account" : "3600",   "amount" : 30, "date" : ISODate("2017-02-7T12:02:08.642Z")}

{  "_id" : "148404972864202122254732254",  "account" : "3600",   "amount" : 10, "date" : ISODate("2017-02-10T12:02:08.642Z")}

for date ranges between 1/1/2017 - 10/10/2017 and 1/2/2017 - 10/2/2017 the output would be like this:

Is it possible to work with such different date ranges? The code would be in Java, but as an example in mongo, can someone please help me?

Upvotes: 0

Views: 95

Answers (1)

AlexDenisov
AlexDenisov

Reputation: 4117

There must be a more elegant solution than this. Anyways you can wrap it into a function and generalize date related arguments.

First, you need to make a projection at the same time deciding into which range an item goes (note the huge $switch expression). By default, an item goes into 'null' range.

Then, you filter out results that didn't match your criteria (i.e. range != null).

The very last step is to group items by the range and make all needed calculations.

db.items.aggregate([
  { $project : {
    amount : true,
    account : true,
    date : true,
    range : {
      $switch : {
        branches : [
          {
            case : {
              $and : [
                { $gte : [ "$date", ISODate("2017-01-01T00:00:00.000Z") ] },
                { $lt : [ "$date", ISODate("2017-01-10T00:00:00.000Z") ] }
              ]
            },
            then : { $concat : [
              { $dateToString: { format: "%d/%m/%Y", date: ISODate("2017-01-01T00:00:00.000Z") } },
              { $literal : " - " },
              { $dateToString: { format: "%d/%m/%Y", date: ISODate("2017-01-10T00:00:00.000Z") } }
            ] }
          },
          {
            case : {
              $and : [
                { $gte : [ "$date", ISODate("2017-02-01T00:00:00.000Z") ] },
                { $lt : [ "$date", ISODate("2017-02-10T00:00:00.000Z") ] }
              ]
            },
            then : { $concat : [
              { $dateToString: { format: "%d/%m/%Y", date: ISODate("2017-02-01T00:00:00.000Z") } },
              { $literal : " - " },
              { $dateToString: { format: "%d/%m/%Y", date: ISODate("2017-02-10T00:00:00.000Z") } }
            ] }
          }
        ],
        default : null
      }
    }
  } },
  { $match : { range : { $ne : null } } },
  { $group : {
    _id : "$range",
    count : { $sum : 1 },
    "amount summation" : { $sum : "$amount" }
  } }
])

Based on your data it will give the following results*:

{ "_id" : "01/02/2017 - 10/02/2017", "count" : 2, "amount summation" : 230 }
{ "_id" : "01/01/2017 - 10/01/2017", "count" : 3, "amount summation" : 170 }

*I believe you have few typos in your questions, that's why the data look different.

Upvotes: 1

Related Questions