MongoDB AND Comparison Fails

Question

I have a Collection named StudentCollection with two documents given below,

> db.studentCollection.find().pretty()
{
    "_id" : ObjectId("52d7c0c744b4dd77efe93df7"),
    "regno" : 101,
    "name" : "Ajeesh",
    "gender" : "Male",
    "docs" : [
            "voterid",
            "passport",
            "drivinglic"
    ]
}
{
    "_id" : ObjectId("52d7c6a144b4dd77efe93df8"),
    "regno" : 102,
    "name" : "Sathish",
    "gender" : "Male",
    "dob" : ISODate("2013-12-09T21:05:00Z")
}

Why does the below query returns a document when it doesn't fulfil the criteria which I gave in find command. I know it's a bad & stupid query for AND comparison. I tried this with MySQL and it doesn't return anything as expected but why does NOSQL makes problem. I hope it's considering the last field for comparison.

> db.studentCollection.find({regno:101,regno:102}).pretty()
{
    "_id" : ObjectId("52d7c6a144b4dd77efe93df8"),
    "regno" : 102,
    "name" : "Sathish",
    "gender" : "Male",
    "dob" : ISODate("2013-12-09T21:05:00Z")
}

Can anyone brief why does Mongodb works this way?

As Kenneth indicated, the reason you are getting only one result is that the JavaScript object being created for your query can only have one value for regno. You can demonstrate this by entering in the mongo JavaScript shell: a = {regno: 10, regno2: 20} and then entering b = {regno: 10, regno: 20} and see the difference. — nachbar, Jan 16 '14 at 13:56

score 4 · Accepted Answer · edited May 23 '17 at 12:29

MongoDB leverages JSON/BSON and names should be unique (http://www.ietf.org/rfc/rfc4627.txt @ 2.2.) Found this in another post How to generate a JSON object dynamically with duplicate keys? . I am guessing the value for 'regno' gets overridden to '102' in your case.

If what you want is an OR query, try the following:

 db.studentCollection.find ( { $or : [ { "regno" : "101" }, {"regno":"102"} ] } );

Or even better, use $in:

 db.studentCollection.find ( { "regno" : { $in: ["101", "102"] } } );

Hope this helps!

Edit : Typo!

score 0 · Answer 2 · answered Jan 16 '14 at 16:19

MongoDB converts your query to a Javascript document. Since you have not mentioned anything for $and condition in your document, your query clause is getting overwritten by the last value which is "regno":"102". Hence you get last document as result.

If you want to use an $and, you may use any of the following:

db.studentCollection.find({$and:[{regno:"102"}, {regno:"101"}]});

db.studentCollection.find({regno:{$gte:"101", $lte:"102"}});

MongoDB AND Comparison Fails

2 Answers2