Geek Answers Handbook

How to add a field to a document containing the result of comparing two other fields

I would like to speed up the query in my mongoDB, which uses $ where to compare two fields in a document, which seems very slow.

My query looks like this:

db.mycollection.find({ $where : "this.lastCheckDate < this.modificationDate})

What I would like to do is add a field to my document, i.e. isCheckDateLowerThenModDate , on which I could probably execute a much faster request:

 db.mycollection.find({"isCheckDateLowerThenModDate":true})

I am completely new to mongoDB, I do not know how to do this. I would appreciate if anyone could give me some tips or examples on

  • How to initialize such a field in an existing collection
  • How to save this field. This means how to update this field when changing lastCheckDate or modificationDate .

Thanks in advance for your help!

+4
source share
2 answers

You think correctly!

1. How to initialize such a field in an existing collection.

The easiest way is to download each document (from your language), calculate this field, update and save.

Or you can upgrade through the mongo shell:

 db.mycollection.find().forEach(function(doc) { if(doc.lastCheckDate < doc.modificationDate) { doc.isCheckDateLowerThenModDate = true; } else { doc.isCheckDateLowerThenModDate = false; } db.mycollection.save(doc); });

2. How to save this field. This means how to update this field when lastCheckDate or modifyDate.

You must do this yourself from your client code. Make some shell for updating, save operations and recount this value every time there. To be absolutely sure that this update works - write unit tests.

+1
source

The $where clause is slow because it evaluates each document using a JavaScript interpreter.

There are several alternatives:

1) Assuming your use case is to “look for posts that need updating,” use the sparse index :

  • add a boolean field like needsChecking and $set each time you update modificationDate

  • in your check procedure, find documents that have this field set (should be fast due to a sparse index)

    db.mycollection.find ({'needsChecking': true});

  • after you have done all the necessary checks, the $unset needsChecking field.

2) The new (and faster) function in MongoDB 2.2 is the Aggregate Structure .

Here is an example of adding the isUpdated field based on comparing dates and filtering related documents:

  db.mycollection.aggregate( { $project: { _id: 1, name: 1, type: 1, modificationDate: 1, lastCheckDate: 1, isUpdated: { $gt:["$modificationDate","$lastCheckDate"] } }}, { $match : { isUpdated : true, }} )

Some current caveats about using an aggregation structure are:

  • you need to specify the fields that should be included in addition to _id
  • the result is limited by the current maximum BSON document size (16 MB in MongoDB 2.2)
+1
source

More articles:


All Articles