CODEWITHSUNDEEP
mongodbaggregation-framework
brianghig
brianghig

Reputation: 197

MongoDB Aggregation Facet to Bucket by Days in a Date Range

How can I bucket documents in MongoDB 3.4.1 with two fields representing a date range into the days that are covered by that range?

For example, I have the following documents:

{ start: ISODate('2017-04-01T12:30:00Z'), end: ISODate('2017-04-02T12:30:00Z') },
{ start: ISODate('2017-04-04T12:00:00Z'), end: ISODate('2017-04-04T12:30:00Z') },
{ start: ISODate('2017-04-04T12:30:00Z'), end: ISODate('2017-04-08T12:30:00Z') },
{ start: ISODate('2017-04-05T12:30:00Z'), end: ISODate('2017-04-06T12:30:00Z') },
{ start: ISODate('2017-04-08T12:30:00Z'), end: ISODate('2017-04-09T12:30:00Z') }

and I'm looking to bucket those into the following histogram:

[
  { '2017-04-01': 1 },
  { '2017-04-02': 1 },
  { '2017-04-04': 2 },
  { '2017-04-05': 2 },
  { '2017-04-06': 2 },
  { '2017-04-07': 1 },
  { '2017-04-08': 2 },
  { '2017-04-09': 1 }
]

If a document's date range covers any portion of a day, the document should count for 1 item on that day.

I'd like to perform this bucketing with a single aggregation pipeline, if possible. This is fairly straightforward with individual timestamp fields by projecting them with a day formatter such as:

{ $project: { day: { $dateToString: { format: '%Y-%m-%d', date: '$start' } } } }

but it does not seem as those there are aggregation operators that support this for a date range.

Upvotes: 1

Views: 2790

Answers (1)

Charlie Swanson
Charlie Swanson

Reputation: 406

Not very simple, but this worked. If you want your exact output format, you'll need the new $objectToArray expression introduced in 3.4.4.

edit: this does not take leap years into account, since it assumes each year has 365 days.

const millisPerDay = 1000 * 60 * 60 * 24;

var results = db.dates.aggregate([
    {
      $project: {
          start: 1,
          range: {
              $range: [
                  0,
                  {
                    $add: [
                        1,
                        {
                          $add: [
                              {$multiply: [365, {$subtract: [{$year: "$end"}, {$year: "$start"}]}]},
                              {$subtract: [{$dayOfYear: "$end"}, {$dayOfYear: "$start"}]}
                          ]
                        }
                    ]
                  }
              ]
          }
      }
    },
    {
      $project: {
          daysOfEvent: {
              $map: {
                  input: "$range",
                  in : {$add: ["$start", {$multiply: ["$$this", millisPerDay]}]}
              }
          }
      }
    },
    {$unwind: "$daysOfEvent"},
    {$group: {_id: {$dateToString: {format: '%Y-%m-%d', date: "$daysOfEvent"}}, count: {$sum: 1}}},
    {$sort: {_id: -1}},
    {$replaceRoot: {newRoot: {$arrayToObject: [[{k: "$_id", v: "$count"}]]}}}
]);

Upvotes: 4

Related Questions