Date Objects in MongoDB Aggregate with Mongoose and Nodejs

Question

I am trying to operate on Date objects within an aggregation from nodejs with mongoose. My DB document has the following form:

{"__v" : 0,
"dob" : ISODate("1991-04-10T11:41:02.361Z"),
"gender" : "MALE",
...}

I am trying calculate the current age as follows, which works fine in robomongo and from the shell

db.p.aggregate([
{$project: { "age": {$divide: [{ $subtract: [ new ISODate(),  "$dob"  ] },31558464000]}}}
])

Output:

"result" : [ 
    {
        "age" : 23.00286577030492,
        "gender" : "MALE"
    }, 
    ...
]

But I cannot get it to work from within mongoose. If I use the same query, ISODate is not defined as stated here: ISODate is not defined

So as proposed I tried different variations of Date()

{$project: { "age": {$divide: [{ $subtract: [ new Date(),  "$dob"  ] }, 31558464000] }}}
RESULT: [MongoError: exception: cant $subtract a String from a Date]

{$project: {
   "age": {$divide: [{ $subtract: [ new Date(),  new Date("$dob")  ] }, 31558464000] },
   "dob2": { $subtract: [ new Date("$dob"), 1  ]  }, "dob": 1}}
RESULT: [ { _id: '10000000000000000000000b',
dob: '1991-04-10T11:41:02.361Z',
age: 44.27401739168928,                          <-- wrong age
dob2: Thu Jan 01 1970 00:59:59 GMT+0100 (CET) }, <-- wrong date
... ]

 {$project: {
   "age": {$divide: [{ $subtract: [ new Date(),  Date("$dob")  ] }, 31558464000] },
   "dob": 1}}
 RESULT: [MongoError: exception: cant $subtract a String from a Date]

If I try to convert the String to a date within the aggregation, it fails to convert correctly but outside of aggregate it works fine:

var dateISOString = "1991-04-10T11:41:02.361Z";
var dateTimeDate = new Date(startTimeISOString);
RESULT: Wed Apr 10 1991 13:41:02 GMT+0200 (CEST) 23.00290412198135  <-- correct Date

So I cannot use the direct reference $dob because it is treated as a String. I cannot use ISODate conversion because it is not defined and I cannot use new Date(), because the conversion is wrong within the aggregation framework. I have googled extensively, but could not find a solution to the problem. How can I either convert my ISODate string to the correct date or directly get a date in the first place without, leaving the aggregation. Help is much appreciated.

MongoDB: "version" : "2.4.9",
"sysInfo" : "Linux SMP Fri Nov 20 17:48:28 EST 2009 x86_64 BOOST_LIB_VERSION=1_49",

nodejs: v0.10.21

Edit after Neil's reply:

     function test( user, callback ) {
        var mongoose = require( 'mongoose' );

        mongoose.connect( 'mongodb://localhost:8080/11', function( ) {  } );


            var
                collection;
            if( err ) {
                callback( err );
            } else {
                collection = mongoose.connections[0].collection( 'aggregate' );

                        collection.aggregate([
                            { "$project": {
                                "_id": 0,
                                "this": { "$divide": [
                                    { "$subtract": [ new Date(), "$dob" ]},
                                    31558464000
                                ]}
                            }}
                        ], function( err, res ) {
                            console.warn( 'collection.doneCb.1', res, JSON.stringify( res ) );
                            callback( err, res );
                        } );

                        /*collection.aggregate( query, function( err, res ) {
                         console.warn( 'collection.doneCb.1', res, JSON.stringify( res ) );
                         callback( err, res );
                         } );*/
                    } );

                } );

            }

};

This produces:

{ [MongoError: exception: cant $subtract a String from a Date]
name: 'MongoError',
errmsg: 'exception: cant $subtract a String from a Date',
code: 16613,
ok: 0 }

Thanks!

Did the answer not explain your problem properly? I see no response. — Neil Lunn
Thank you Neil. It is a work related question so over the weekend I did not check, sorry for the delay. I will reply in a couple of hours after my appointment, but at first glance I see no mongoose connection in your reply. There are no problems when I query from shell. From mongoose the reference to a date produces a string (first non working query) — user1437515
I actually wrote out the response as fairly generic method as it does have a possibility of applying outside of Mongoose itself, and is basically just JavaScript. So actually since there is no ISODate function available to JavaScript with "node" or "Mongoose" then it basically solves. For Mongoose in particular, then just replace the db.birthdays part with the name of your actual model and the appropriate callback function after the conditions. — Neil Lunn

Neil Lunn Neil Lunn · Accepted Answer · 2014-04-11T13:33:29

So just to clarify the output. ISODate is an internal function to MongoDB shell, or is otherwise provided in a driver function. But for JavaScript just use the Date() object constructor:

Given the data:

{ "date" : ISODate("1971-09-22T00:00:00Z") }

The following query:

db.birthdays.aggregate([
    { "$project": { 
        "_id": 0,
        "this": { "$divide": [
            { "$subtract": [ new Date(), "$date" ]}, 
            31558464000 
        ]} 
    }}
])

Produces:

{ "this" : 42.552055734461604 }

Even though I'm sorry to say it ;-)

Date Objects in MongoDB Aggregate with Mongoose and Nodejs

1 Answers