Is there any way to get date from ObjectId from mongoose using aggregate?

Question

I have Users Collection. devices are all in array of Objects.

[{
    "_id" : ObjectId("5c66a979e109fe0f537c7e37"),
    "devices": [{
            "dev_token" : "XXXX",
            "_id" : ObjectId("5ccc0fa5f7778412173d22bf")
        }]

},{
    "_id" : ObjectId("5c66b6382b18fc4ff0276dcc"),
    "devices": [{
            "dev_token" : "XXXX",
            "_id" : ObjectId("5c93316cc33c622bdcfaa4be")
        }]

}]

I need to query the documents with adding the new field date in devices like

"devices": [{
       "dev_token" : "XXXX",
        "_id" : ObjectId("5c93316cc33c622bdcfaa4be"),
       "date": ISODate("2012-10-15T21:26:17Z")
}]

date key from devices._id.getTimestamp()

I tried using aggregate this one, donno how to use getTimestamp()

db.getCollection('users').aggregate([ {
    "$unwind": "$devices"
}, {
    "$group": {
        "_id": "$_id",
        "devices": {
                "$push": "$devices._id.getTimestamp()"
          }
     }
}])

I use $devices._id.getTimestamp(), this could be error.. Here how I handle this one.. Thanks for advance

Answer 1

Using MongoDb 3.6

The $dateFromParts operator comes in handy here where you can use it in conjunction with the other date operators. You won't need to $unwind the array as you can use $map to map over the devices array documents and add the extra date field with the above expression.

This can be followed with an example pipeline below :

db.getCollection('users').aggregate([    
    { "$addFields": {
        "devices": {
            "$map": {
                "input": "$devices",
                "in": {
                    "dev_token": "$$this.dev_token",
                    "_id": "$$this._id",
                    "date": {
                        "$dateFromParts": {
                            'year': { "$year": "$$this._id"}, 
                            'month': { "$month": "$$this._id"}, 
                            'day':{ "$dayOfMonth": "$$this._id"},
                            'hour': { "$hour": "$$this._id"}, 
                            'minute': { "$minute": "$$this._id"}, 
                            'second': { "$second": "$$this._id"},
                            'millisecond': { "$millisecond": "$$this._id"}
                        }
                    }
                }
            }
        }
    } }
])

Output

/* 1 */
{
    "_id" : ObjectId("5c66a979e109fe0f537c7e37"),
    "devices" : [ 
        {
            "dev_token" : "XXXX",
            "_id" : ObjectId("5ccc0fa5f7778412173d22bf"),
            "date" : ISODate("2019-05-03T09:53:41.000Z")
        }
    ]
}

/* 2 */
{
    "_id" : ObjectId("5c66b6382b18fc4ff0276dcc"),
    "devices" : [ 
        {
            "dev_token" : "XXXX",
            "_id" : ObjectId("5c93316cc33c622bdcfaa4be"),
            "date" : ISODate("2019-03-21T06:38:36.000Z")
        }
    ]
}

---

Using MongoDb 4.0 and newer:

The pipeline can be tweaked slightly to use the new $toDate or $convert operators. Their respective uses follow:

$toDate

db.getCollection('users').aggregate([    
    { "$addFields": {
        "devices": {
            "$map": {
                "input": "$devices",
                "in": {
                    "dev_token": "$$this.dev_token",
                    "_id": "$$this._id",
                    "date": { "$toDate": "$$this._id" }
                }
            }
        }
    } }
])

$convert

db.getCollection('users').aggregate([    
    { "$addFields": {
        "devices": {
            "$map": {
                "input": "$devices",
                "in": {
                    "dev_token": "$$this.dev_token",
                    "_id": "$$this._id",
                    "date": { 
                        "$convert": { "input": "$$this._id", "to": "date" }
                    }
                }
            }
        }
    } }
])

Answer 2

You can use $toDate to get Timestamp from the _id field.

Add date field to each devices element after unwind stage, using $addFields

Try this :

db.getCollection('users').aggregate([ {
    "$unwind": "$devices"
},{
    $addFields : {
        "devices.date": { $toDate: "$_id" }
    }
}, {
    "$group": {
        "_id": "$_id",
        "devices": {
                "$push": "$devices"
          }
     }
}])

You can check the result at Mongo Playground (just press "run")