How to extract ObjectId to timestamp using mongodb function gettimestamp() in Pentaho Data Integrator

Question

I'm trying to modify my ETL flow on Pentaho, which is mongodb to oracle. I'd like to change it to incremental flow so I need a create date.

In mongodb there is a column named _id which has ObjectId and it gives you timestamp with getTimestamp() function.

ObjectId.getTimestamp()

How can use it on the json path though?

Here is ss from pentaho;

enter image description here

I tried this $._id.getTimestamp() and it give me null value.

Answer 1

var objectIdFromDate = function (date) {
    return Math.floor(date.getTime() / 1000).toString(16) + "0000000000000000";
};

var dateFromObjectId = function (objectId) {
    return new Date(parseInt(objectId.substring(0, 8), 16) * 1000);
};

Source and credits: SteveRdiout https://github.com/SteveRidout/mongo-object-time