How to get record count in last X days in MongoDB-CodePudding

I have a database where I keep repair information, I need to list them using Chartjs. Simply, the number of repairs made in the last 7 days should be listed daily, and the days of no action are 0 by default.

Repairs Collection >> https://wetransfer.com/downloads/8829001b9825cd356247153fff96214e20221029092928/c82381 Example result >> data: [30, 40, 0, 80, 75, 0, 90]

CodePudding user response：

Okay, so as long as you save the repair date on the document, this can be easily done using an aggregation pipeline.

Solution

We will start by first matching all the relevant repairs, done in the dates we would like to filter out. This can be done using a $match stage:

{
  $match: {
    createdDate: {
      $gt: `Your start date`,
      $lt: `Your end date`
    }
  }
}

After we have the relevant repairs, we can then continue and group them by date and counting repairs by date. This can be done using a $group stage:

{
  $group: {
    _id: {$dateToString: {date: '$createdDate', format: '%Y-%m-%d'}},
    repairs: {$sum: 1}
  }
}

Now we have x documents (depending on the date range you need), looking like this:

{
  "_id": `date`,
  "repairs": `amount of repairs`
}

Example

Using the stages mentioned above, I created a pipeline that is outputting the amount of repairs for each day between 23.10.22 to 29.10.22.

[{
 $match: {
  createdDate: {
   $gt: new Date('2022-10-23T00:00:00.000Z'),
   $lt: new Date('2022-10-30T00:00:00.000Z')
  }
 }
}, {
 $group: {
  _id: {
   $dateToString: {
    date: '$createdDate',
    format: '%Y-%m-%d'
   }
  },
  repairs: {
   $sum: 1
  }
 }
}]

The following will return this:

[
  {
    "_id": "2022-10-23",
    "repairs": 10
  },
  {
    "_id": "2022-10-24",
    "repairs": 10
  },
  {
    "_id": "2022-10-25",
    "repairs": 10
  },
  {
    "_id": "2022-10-26",
    "repairs": 10
  },
  {
    "_id": "2022-10-27",
    "repairs": 10
  },
  {
    "_id": "2022-10-28",
    "repairs": 10
  },
  {
    "_id": "2022-10-29",
    "repairs": 10
  }
]

Sources

Edit

This can also be done using the $dateTrunc operator that was added on mongodb version 5.0.

The pipeline will look like this:

db.collection.aggregate([
  {
    $match: {
      createdDate: {
        $gt: new Date("2022-10-23T00:00:00.000Z"),
        $lt: new Date("2022-10-30T00:00:00.000Z")
      }
    }
  },
  {
    $group: {
      _id: {
        $dateTrunc: {
          date: "$createdDate",
          unit: "day"
        }
      },
      repairs: {
        $sum: 1
      }
    }
  }
])