Using existing document data with new info in MongoDB FindAndModify

Question

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?

@displayName when an order is created, the document is sent to the DB, start is set at that time. Then the order is sent to work stations via node.js + socket.io. When a user completes an order, he sends an ajax request to the server to mark it as completed, this is when the server determines and $sets the endTime — cml, Jun 11 '14 at 00:58
no, where are you getting this format of startTime? you are not writing startTime by reading it from MongoDB, are you? — displayName, Jun 11 '14 at 00:59
@displayName startTime is created on the server when the orderSave process is requested. The orderSave process adds the startTime property to the JSON document received via the payload and saves it all to the DB — cml, Jun 11 '14 at 01:06
@displayName var startTime= new Date().getTime; orderData.startTime=startTime; db.collection('orders').update({orderID:'xxxx-xxxx-xxxxxxx',startTime:yyyyyyy,items:[...]},{upsert:true}); — cml, Jun 11 '14 at 01:11
then why don't you also perform the calculation of duration in the orderSave process. The way to get timestamp in MongoDB is by the command `new Timestamp()`. Refer to http://docs.mongodb.org/manual/reference/bson-types/ — displayName, Jun 11 '14 at 01:13
put your above comment in the question as an edit... It provides more info to people who are trying to answer your question. — displayName, Jun 11 '14 at 01:14

yaoxing · Accepted Answer · 2014-06-11T02:08:48.553

2

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}}.

edited Jun 11 '14 at 02:08

answered Jun 11 '14 at 01:37

yaoxing

4,003
2
21
30

You did mean `.findOne()` didn't you. Cursors don't tend to have field properties. – Neil Lunn Jun 11 '14 at 01:53
@yaoxing The reason I'm using findAndModify is for atomicity. Your findOne + findAndModify combination is interesting. Why is it better than two findAndModify? The first to set endTime and return new document, then from the new document extract both fields, calculate the duration and then a second findAndModify to set the duration. – cml Jun 11 '14 at 16:07
@yaoxing I do have a status field in the document, but didn't include it because I didn't think it relevant to the particular question. In your suggestion, are you thinking of having the status field to find, for example, open orders? I'm curious as to what else I can use the field for? What do you mean by "To lock the order with the status"? – cml Jun 11 '14 at 16:08
So why you want it 'atomic'? The only reason I see is to prevent it from being updated twice. And if you can do it with only one atomic operation, why two? After all, atomic takes more time and reduce performance. – yaoxing Jun 12 '14 at 00:16
@cml what I mean is still focus on the purpose of preventing being updated twice. I think when you set endTime the status of order should have been changed. Otherwise why is it called 'endTime'? So what I'm trying to do is, only update endTime if the order is in specific status. – yaoxing Jun 12 '14 at 00:22
By the way, you should probably update endTime/duration/orderStatus in the same atomic operation. – yaoxing Jun 12 '14 at 01:33
@yaoxing I'm using the atomic operation to prevent the operation from being update from another device at the same time. I agree you're findOne is more efficient and then only one atomic write operation is needed. – cml Jun 12 '14 at 18:54

Punnoose · Answer 2 · 2014-06-11T02:18:23.460

0

You can do this using the aggregation framework. Given below is the sample code:

db.col.aggregate(
[
    {$match:{order:1}},
    {$project:{order:1, startTime:1,endTime:{$add:[new Date().getTime()]}}},
    {$project:{order:1,startTime:1,endTime:1,duration:{$subtract:["$endTime","$startTime"]}}}
])

This is how the given pipeline will execute:

$match will select the orders matching the criteria order=1
First $project will add the "endTime" property to the selected Order
Second $project will add the duration field to the selected order by subtracting startTime from endTime

edited Jun 11 '14 at 02:18

answered Jun 11 '14 at 01:37

Punnoose

72
3

Your answer is entirely incorrect. The aggregation pipeline, as of now, does not create new documents. This aggregation will only write the result to the screen while the original docs will remain unchanged. – displayName Jun 11 '14 at 13:32

score 0 · Answer 3 · answered Jun 11 '14 at 19:06

The first problem with your code is this: You are using two different units (i.e. plain javascript and MongoDB) for putting a timestamp on a document. Not that using two different programs is wrong, but in this case here the format in which they return time are not same!

You can either let MongoDB do it all by itself or allow Nodejs to do it. Because MongoDB does not allow to do mathematical operations in its update query, therefore the best way is to accomplish this by javascript itself (or whatever you are using for interacting with MongoDB). Simply pull the doc to your orderSave(), obtain the endTime for this order, calculate the duration and run:

db.collection.update (
                      {'order' : {$eq: valueOfOrderToUpdate}},
                      {
                         $set : {'endTime': calculatedEndTimeValue},
                         $set : {'duration' : calculatedDuration}
                      },
                      {upsert : true}
);

Using existing document data with new info in MongoDB FindAndModify

3 Answers3