MongoDB using NOT and AND together

Question

I'm trying to negate an $and clause with MongoDB and I'm getting a MongoError: invalid operator: $and message back. Basically what I want to achieve is the following:

query = { $not: { $and: [{institution_type:'A'}, {type:'C'}] } }

Is this possible to express in a mongo query?

Here is a sample collection:

{ "institution_type" : "A", "type" : "C" }
{ "institution_type" : "A", "type" : "D" }
{ "institution_type" : "B", "type" : "C" }
{ "institution_type" : "B", "type" : "D" }

What I want to get back is the following:

{ "institution_type" : "A", "type" : "D" }
{ "institution_type" : "B", "type" : "C" }
{ "institution_type" : "B", "type" : "D" }

Answer 1

You're looking for NOT (A AND C), which is equivalent to NOT A OR NOT C:

db.collection.find({
  "$or": [
    {"institution_type": {"$ne": "A"}},
    {"type": {"$ne": "C"}}
  ]
})

MongoDB also has a $nor logical operator that "performs a logical NOR operation on an array of one or more query expression and selects the documents that fail all the query expressions in the array", so an equivalent query would be:

db.collection.find({
  "$nor": [
    {"institution_type": "A"},
    {"type": "C"}
  ]
})

The accepted answer recommends using a $where operator, but that is unnecessary here and taxes performance.

Answer 2

Not a standard negation that I can see, you would need a $where operator styled query with JavaScript:

db.collection.find(function(){ 
    return !( this.institution_type == "A" && this.type == "C" ) 
})

Not the best as that scans a whole collection, but the negation in your logic requires this as you need to test both values.

Or the essentially equivalent aggregation operation:

db.collection.aggregate([
    { "$project": {
        "institution_type": 1,
        "type": 1,
        "notmatched": {
            "$not": {
                "$and": [
                    { "eq": [ "$institution_type", "A" ] },
                    { "eq": [ "$type", "C" ] }
                ]
            }
        }
    }},
    { "$match": { 
        "notmatched": true
    }}
])

Only just realized this was also a valid form by including the negation on the inside:

db.collection.find({
    "$or": [
        { "institution_type": { "$ne": "A" } }, 
        { "type": { "$ne": "C" }},
    ]
})

Where that essentially negates the combination of "A" and "C" in the same document just as the other logic does with a wrapping "not" condition.

---

Orig

In MongoDB queries the $and is actually implicit since that is the default comparison of terms in a query. You only need to use $and when you are looking for multiple conditions on the same field.

But your query is a simple inequality match:

db.collection.find({
    "institution_type": { "$ne": "bank" },
    "type": { "$ne": "account_type" }
})

So you use the $ne operator to negate your terms from a match, where as already stated the $and is implicit so both conditions must apply.

So with sample data:

{ "institution_type" : "bank", "type" : "account_type" }
{ "institution_type" : "school", "type" : "account_type" }
{ "institution_type" : "school", "type" : "account" }

The query only returns the row that does not meet both conditions:

{ "institution_type" : "school", "type" : "account" }

Your comment says you wan "OR" which you do not specify in your query as you are using "AND", so perhaps you are expecting that "OR" is implicit which is the opposite of what I have told you is the case.

An $or condition needs to be explicitly applied:

db.collection.find({
    "$or": [
        { "inititution_type": { "$ne": "bank" } },
        { "type": { "$ne": "account_type" } }
    ]
})

Which is actually double negation and would return all results.

Perhaps you mean $nor:

db.data.find({
    "$nor": [
        { "inititution_type": "bank" },
        { "type": "account_type"  }
    ]
})

But actually that is logically the same as the first query I gave you.