CODEWITHSUNDEEP
mongodbmongoose
Point Networks
Point Networks

Reputation: 1101

How to search document with condition of not having exact object in array of objects?

I have a collection of persons whose schema looks like the collection of following documents.

    Document: {

    name:
    age:
    educations:[{
title:xyz,
passed_year:2005,
univercity:abc},
{
title:asd
passed_year:2007,
univercity:mno
}],
current_city:ghi
}

Now I wanna show all the persons who has not done xyz education from abc university in year 2005.

I think two possible queries for this need but not sure which one to use as both of them are giving me the output

Query 1:

db.persons.find({"education":{$ne:{$elemMatch:{"title":"xyz","passed_year":2005,"univercity":"abc"}}}})

Query 2:

db.persons.find({"education":{$not:{$elemMatch:{"title":"xyz","passed_year":2005,"univercity":"abc"}}}})

I'm quite confused about operator $ne and $not, which one should I use with $elemMatch as both of them are giving me the output.

Upvotes: 0

Views: 108

Answers (1)

glytching
glytching

Reputation: 47865

Given this $elemMatch: {"title":"xyz","passed_year":2005,"univercity":"abc"} I think you want to exclude any documents which contain an sub document in the educations array which contains all of these pairs:

  • "title" : "xyz"
  • "passed_year" : 2005
  • "univercity" : "abc"

This query will achieve that:

db.persons.find({
    "educations": {
        $not: {
            $elemMatch:{"title": "xyz", "passed_year": 2005, "univercity": "abc"}
         }
     }
})

In your question you wrote:

both of them are giving me the output

I suspect this is because your query is specifying education whereas the correct attribute name is educations. By specifying education you are adding a predicate which cannot be evaluated since it references a non existent document attribute so regardless of whether that predicate uses $ne or $not it will simply not be applied.

In answer to the question of which operator to use: $not or $ne: if you run the above query with .explain(true) you'll notice that the parsed query produced by Mongo is very different for each of these operators.

  • Using $ne

    "parsedQuery" : {
    "$not" : {
        "educations" : {
            "$eq" : {
                "$elemMatch" : {
                    "title" : "xyz",
                    "passed_year" : 2005,
                    "univercity" : "abc"
                }
            }
        }
    }
}

  • Using $not:

    "parsedQuery" : {
    "$not" : {
        "educations" : {
            "$elemMatch" : {
                "$and" : [ 
                    {
                        "passed_year" : {
                            "$eq" : 2005
                        }
                    }, 
                    {
                        "title" : {
                            "$eq" : "xyz"
                        }
                    }, 
                    {
                        "univercity" : {
                            "$eq" : "abc"
                        }
                    }
                ]
            }
        }
    }
}

So, it looks like use of $ne causes Mongo to do something like this psuedo code ...

not educations equalTo "$elemMatch" : {"title" : "xyz", "passed_year" : 2005, "univercity" : "abc"}

... i.e. it treats the elemMatch clause as if it is the RHS of an equality operation whereas use of $not causes Mongo to actually evaluate the elemMatch clause.

Upvotes: 1

Related Questions