132

How to search for documents in a collection that are missing a certain field in MongoDB?

Somnath Muluk
  • 55,015
  • 38
  • 216
  • 226
bcmcfc
  • 25,966
  • 29
  • 109
  • 181

3 Answers3

199

Yeah, it's possible using $exists:

db.things.find( { a : { $exists : false } } ); // return if a is missing

When is true, $exists matches the documents that contain the field, including documents where the field value is null. If is false, the query returns only the documents that do not contain the field.

Somnath Muluk
  • 55,015
  • 38
  • 216
  • 226
Andrew Orsich
  • 52,935
  • 16
  • 139
  • 134
  • 2
    Be warned, `$exist` queries cannot use indexes (see http://www.mongodb.org/display/DOCS/Advanced+Queries#AdvancedQueries-%24exists). – Theo Apr 19 '11 at 17:03
  • 4
    @Theo: Starting from MongoDB 2.0 $exists is able to use indexes (https://jira.mongodb.org/browse/SERVER-393) – Dmitry Schetnikovich Nov 23 '11 at 15:48
  • I was after this for Mongoid to use in a scope. Looks like this > ```scope :without_recommendation, :where => {:recommendation => {"$exists"=>false}}``` – genkilabs Jun 15 '12 at 01:15
60

If you don't care if the field is missing or null (or if it's never null) then you can use the slightly shorter and safer:

db.things.find( { a : null } ); // return if a is missing or null

It's safer because $exists will return true even if the field is null, which often is not the desired result and can lead to an NPE.

nilskp
  • 3,097
  • 1
  • 30
  • 34
  • 2
    However, somebody reading the code might interpret it as the field has to be equal to `null` and not missing. This is actually unexpected behavior, because you wouldn't be able to do the same for `0` (which is also `false`), so `null` is kind of the exception here. Therefore, best practice is the more readable answer using `$exists: false` which is not ambiguous. Remember, your slightly shorter variant is not actually shorter if you need to have that comment behind it! – Yeti Sep 03 '18 at 17:01
  • 1
    @Yeti if my goal is to find all objects that are missing a value for field `a`, either because `a` is `null` or because `a` is missing, then `$exists` is not good enough, as it won't catch the cases where `a` is `null`. – nilskp Sep 07 '18 at 15:16
  • I think this is the best answer because `{ $exists: false }` does not use index if any, but `{ $eq: false }` will if there is one. – Alex Jones May 15 '23 at 08:35
2

just for the reference here, for those of you using mongoose (v6) and trying to use the $exists to find a field that is not defined in your mongoose schema, mongoose v6 will escape it.

see here https://mongoosejs.com/docs/migrating_to_6.html#strictquery-is-removed-and-replaced-by-strict

for example:

const userSchema = new Schema({ name: String });
const User = mongoose.model('User', userSchema);

// By default, this is equivalent to `User.find()` because Mongoose filters out `notInSchema`
await User.find({ notInSchema: 1 });

// Set `strictQuery: false` to opt in to filtering by properties that aren't in the schema
await User.find({ notInSchema: 1 }, null, { strictQuery: false });
// equivalent:
await User.find({ notInSchema: 1 }).setOptions({ strictQuery: false });
Normal
  • 1,616
  • 15
  • 39