Categories
Development MongoDB

mongodb aggregations by date range, fill blank dates with data from previous documents

I have a dataset of records as following:

{
  "_id": 1,
  "itemId": "t1",
  "startDate": ISODate("2019-10-14T21:00:00.000+0000"),
  "endDate": ISODate("2019-10-16T21:00:00.000+0000"),
  "status": "TODO"
},
{
   "_id": 2,
   "itemId": "t1",
   "startDate": ISODate("2019-10-17T21:00:00.000+0000"),
   "endDate": null,
   "status": "DONE"
 }

where each document contains the current status of an item. if a document has an endDate – it means the item’s state was represented by this document until that date. if the endDate is null – it means the document still represents the item’s current state.

I would like to create an aggregation that will count the amount of items in each status, per day. for example, for the data above and the range 14.10.2019 – 19.10.2019 the result will be:

{
  "results":
   [
     {"date": ISODate("2019-10-14T21:00:00.000+0000"), "TODO": 1, "DOING": 0, "DONE": 0},
     {"date": ISODate("2019-10-15T21:00:00.000+0000"), "TODO": 1, "DOING": 0, "DONE": 0},
     {"date": ISODate("2019-10-16T21:00:00.000+0000"), "TODO": 1, "DOING": 0, "DONE": 0},
     {"date": ISODate("2019-10-17T21:00:00.000+0000"), "TODO": 0, "DOING": 1, "DONE": 0},
     {"date": ISODate("2019-10-18T21:00:00.000+0000"), "TODO": 0, "DOING": 1, "DONE": 0},
     {"date": ISODate("2019-10-19T21:00:00.000+0000"), "TODO": 0, "DOING": 1, "DONE": 0}
   ]
 }

I’m looking for a way to map the data, and for each document run the logic of:

 if (startDate <= currentDate) && (endDate == null || endDate > currentDate) -> add 1 to currentDate's status count for this day and status.

Thanks in advance!

Leave an answer

Your email address will not be published. Required fields are marked *