CODEWITHSUNDEEP
mongodbmongodb-queryexists
Sav74
Sav74

Reputation: 88

Mongodb weird behaviour of $exists

I don't understand the behaviour of the command $exists.

I have two simple documents in the collection 'user':

/* 1 */
{
    "_id" : ObjectId("59788c2f6be212c210c73233"),
    "user" : "google"
}

/* 2 */
{
    "_id" : ObjectId("597899a80915995e50528a99"),
    "user" : "werty",
    "extra" : "very important"
}

I want to retrieve documents which contain the field "extra" and the value is not equal to 'unimportant':

The query:

db.getCollection('users').find(
{"extra":{$exists:true},"extra": {$ne:"unimportant"}}
)

returns both two documents.

Also the query 

db.getCollection('users').find(
{"extra":{$exists:false},"extra": {$ne:"unimportant"}}
)

returns both two documents.

It seems that $exists (when used with another condition on the same field) works like an 'OR'. What I'm doing wrong? Any help appreciated.

I used mongodb 3.2.6 and 3.4.9

I have seen Mongo $exists query does not return correct documents but i haven't sparse indexes.

Upvotes: 3

Views: 237

Answers (2)

chridam
chridam

Reputation: 103365

The way you constructed your query is wrong, nothing to do with how $exists works. Because you are checking two conditions, you would need a query that does a logical AND operation to satisfy the two conditions.

The correct syntax for the query

I want to retrieve documents which contain the field "extra" and the value is not equal to 'unimportant'

should follow:

db.getCollection('users').find(
    {
        "extra": {
            "$exists": true,
            "$ne": "unimportant"
        }
    }
)

or using the $and operator as:

db.getCollection('users').find(
    {
        "$and": [
            { "extra": { "$exists": true } },
            { "extra": { "$ne": "unimportant" } }
        ]
    }
)

Upvotes: 0

marcofo88
marcofo88

Reputation: 385

Per MongoDB documentation (https://docs.mongodb.com/manual/reference/operator/query/and/):

Using an explicit AND with the $and operator is necessary when the same field or operator has to be specified in multiple expressions.

Therefore, and in order to enforce the cumpliment of both clauses, you should use the $and operator like follows:

db.getCollection('users').find({ $and : [ { "extra": { $exists : true } }, { "extra" : { $ne : "unimportant" } } ] });

Upvotes: 3

Related Questions