MongoDB not equal: $ne and $nin
Query MongoDB for values that are not equal to something with $ne and $nin, and why both also return documents where the field is missing.
Quick Answer
Use $ne for one value and $nin for a list:
db.orders.find({ status: { $ne: 'shipped' } })
db.orders.find({ status: { $nin: ['shipped', 'cancelled'] } })Both also match documents that don't have a status field at all. Add $exists: true if you don't want those.
$ne: not equal to one value
db.orders.find({ status: { $ne: 'shipped' } })
db.orders.find({ total: { $ne: 0 } })$ne works for strings, numbers, dates, ObjectIds and null. Comparisons are type-aware: { total: { $ne: 0 } } still matches a document where total is the string "0".
In VisuaLeaf
Check what $ne really matches
- Open the collection and type the filter, such as
{ status: { $ne: 'active' } }, in the Query field. Field names autocomplete from the collection's schema. - Click Run Count. Then count
{ status: { $exists: false } }the same way: that number is how many of your matches have no status at all. - If you need to rule those out, add the second condition in the Query Builder with an And group instead of nesting braces by hand.
Two quick counts tell you whether missing fields are inflating a $ne result before it reaches a report. See the Mongo Query docs.
The missing-field trap
This is the part that causes wrong results. Take five orders where one has no status field and one has status: null:
| Query | Matches |
|---|---|
{ status: { $ne: 'shipped' } } | pending, cancelled, missing, null |
{ status: { $nin: ['shipped', 'cancelled'] } } | pending, missing, null |
{ status: { $nin: [...], $exists: true } } | pending, null |
{ status: { $ne: null } } | shipped, pending, cancelled (excludes missing and null) |
So if you mean "has a status, and it isn't shipped", say so:
db.orders.find({ status: { $exists: true, $ne: 'shipped' } })And { field: { $ne: null } } is the shortest way to ask for documents where a field is present and not null. More on this in MongoDB query mistakes that return wrong results.
$nin: not in a list
db.orders.find({ status: { $nin: ['shipped', 'cancelled'] } })
db.users.find({ _id: { $nin: [ObjectId('...'), ObjectId('...')] } })$nin is the opposite of $in, and like $ne it includes documents without the field.
Not equal on arrays
On an array field, $ne means "no element equals this value", not "the array isn't exactly this":
// documents whose tags array does not contain 'archived'
db.posts.find({ tags: { $ne: 'archived' } })
// none of these tags
db.posts.find({ tags: { $nin: ['archived', 'draft'] } })To exclude one exact array, compare to the whole array: { tags: { $ne: ['a', 'b'] } }. Order matters in that comparison.
$not, $nor and comparing two fields
| Operator | Use it for | Example |
|---|---|---|
$not | Negating another operator, such as a regex | { name: { $not: /^test/ } } |
$nor | None of several conditions on different fields | { $nor: [{ status: 'shipped' }, { flagged: true }] } |
$expr | Two fields in the same document that differ | { $expr: { $ne: ['$billingZip', '$shippingZip'] } } |
$expr with $ne is the only way to compare one field to another. $expr explained covers it in depth.
What we found testing this: $ne, $nin and indexes
A common claim is that $ne can't use an index, or that an indexed $ne is no better than a collection scan. We tested 100,000 users with an index on status: 81,000 active, 5,000 banned, 4,000 pending, and 10,000 with no status field.
| Query | Matches | Keys read | Docs fetched |
|---|---|---|---|
{ status: { $ne: 'active' } } | 19,000 | 19,001 | 19,000 |
{ status: { $nin: ['active', 'banned'] } } | 14,000 | 14,002 | 14,000 |
{ status: { $exists: true, $ne: 'active' } } | 9,000 | 19,001 | 19,000 |
{ status: { $nin: ['active', null] } } | 9,000 | 9,002 | 9,000 |
{ status: { $in: ['banned', 'pending'] } } | 9,000 | 9,000 | 9,000 |
$nedoes use the index, as two ranges. The plan's bounds were[MinKey, "active")and("active", MaxKey], so it skips the excluded value entirely and reads about one key per result. It's slow only when the value you exclude is rare and most of the collection matches.$exists: truecosts a second pass. Documents with no field are indexed asnull, so the index can't tell "missing" apart from "present". The query read 19,001 keys and fetched 19,000 documents to return 9,000.- Use
$nin: [value, null]instead. It excludes the value, explicit nulls and missing fields in one index range: 9,002 keys for 9,000 results. Only use it if you also want explicitnullvalues excluded. - Arrays:
{ status: { $ne: 'active' } }did not match a document withstatus: ['active', 'banned'], because one element equals'active'.
Filters are written the same way in every driver. In Node.js: collection.find({ status: { $ne: 'banned' } }). In PyMongo: collection.find({"status": {"$ne": "banned"}}), with the $ keys as quoted strings.
Frequently Asked Questions
Does $ne match documents where the field doesn't exist?
Yes. Missing fields are not equal to anything, so they match $ne and $nin. To exclude them, $nin: [value, null] is cheaper than adding $exists: true, because it stays a single index range.
Can $ne use an index?
Yes. MongoDB scans the index as two ranges on either side of the excluded value. It reads roughly one key per matching document, so it is only slow when most of the collection matches.
How do I find documents where a field is not null in MongoDB?
Use { field: { $ne: null } }. It excludes both documents where the field is null and documents where it is missing.
What is the difference between $ne and $not?
$ne compares to a value. $not negates another operator expression or a regex, for example { name: { $not: /^test/ } }.
How do I check that two fields are not equal?
Use $expr: { $expr: { $ne: ["$fieldA", "$fieldB"] } }.