mongodb - How to invert query with $not?

Question

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

I think the last query below is explicit, I would of course expect to get 225506-125102 documents, and not 0. So 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

The problem

$not can't be used to simply invert an existing expression.

var originalSelection = {code: /^AAA/, 'author.name': {'$in': ['BB', 'CC']}}
var invalidSelection = {$not:originalSelection}

"unknown top level operator: $not"

---

The solution

The simple solution is to use the $nor operator, passing the expression as a single-item array.

var inverseSelection = {$nor:[originalSelection]}

This works because NOR of a single operand is equivalent to NOT.

Answer 2

What is the query you're running that's not giving the right results? What version of MongoDB are you using? Your $not query is not a valid query 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's 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" }

The easy way to write down this query is to use DeMorgan's Laws: the complement of an intersection (and) is the union of the complements. Since you are searching for documents that don't satisfy (code is AAA) and (name is one of BB or CC), the condition they satisfy is not ((code is AAA) and (name is one of BB or CC)) = (code is not AAA) or (name is not BB or CC).