$elemmatch not working in MongoDB

Question

I am trying to filter mongo data by using the below query in mongodb version 2.6.1 but getting error.

MongoDB version 2.4.6 (Working):

> db.BC_1839.find({data: {$elemMatch:{$where : "this.First_name.toLowerCase().indexOf('kim') ==0"}}});

output:

{ 
    "_id" : ObjectId("53719a9d5b9e5c8c110001b9"),
    "data" : [
        {
            "First_name" : "Kimberely",
            "Last_name" : "Weyman",
            "Company_name" : "Scientific Agrcltl Svc Inc",  
            "Address" : "7721 Harrison St",
            "City" : "Kingsway West",
            "State" : "NS",
            "Post" : "2208",   
            "Phone1" : "02-7091-8948",   
            "Phone2" : "0441-151-810", 
            "Email" : "[e-mail]",
            "Web" : "http://www.scientificagrcltlsvcinc.com.au", 
            "active" : "true"
        } 
    ], 
    "history" : [    
        { 
            "timestamp" : "2014-05-13 06:07:55",
            "event": "creation",
            "createdby" : "Srikesh Infotech",
            "creation_data" : [
                {
                    "crm_base_contact_id" : "1839",
                    "crm_imported_files_id" : "1464"
                }  
            ] 
        },
        {
            "timestamp" : "2014-05-13 06:09:05",
            "event" : "Task",
            "createdby" : "Srikesh Infotech",
            "Task_data" : [ 
                {
                    "Campaign ID" : "193",  
                    "Campagin Name" : "Test Campa1"
                }
            ]
        }
    ], 
    "ref" : [ 
        { "crm_base_contact_id" : "1839", "crm_imported_files_id" : "1464" }
    ]
}

MongoDB version 2.6.1(Not Working):

> db.BC_1839.find({data: {$elemMatch:{$where : "this.First_name.toLowerCase().indexOf('kim') ==0"}}});

output:

error: {
        "$err" : "Can't canonicalize query: BadValue $elemMatch cannot contain $
where expression",
        "code" : 17287
}

Same query executes in mongodb version 2.4.6 but not in mongodb version 2.6.1 Why???

Answer 1

It shouldn't have worked in earlier versions at all, as at the very least you have modified the scoping of this to now refer to "data" as a top level element. In short, this is no longer allowed and you really should not be using JavaScript methods unless you absolutely have to. Even then, there is probably still a better way in most cases.

But in fact this is an un-necessary use of JavaScript matching as it is not required when there are other operators existing that will do this.

You should be using a $regex form instead:

db.docs.find({ "data.First_name": /^kim/i })

Or anywhere within the field, remove the caret ^:

db.docs.find({ "data.First_name": /kim/i })

Which is pretty much as inefficient as JavaScript execution but not as much as there is not the overhead of processing through that interpreter engine. And of course it works everywhere.

Also think about what a query relying on JavaScript to resolve is actually doing:

• Invokes a JavaScript interpreter instance
• Converts BSON document types per document to JavaScript types
• Evaluates JavaScript code in the interpreter per document
• Casts JavaScript true|false back as a result per document

Considering that $regex ( but with a case insensitive match which is not optimal ) is doing the same operations but using the "pcre" C library natively without conversion and recasting per document, then it is clearly the sane choice of the two.