MongoDB: Count documents created, for each day in the past X days, using _id

Question

I am looking to gather sign up stats about users on my website--a signup count by date for the past X days via the mongo shell. The results could look something like:

Dec 7, 2019: 100
Dec 6, 2019: 150
Dec 5, 2019: 150
...

The documents do not have any created_at property, but there is an _id property which I believe can be used to achieve this goal?

If that can help be more clear, the equivalent in MySQL would be something like SELECT count(id) FROM Users WHERE created_at > timestamp_X_days_ago GROUP BY created_at ORDER BY DESC created_at

The name of the collection is Users.

I tried many queries, and the one I believe to be the closest with is db.Users.find({ "_id" : {$lt: new Date(), $gte: new Date(new Date().setDate(new Date().getDate()-1))}}).count(), if only _id I figured out how to cast a date on _id.

Mongo 3.6

see this.. https://stackoverflow.com/questions/22516514/mongodb-return-the-count-of-documents-for-each-day-for-the-last-one-month — Jabongg, Dec 07 '19 at 16:34
Doesn't help, I don't have any other date fields besides what's available in `_id` — Dan P., Dec 07 '19 at 16:56
https://stackoverflow.com/questions/15572607/find-all-documents-within-last-n-days this question is asked before. — sachin verma, Dec 16 '19 at 08:51

prasad_ · Answer 1 · 2019-12-10T14:39:34.840

Assuming the _id field is of type ObjectId, the following query prints the counts by day, in descending order.

The initial match stage will filter documents by "last X days". For example, to process only the past 10 days documents from today, get the past_x_days in milliseconds and use it in the query:

var past_x_days = 10 * 86400000; // where 86400000 is millis per day (24*60*60*1000)

db.test.aggregate( [
  { 
      $match: { 
          $expr: { 
              $gt: [ { $toDate: "$_id" },  { $toDate: { $subtract: [ ISODate(), past_x_days ] } } ]  
          } 
      } 
  },
  { 
      $group: { 
          _id: { dateYMD: { 
             $dateFromParts : {
                 year: { $year: "$_id" }, 
                 month: { $month: "$_id" }, 
                 day: { $dayOfMonth: "$_id" }
             }
         } }, 
         count: { $sum: 1 } 
       } 
  },
  { 
      $sort: { "_id.dateYMD" : -1 } 
  },
  { 
      $project: { 
          _id: 0, 
          count: 1, 
          dateDMY: { $dateToString: { date: "$_id.dateYMD", format: "%d-%m-%Y" } } 
      } 
  }
] )

The output will look like this:

{ "count" : 2, "dateDMY" : "09-12-2019" }
{ "count" : 3, "dateDMY" : "01-12-2019" }

NOTE: The above query works with MongoDB version 4.0. The query is modified to work with version 3.6:

db.test.aggregate( [
  { 
      $addFields: { 
          oid_date: { $dateToParts: { date: "$_id" } }, 
          dt_diff: { $subtract: [ ISODate(), past_x_days ] }  
      }
  },
  { 
      $addFields: { 
          oid_dt_ymd: { 
              $dateFromParts: { 
                  year : "$oid_date.year", 
                  month : "$oid_date.month", 
                  day: "$oid_date.day" 
              } 
          }
      }
  },
  { 
      $match: { 
          $expr: { 
              $gt: [ "$oid_dt_ymd",  "$dt_diff"  ]  
          } 
      } 
  },
  { 
      $group: { 
          _id: "$oid_dt_ymd", 
          count: { $sum: 1 } 
       } 
  },
  { 
      $sort: { "_id" : -1 } 
  },
  { 
      $project: { 
          _id: 0, 
          count: 1, 
          dateDMY: { $dateToString: { date: "$_id", format: "%d-%m-%Y" } } 
      } 
  }
])

The following are the ObjectIds are input (shown here with their corresponding date values).

ObjectId(), Dec 10 2019
ObjectId(), Dec 10 2019
ObjectId("5c6b8f57f3558c2685b0d4e3"), Feb 19 2019
ObjectId("5c6b95a7f3558c2685b0d4e4"), Feb 19 2019
ObjectId("5c6b95a7f3558c2685b0d4e5"), Feb 19 2019
ObjectId("5dd525c60fd48753f98ea39b"), Nov 20 2019
ObjectId("5dd525c60fd48753f98ea39c"), Nov 20 2019
ObjectId("5dd525c60fd48753f98ea3a1"), Nov 20 2019
ObjectId("5dd60eaeae3321b020320583"), Nov 21 2019

And, the variable past_x_days = 30 * 86400000. The query returns:

{ "count" : 2, "dateDMY" : "10-12-2019" }
{ "count" : 1, "dateDMY" : "21-11-2019" }
{ "count" : 3, "dateDMY" : "20-11-2019" }

Thank you. We're using Mongo 3, so `$toDate` does not work. Checking right now for an alternative in Mongo 3. — Dan P., Dec 09 '19 at 13:35

s7vr · Answer 2 · 2019-12-11T13:29:40.170

0

You could use below aggregation query in mongo shell for efficient search. First perform the match followed by transformation to date for grouping.

var lastWeek = new Date();
lastWeek.setDate(lastWeek.getDate() -7);
var lastWeekObjectId = ObjectId.fromDate(lastWeek);


db.Users.aggregate(
{$match:{_id:{$gt:lastWeekObjectId}}},
{$addFields:{_id:0,roundDate:{$dateFromParts:{
     year:{$year:"$_id"},
     month:{$month:"$_id"},
     day:{$dayOfMonth:"$_id"}
}}}},
{$sortByCount:{$dateToString:{date:"$roundDate",format:"%m-%d-%Y"}}}
)

edited Dec 11 '19 at 13:29

answered Dec 10 '19 at 14:56

s7vr

73,656
11
106
127

liked the `$sortByCount` stage, never noticed it existed... – Chai Halfon Dec 13 '19 at 23:12

Chai Halfon · Answer 3 · 2019-12-13T23:02:56.923

Since mongo ObjectId holds a timestamp inside it, you can use this method to create new ObjectIds with the timestamp you want to compare to as boundaries, lower and upper:

db.Users.aggregate(
  [
    {
      $match: {
        _id: {
          $gt: ObjectId(Math.floor((new Date('2019-08-28T22:55:00Z')) / 1000).toString(16) + "0000000000000000"),
          $lt: ObjectId(Math.floor((new Date('2019-11-28T22:56:00Z')) / 1000).toString(16) + "0000000000000000")
        }
      }
    },
    {
      $project: {
        date: {$dateToString: {format: '%Y-%m-%d', date: '$_id'}}
      },
    },
    {
      $group: {
        _id: "$date",
        count: {$sum: 1}
      }
    },
    {
      $sort: {
        _id: 1
      }
    }
  ]
)

just change the dates to the desired dates you would like to query on..

So the match query filter by dates upon the _id of the object, then when we convert the ObjectIds we got to string, group by that day string, count, and sort :)

MongoDB: Count documents created, for each day in the past X days, using _id

3 Answers3