CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Dan P.
Dan P.

Reputation: 1775

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

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

Upvotes: 2

Views: 5352

Answers (3)

Chai Halfon
Chai Halfon

Reputation: 899

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 :)

Upvotes: 0

s7vr
s7vr

Reputation: 75964

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"}}}
)

Upvotes: 0

prasad_
prasad_

Reputation: 14317

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" }

Upvotes: 3

Related Questions