Mongoose: Aggregate $match with ISODate

[whichsteveyp]

Hey guys -

Have been scratching my head today on this one.

TL;DR

When using a .aggregate([{ $match: }]) , I'm unable to get results back when matching dates. I am able to get results back from the command line via aggregation matching, as well as Robomongo, and from Model.find() when using a date.

Tests - Newbie Disclaimer

I went ahead and forked so that I could add in some tests, although it appears that aggregate.test.js does not run any of its tests. At least I'm getting a 0 tests complete when doing T="-g 'aggregate.test.js' -R spec" make test - console.logs() work inside the describe() levels, but not instead the it() levels. I'm new to tests a bit so I'll keep working on that.

Some details

Looking to $match a set of data to a date range, so that I can pipeline it to a $group.

This works and returns data from Robomongo (as well as the mongo shell):

db.points.aggregate([
{
  '$match' : { 'createdAt' : { '$gte' : ISODate('2013-07-26T18:23:37.000Z') } }
},
{
  '$group' : { '_id' : '$rfid' }
}
]);

This works and returns data from Model.find()

Points.find({
    createdAt: {
        $gt: '2013-07-26T18:23:37.000Z',
    }
}, callback);

Additionally, this aggregate pipeline worked before, where vendorId was a string for the $match values

Points.aggregate([
    { $match : { vendorId : vendorId } },
    { $group : { _id : '$rfid', amount: { $max : '$amount' } } },
    { $project: { _id: 0, amount: 1, rfid: '$_id' } },
    { $sort : { points: -1 } },
    { $limit : 25 }
], callback);

The 'bug'

I am not able to retrieve any results from Points.aggregate([]) when using a $match that contains any variation of this:

$match : { createdAt : { $gt: utcBounds.start, $lt: utcBounds.end } }  // moment utc dates that work in .find() as well
$match : { createdAt : Date('2013-07-26T18:23:37.000Z') }
$match : { createdAt : Date('2013-07-26T18:23:37.000Z').toISOString() }
$match : { createdAt : Schema.Types.Date('2013-07-26T18:23:37.000Z') }
$match : { createdAt : '2013-07-26T18:23:37.000Z' }
$match : { createdAt : { $gt: '2013-07-26T18:23:37.000Z' } }

Sample Data

If it helps, pulling up a record from MongoLabs looks like this:

{
    "rfid": "0004980F0A9E2A80",
    "type": "debit",
    "vendorId": "51e837ce2d494abc32000001",
    "fullName": "Stephen Rivas Jr.",
    "_id": {
        "$oid": "51f2bea9560b54b437000001"
    },
    "createdAt": {
        "$date": "2013-07-26T18:23:37.000Z"
    },
    "amount": 15,
    "__v": 0
}

If I'm missing something, I apologize. After several different attempts, and upon realizing I am able to query the collection through other methods with the same type of query - I figured this was a bug and not necessarily a user error. Mayhaps have missed something in the documentation too.

Like I said above, I went ahead and forked the repo so I can start trying to build some tests for it - if someone wants to point me in the right direction there for running those I'm happy to try and find the root of the issue as well.

[whichsteveyp]

Whoops - version information:

mongoose: 3.6.15
mongo: v2.4.5 (mongod @ MongoLabs)
node: v0.10.9

[aheckmann]

As documented, Model.aggregate does not cast it's arguments. You'll need to create instances of Date.

[whichsteveyp]

The issue is that none of these work in Mongo, or Mongoose:

$match : { createdAt : { $gt: utcBounds.start, $lt: utcBounds.end } }  // moment utc dates that work in .find() as well
$match : { createdAt : Date('2013-07-26T18:23:37.000Z') }
$match : { createdAt : Date('2013-07-26T18:23:37.000Z').toISOString() }
$match : { createdAt : Schema.Types.Date('2013-07-26T18:23:37.000Z') }
$match : { createdAt : '2013-07-26T18:23:37.000Z' }
$match : { createdAt : { $gt: '2013-07-26T18:23:37.000Z' } }

Mongoose will only return with $match if the date is wrapped in ISODate(). It would appear then that you are unable to query $match aggregation by dates; and would just have to do a map reduce.

[ebensing]

I just created a simple test script and everything seems to be working fine. You _must_ pass the date value as a javascript Date object.

If this still isn't your issue, can you explain again what the problem is?

For reference, the testing code I used is here:

var mongoose = require('./lib');                                                
var Schema = mongoose.Schema;                                                   

var ts = new Schema({                                                           
  title : String,                                                               
  d : Date                                                                      
});                                                                             

var Test = mongoose.model('Test', ts);                                          

mongoose.connect('mongodb://localhost/tmongoose', function (err) {              
  if (err) throw err;                                                           

  var count = 5;                                                                

  for (var i=0; i < 5; i++) {                                                   
    var d = new Date();                                                         
    d.setDate(i);                                                               
    Test.create({ title : i.toString(), d : d }, function (err) {               
      if (err) throw err;                                                       
      --count || next();                                                        
    });                                                                         
  }                                                                             

  function next() {                                                             
    var d = new Date;                                                           
    d.setDate(1);                                                               
    Test.aggregate({ $match : { d : { $gt : d } } }).group({ _id : "$_id", title : { $max : "$title" } }).exec(function (err, res) {
      if (err) throw err;                                                       
      console.log(res);                                                         
      Test.remove(function (err) {                                              
        if (err) throw err;                                                     
        mongoose.disconnect();                                                  
      });                                                                       
    });                                                                         

  }                                                                             
});

[whichsteveyp]

Bug found. User error (shocker).

Date('2013-07-29T20:15:09.000Z') !== new Date('2013-07-29T20:15:09.000Z'). I was unaware there was a a difference between the two.

Sorry to pester then, thanks much.

[raoabishay]

@sprjr : Thank you. This saved me a lot of heartache!

[n0impossible]

@sprjr : you're rock bro

this work fine to me

Points.find({ createdAt: { $gt: new Date('2013-07-29T20:15:09.000Z'), } }, callback);

Thanks a lot

[Jo-ravar]

Brooooo you are awesome.....!
you made my day this works fine for me
Modelname.aggregate([$match:{date: new Date('2016-12-26T00:00:00.000z')}])

[adaviding]

I wish I could define the match statement for a Mongo aggregator pipeline like this:

"$match" : {
   "context.observationTime" :  {
        "$gte" :{"$date" : "2016-01-01T00:00:00.000Z"} ,
    "$lt" : {"$date" : "2016-07-01T00:00:00.000Z"}
}

I feel like this should be a feature of Mongo.

[vkarpov15]

@adaviding I'd check out this npm module: https://www.npmjs.com/package/mongodb-extended-json it'll automatically convert { '$date': "2016-01-01T00:00:00.000Z"} to a date object for you

[karthikeyana]

your should try some thing like this

$match : { createdAt : new Date('2013-07-26T18:23:37.000Z') }

[radiumrasheed]

// Pipeline
[
    // Stage 1
    {
        $project: {
            day: {$dayOfMonth: "$connected_at"},
            week: {$week: "$connected_at"},
            month: {$month: "$connected_at"},
            _device: "$_device",
            period: "$period",
            current_week: {$week: new Date()}
        }
    },

    // Stage 2
    {
        $match: {
            week: "$current_week" // or
                   week: {$week : new Date()}
        }
    },

I hope to get documents for the current week only.

Any help ?

[dmdb]

You can use moment to get current week number, then cast it to int and pass to $match

[prafful-panwar]

I am not getting the result from below code but there are records in my Event Collection with given date range.

Here is my code

$startDate = new \DateTime(request()->input('start_from_date'));
$endDate = new \DateTime(request()->input('end_from_date'));

Event::raw(function($collection) use($startDate, $endDate) {
            return $collection->aggregate([
                    [
                        '$match' => [
                            'start_timestamp' => [
                                '$gte' => $startDate,
                                '$lte' => $endDate
                            ]
                        ]
                    ],
                    [
                        '$group' => [
                            '_id' => array(
                                '$dateToString' => array(
                                    "format" => "%Y-%m-%d",
                                    "date" => '$start_timestamp'
                                ),
                            ),
                            'count' => ['$sum' => 1],
                        ]
                    ],
                    ['$sort' => array("_id" => 1)]
            ]);
        });

MongoDB query

events.aggregate([{"$match":{"start_timestamp":{"$gte":{"date":"2017-06-23 00:00:00.000000","timezone_type":3,"timezone":"Asia\/Kolkata"},"$lte":{"date":"2018-06-25 00:00:00.000000","timezone_type":3,"timezone":"Asia\/Kolkata"}}}},{"$group":{"_id":{"$dateToString":{"format":"%Y-%m-%d","date":"$start_timestamp"}},"count":{"$sum":1}}},{"$sort":{"_id":1}}])

My Collection

{ 
    "_id" : ObjectId("5b2df7a2309a2013d5572dd3"), 
    "title" : "Create your event", 
    "slug" : "create-your-event-5b2e19a70c4c6", 
    "description" : "Create your event Create your event", 
    "location" : "4th Floor, UNI Building,, Thimmaiah Road, Millers Tank Bund Rd, Govinda Chetty Colony, Kaverappa Layout, Vasanth Nagar, Bengaluru, Karnataka 560052, India",
    "start_timestamp" : ISODate("2018-08-01T07:32:00.000+0000"), 
    "end_timestamp" : ISODate("2018-08-24T09:32:00.000+0000")
}

[vkarpov15]

@prafful-panwar are you sure you're posting in the right repo? Your code doesn't look like valid JavaScript unless I'm missing something here.

[karunakapil]

please anyone help me to find users who visit my page more than 3 days out of 7 days?
need mongo query to solve this

[vkarpov15]

@karunakapil impossible to tell without knowing what your schema looks like. But here's an example of how you can do this with a simple event schema:

const assert = require('assert');
const mongoose = require('mongoose');
mongoose.set('debug', true);

const GITHUB_ISSUE = `gh1599`;
const connectionString = `mongodb://localhost:27017/${ GITHUB_ISSUE }`;
const { Schema } = mongoose;

run().then(() => console.log('done')).catch(error => console.error(error.stack));

async function run() {
  await mongoose.connect(connectionString);
  await mongoose.connection.dropDatabase();

  const eventSchema = new Schema({
    timestamp: Date,
    userId: Number
  });
  const Event = mongoose.model('Event', eventSchema);

  await Event.create([
    { timestamp: new Date('2019-06-01'), userId: 1 },
    { timestamp: new Date('2019-06-07'), userId: 1 },
    { timestamp: new Date('2019-06-08'), userId: 1 },
    { timestamp: new Date('2019-06-07'), userId: 2 },
    { timestamp: new Date('2019-06-08'), userId: 2 },
    { timestamp: new Date('2019-06-09'), userId: 2 },
  ]);

  const res = await Event.aggregate([
    { $match: { timestamp: { $gte: new Date('2019-06-02') } } },
    { $project: { userId: '$userId', dayOfWeek: { $isoDayOfWeek: '$timestamp' } } },
    { $group: { _id: '$userId', days: { $addToSet: '$dayOfWeek' } } },
    { $match: { 'days.2': { $exists: true } } },
    { $project: { _id: 1 } }
  ]);

  console.log(res);
}