Mongodb - How to invert a query using $ not?

Question

I want to select all documents except those that have a code starting with AAA if the name is also BB or CC.

I think the last request below is explicit, I would certainly expect to receive documents 225506-125102, and not 0. Thus, the result here is definitely unexpected.

> db.amon.find().count() 225506 > db.amon.find({code: /^AAA/, 'author.name': {'$in': ['BB', 'CC']}}).count() 125102 > db.amon.find({$not: {code: /^AAA/, 'author.name': {'$in': ['BB', 'CC']}}}).count() 0 

Answer 1

What query are you using that does not produce the correct results? What version of MongoDB are you using? Your $not request is not a valid request in MongoDB 2.6:

 > db.amon.find({ "$not" : { "code" : /^AAA/, "name" : { "$in" : ["BB", "CC"] } } }) error: { "$err" : "Can't canonicalize query: BadValue unknown top level operator: $not", "code" : 17287 } 

Here is an example that does what you want:

 > db.amon.find().pretty() { "_id" : ObjectId("53ea66bdf9b63e0dd3ca1a18"), "code" : "AAA", "name" : "AA" } { "_id" : ObjectId("53ea66c1f9b63e0dd3ca1a19"), "code" : "AAA", "name" : "BB" } { "_id" : ObjectId("53ea66c3f9b63e0dd3ca1a1a"), "code" : "AAA", "name" : "CC" } { "_id" : ObjectId("53ea66d3f9b63e0dd3ca1a1b"), "code" : "BBB", "name" : "AA" } { "_id" : ObjectId("53ea66d6f9b63e0dd3ca1a1c"), "code" : "BBB", "name" : "BB" } { "_id" : ObjectId("53ea66daf9b63e0dd3ca1a1d"), "code" : "BBB", "name" : "CC" } > db.amon.find({ "$or" : [ { "code" : { "$not" : /^AAA/ } }, { "name": { "$not" : { "$in" : ["BB", "CC"] } } } ] }) { "_id" : ObjectId("53ea66bdf9b63e0dd3ca1a18"), "code" : "AAA", "name" : "AA" } { "_id" : ObjectId("53ea66d3f9b63e0dd3ca1a1b"), "code" : "BBB", "name" : "AA" } { "_id" : ObjectId("53ea66d6f9b63e0dd3ca1a1c"), "code" : "BBB", "name" : "BB" } { "_id" : ObjectId("53ea66daf9b63e0dd3ca1a1d"), "code" : "BBB", "name" : "CC" } 

An easy way to record this query is to use DeMorgan Laws : the complement to the intersection (s) is the union of the additions, Since you are looking for documents that do not satisfy (AAA code) and (the name is one of BB or CC), the condition that they satisfy is not is ((code is AAA) and (name is one of BB or CC)) = (code is not AAA) or (name is not BB or CC).

Answer 2

Use $ ne or $ nin See link operators

 db.amon.find().count() 225506 db.amon.find({code: /^AAA/, 'author.name': {'$in': ['BB', 'CC']}}).count() 125102 db.amon.find({$not: {code: /^AAA/, 'author.name': {'$in': ['BB', 'CC']}}}).count() 0 could be db.amon.find({code: { $ne : /^AAA/}, 'author.name': {'$nin': ['BB', 'CC']}}}).count() and if you want this to work as index only then create a compond index on the 2 fields db.amon.find({code: { $ne : /^AAA/}, 'author.name': {'$nin': ['BB', 'CC']}}},{code:1,_id:0}).count() and if you want it to work on a sharded cluster db.amon.find({code: { $ne : /^AAA/}, 'author.name': {'$nin': ['BB', 'CC']}}},{code:1,_id:0}).explain().n 

Answer 3

Just going to the description of the problem, I think this query should work for you:

 db.amon.find({$and:[{code: {$not:/^AAA/}},{ 'author.name': {'$in': ['BB', 'CC']}}]}).count();