Auto calculating fields in mongodb

Question

Let's say there are documents in MongoDB, that look something like this:

{
    "lastDate" : ISODate("2013-14-01T16:38:16.163Z"),
    "items":[
        {"date":ISODate("2013-10-01T16:38:16.163Z")},
        {"date":ISODate("2013-11-01T16:38:16.163Z")},
        {"date":ISODate("2013-12-01T16:38:16.163Z")},
        {"date":ISODate("2013-13-01T16:38:16.163Z")},
        {"date":ISODate("2013-14-01T16:38:16.163Z")}        
    ]
}

Or even like this:

{
    "allAre" : false,
    "items":[
        {"is":true},
        {"is":true},
        {"is":true},
        {"is":false},
        {"is":true}        
    ]
}

The top level fields "lastDate" and "allAre" should be recalculated every time the data in array changes. "lastDate" should be the biggest "date" of all. "allAre" should be equal to true only if all the items have "is" as true.

How should I build my queries to achieve such a behavior with MongoDB?
Is it considered to be a good practice to precalculate some values on insert, instead of calculating them during the get request?

You want to update the record according to this condition or you want this condition should be appled on insert. — Himanshu sharma, Oct 15 '16 at 07:31
@Himanshusharma the idea is to update calculated field (such as `"allAre` or `"lastDate"`) on every operation with `"items"`. It could be `$pull`, `$push`, or `$ (update)`. — dmigo, Oct 15 '16 at 07:38
@dmigo why you can't just extend the update query and set manually the property? I mean since you are making a "push" then you know that the value should be changed. — Daniele Tassone, Oct 16 '16 at 16:37
@DanieleTassone it would be great, but how can I achieve that? Consider that when I push an item `{"date":ISODate("2013-11-01T16:38:16.163Z")}` there might be an item with a bigger date in the array already. And sometimes I want to perform `$pull` operation as well. — dmigo, Oct 16 '16 at 17:44
In two step, is more easy: i mean with 2 query. Can 2-step work well for you? — Daniele Tassone, Oct 16 '16 at 19:45
@DanieleTassone it could, but I fear of race conditions. That might become a problem if two operations, each consisting of two queries, would be executed simultaneously. — dmigo, Oct 16 '16 at 19:48
@dmigo lastDate should be the "latest date pushed" or "highest date pushed"? I mean if i push (10-oct) and in the array there is (15-oct) the 'lastDate' should not be updated because is not the 'highest'. — Daniele Tassone, Oct 16 '16 at 20:20
@DanieleTassone the "highest" one, in your example the 15th of October. — dmigo, Oct 16 '16 at 20:56
@dmigo ok then you should not have race condition, MongoDB is atomic for operation. Then if 2 update on the same document will happen, then the most 'highest' date will be set. I will write you an answer with 2-step strategy. — Daniele Tassone, Oct 16 '16 at 21:43

score 1 · Accepted Answer · answered Oct 17 '16 at 11:14

MongoDB cannot make what you are asking for with 1 query. But you can make it in two-step query.

First of all, push the new value into the array:

db.Test3.findOneAndUpdate(
{_id: ObjectId("58047d0cd63cf401292fe0ad")},
{$push: {"items":  {"date": ISODate("2013-01-27T16:38:16.163+0000")}}},
{returnNewDocument: true},
function (err, result) {

}
);

then update "lastDate" only if is less then the last Pushed.

  db.Test3.findOneAndUpdate (
   {_id: ObjectId("58047d0cd63cf401292fe0ad"), "lastDate":{$lt: ISODate("2013-01-25T16:38:16.163+0000")}},
   {$set: {"lastDate": ISODate("2013-01-25T16:38:16.163+0000")}},
   {returnNewDocument: true},
   function (err, result) {
   }
  );

the second parameter "lastDate" is needed in order to avoid race condition. In this way you can be sure that inside "lastDate" there are for sure the "highest date pushed".

Related to the second problem you are asking for you can follow a similar strategy. Update {"allAre": false} only if {"_id":yourID, "items.is":false)}. Basically set "false" only if some child of has a value 'false'. If you don't found a document with this property then do not update nothing.

// add a new Child to false
db.Test4.findOneAndUpdate(
{_id: ObjectId("5804813ed63cf401292fe0b0")},
{$push: {"items":  {"is": false}}},
{returnNewDocument: true},
 function (err, result) {

}
);

// update allAre to false if some child is false
db.Test4.findOneAndUpdate (
   {_id: ObjectId("5804813ed63cf401292fe0b0"), "items.is": false},
   {$set: {"allAre": false}},
   {returnNewDocument: true},
   function (err, result) {
   }
  );

Thank you! it seems to be it. For the first case one might even use `$max`. It is a bit disappointing that MongoDB doesn't have update-select functionality. — dmigo, Oct 17 '16 at 12:05

Auto calculating fields in mongodb

1 Answers1

Linked