Search code examples
mongodbfindandmodify

Using existing document data with new info in MongoDB FindAndModify

A document has the following schema when an order is created:

{order:1,startTime:1402442830110}

When an order is finished, a db.collection.findAndModify() operation is processed to set the endTime resulting in a document like so:

{order:1,startTime:1402442830000,endTime:1402442930000}

In that same process, I need to set the order duration, calculated by subtracting both times: doc.endTime-doc.startTime.

Is there an operator or procedure that helps do this in a single operation? Or do I have to perform two FindAndModify operations, one to set the new data returning the new document and another to set the duration field?

Solution

  • From what I'm understanding, translated to SQL you want to do something like this

    UPDATE [Order] SET
    EndTime = GETDATE(),
    Duration = GETDATE() - StartTime
WHERE OrderID = ''

    Unfortunately this is not supported in MongoDB yet. However, you can still do it with 2 updates. The first one is not necessarily a findAndModify, a findOne is enough.

    var startTime = db.order.findOne({order: 1}).startTime;
var endTime = new Date();
db.order.findAndModify({
    query: {order: 1},
    update: {$set: {endTime: endTime, duration: endTime - startTime}}
});

    Actually I don't think the findAndModify here will bring you any advantage than pure update, unless you want to prevent the same order from being updated more than once. In this case, it would be better change the condition like this:

    var startTime = db.order.findOne({order: 1}).startTime;
var endTime = new Date();
db.order.findAndModify({
    query: {order: 1, endTime: {$exists: false}},
    update: {$set: {endTime: endTime, duration: endTime - startTime}}
});

    In my point of view, you should have something to control the status of the order, like ordered/paid/cancelled... To lock the order with the status would be much better than {endTime: {$exists: false}}.