mongodb - How to invert query with $not?

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).


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.


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

Tags:

Mongodb

Recent Posts