MongoDB aggregate by date using _id

Question

What I'm trying to do should be relatively simple. My document is called "clicks", and looks like this:

{
     "_id":"5dcc53f30666eacfdb9d9f59"
,     "productID":"5dcc4c47a0d861ec6a0b432b"
,     "userIP":"XX.XX.XX"
}

I just want to get back the number of clicks per day, using _id and (I assume) GetTimestamp() to figure out the date:

[
{
     "date":"2019-11-01"
,    "clicks":"125"
}
,{
     "date":"2019-11-02"
,    "clicks":"153"
}]

I thought that I could do something like this, but it's not working:

db.clicks.aggregate([
    {"$group" : {_id: ObjectId(_id).getTimeStamp(), count:{$sum:1}}}
])

All the examples I'm seeing (eg: group by dates in mongodb ) have an element in the document containing a date element-- how do I do this JUST with the _id?

score 1 · Accepted Answer · answered Nov 23 '19 at 19:33

I have to go, but here's a starting point that solves your question. Can likely be optimized.

[
  {
    $addFields: /** * newField - The new field name. * expression - The new field expression. */ {
      date: { $toDate: '$_id' }
    }
  },
  {
    $addFields: /** * newField - The new field name. * expression - The new field expression. */ {
      year: { $year: '$date' },
      month: { $month: '$date' },
      day: { $dayOfMonth: '$date' }
    }
  },
  {
    $addFields: /** * newField - The new field name. * expression - The new field expression. */ {
      yearString: { $toString: '$year' },
      monthString: { $toString: '$month' },
      dayString: { $toString: '$day' }
    }
  },
  {
    $addFields: /** * newField - The new field name. * expression - The new field expression. */ {
      monthYear: { $concat: ['$yearString', '-', '$monthString'] }
    }
  },
  {
    $addFields: /** * newField - The new field name. * expression - The new field expression. */ {
      fullDate: { $concat: ['$monthYear', '-', '$dayString'] }
    }
  },
  {
    $group: /** * _id - The id of the group. * field1 - The first field name. */ {
      _id: '$fullDate',
      ids: { $push: '$_id' }
    }
  },
  {
    $project: /** * specifications - The fields to *   include or exclude. */ {
      totalClicks: { $size: '$ids' }
    }
  }
];

That gave me the relevant part I needed-- $toDate: '$_id' I can actually cut out all those $addfields and just use: _id: { $substrCP: [ {$toDate: '$_id' }, 0, 10 ] }, At which point this becomes human-readable again :) — user1279779, Nov 23 '19 at 20:34
Human-readable? And nice, hadn't seen the `$substrCp` operator yet. — christian, Nov 23 '19 at 22:16

score 1 · Answer 2 · answered Jan 17 '20 at 02:24

Cause MongoDB has $dateToString function, here's a more clean version with timezone:

[
  {
    $group: {
      _id: {
        $dateToString: {
          date: '$_id',
          format: "%Y-%m-%d",
          timezone: "Asia/Shanghai"
        }
      },
      ids: { $push: '$_id' }
    }
  },
  {
    $project: {
      totalClicks: { $size: '$ids' }
    }
  },
  { $sort: { _id: -1 } }
]

score 0 · Answer 3 · answered Nov 30 '19 at 17:43

I'm going to give Christian credit for the answer, but for anyone who comes across this down the line, here's a cleaner version of it:

db.clicks.aggregate(
  [

  {
    $group:  {
      _id: { $substrCP: [ {$toDate: '$_id' }, 0, 10 ] 
        },
      ids: { $push: '$_id' }
    }
  },
  {
    $project:  {
      totalClicks: { $size: '$ids' }
    }
  },
  {$sort: {_id: 1} }
]  )

The primary difference is that, rather than using $addFields to build the date string, I just use $toDate, then take a substring that gives me YYYY-MM-DD-- this works because $toDate happens to return a format that's close to what I want, if you wanted May 13, 2019, then you'd have to do most converting.

Since time is stored in UTC, here's one way to convert it back to your local time zone-- this is hard-coded to Pacific (8) and doesn't account for DST, but should at least give you an idea of how to mess with it.

db.clicks.aggregate(
  [

  {
    $group:  {
      _id: { $substrCP: [ {$subtract: [{$toDate: '$_id' }, 1000*60*60*8]}, 0, 10 ] 
        },
      ids: { $push: '$_id' }
    }
  },
  {
    $project:  {
      totalClicks: { $size: '$ids' }
    }
  },
  {$sort: {_id: 1} }
]  )

MongoDB aggregate by date using _id

3 Answers3