Query for Null or Missing Fields

This page provides examples in:

Different query operators in MongoDB treat null values differently.

  • Mongo Shell
  • Compass
  • Python
  • Java (Sync)
  • Node.js
  • Other
    • PHP
    • Motor
    • Java (Async)
    • C#
    • Perl
    • Ruby
    • Scala
    • Go

This page provides examples of operations that query for null values using thedb.collection.find() method in themongo shell. The examples on this page use theinventory collection. To populate the inventorycollection, run the following:

This page provides examples of operations that query for null values usingMongoDB Compass. The examples on thispage use the inventory collection. Populate theinventory collection with the following documents:

This page provides examples of operations that query for null values using thepymongo.collection.Collection.find() method in thePyMongoPython driver. The examples on this page use the inventorycollection. To populate the inventory collection, run thefollowing:

This page provides examples of operations that query for null values using thecom.mongodb.client.MongoCollection.find method in the MongoDBJava Synchronous Driver.

Tip

The driver provides com.mongodb.client.model.Filtershelper methods to facilitate the creation of filterdocuments. The examples on this page use these methods tocreate the filter documents.

The examples on this page use the inventorycollection. To populate the inventory collection, run thefollowing:

This page provides examples of operations that query for null values using theCollection.find() method inthe MongoDB Node.js Driver.The examples on this page use the inventory collection. Topopulate the inventory collection, run the following:

This page provides examples of operations that query for null values using theMongoDB\Collection::find()method in theMongoDB PHP Library.The examples on this page use the inventory collection. Topopulate the inventory collection, run the following:

This page provides examples of operations that query for null values using themotor.motor_asyncio.AsyncIOMotorCollection.find()method in the Motordriver. The examples on this page use the inventorycollection. To populate the inventory collection, run thefollowing:

This page provides examples of operations that query for null values using thecom.mongodb.reactivestreams.client.MongoCollection.find)method in the MongoDB Java Reactive Streams Driver.

The examples on this page use the inventorycollection. To populate the inventory collection, run thefollowing:

This page provides examples of operations that query for null values using theMongoCollection.Find()method in theMongoDB C# Driver.The examples on this page use the inventory collection. Topopulate the inventory collection, run the following:

This page provides examples of operations that query for null values using theMongoDB::Collection::find() methodin theMongoDB Perl Driver.The examples on this page use the inventory collection. Topopulate the inventory collection, run the following:

This page provides examples of operations that query for null values using theMongo::Collection#find()method in theMongoDB Ruby Driver.The examples on this page use the inventory collection. Topopulate the inventory collection, run the following:

This page provides examples of operations that query for null values using thecollection.find()(implicite:org.mongodb.scala.bson.DefaultHelper.DefaultsTo[C,TResult],implicitct:scala.reflect.ClassTag[C]):org.mongodb.scala.FindObservable[C]) methodin theMongoDB Scala Driver.The examples on this page use the inventory collection. Topopulate the inventory collection, run the following:

This page provides examples of operations that query for null values using theCollection.Findfunction in theMongoDB Go Driver.The examples on this page use the inventory collection. Topopulate the inventory collection, run the following:

  • Python
  • Motor
  • C#
  • Perl
  • Ruby
  • Other
    • Scala
    • Go

Important

Use None with the PyMongo Python driver toquery for null or missing fields in MongoDB.

Important

Use None with the Motor driver toquery for null or missing fields in MongoDB.

Important

Use BsonNull.Value with the MongoDB C# driver toquery for null or missing fields in MongoDB.

Important

Use undef with the MongoDB Perl driver toquery for null or missing fields in MongoDB.

Important

Use nil with the MongoDB Ruby driver toquery for null or missing fields in MongoDB.

Important

Use BsonNull() with the MongoDB Scala driver to queryfor null or missing fields in MongoDB.

Important

Use nil with the MongoDB Go driver toquery for null or missing fields in MongoDB.

  • Mongo Shell
  • Compass
  • Python
  • Java (Sync)
  • Node.js
  • Other
    • PHP
    • Motor
    • Java (Async)
    • C#
    • Perl
    • Ruby
    • Scala
    • Go
  1. db.inventory.insertMany([
  2.    { _id: 1, item: null },
  3.    { _id: 2 }
  4. ])

You can run the operation in the web shell below:

  1. [
  2.    { _id: 1, item: null },
  3.    { _id: 2 }
  4. ]

For instructions on inserting documents in MongoDB Compass, seeInsert Documents.

  1. db.inventory.insert_many([{"_id": 1, "item": None}, {"_id": 2}])
  1. collection.insertMany(asList(
  2.         Document.parse("{'_id': 1, 'item': null}"),
  3.         Document.parse("{'_id': 2}")
  4. ));
  1. await db.collection('inventory').insertMany([{ _id: 1, item: null }, { _id: 2 }]);
  1. $insertManyResult = $db->inventory->insertMany([
  2.     ['_id' => 1, 'item' => null],
  3.     ['_id' => 2],
  4. ]);
  1. await db.inventory.insert_many([{"_id": 1, "item": None}, {"_id": 2}])
  1. Publisher<Success> insertManyPublisher = collection.insertMany(asList(
  2.         Document.parse("{'_id': 1, 'item': null}"),
  3.         Document.parse("{'_id': 2}")
  4. ));
  1. var documents = new[]
  2. {
  3.     new BsonDocument { { "_id", 1 }, { "item", BsonNull.Value } },
  4.     new BsonDocument { { "_id", 2 } }
  5. };
  6. collection.InsertMany(documents);
  1. $db->coll("inventory")->insert_many( [ { _id => 1, item => undef }, { _id => 2 } ] );
  1. client[:inventory].insert_many([{ _id: 1, item: nil },
  2.                                 { _id: 2 }])
  1. collection.insertMany(Seq(
  2.   Document("""{"_id": 1, "item": null}"""),
  3.   Document("""{"_id": 2}""")
  4. )).execute()
  1. docs := []interface{}{
  2.     bson.D{
  3.         {"_id", 1},
  4.         {"item", nil},
  5.     },
  6.     bson.D{
  7.         {"_id", 2},
  8.     },
  9. }
  10.  
  11. result, err := coll.InsertMany(context.Background(), docs)

Equality Filter

  • Mongo Shell
  • Compass
  • Python
  • Java (Sync)
  • Node.js
  • Other
    • PHP
    • Motor
    • Java (Async)
    • C#
    • Perl
    • Ruby
    • Scala
    • Go

The { item : null } query matches documents that eithercontain the item field whose value is null or thatdo not contain the item field.

The { item : null } query matches documents that eithercontain the item field whose value is null or thatdo not contain the item field.

The { item : None } query matches documents that eithercontain the item field whose value is null or thatdo not contain the item field.

The eq("item", null) query matches documents that eithercontain the item field whose value is null or thatdo not contain the item field.

The { item : null } query matches documents that eithercontain the item field whose value is null or thatdo not contain the item field.

The [ item => undef ] query matches documents that eithercontain the item field whose value is null or thatdo not contain the item field.

The { item : None } query matches documents that eithercontain the item field whose value is null or thatdo not contain the item field.

The eq("item", null) query matches documents that eithercontain the item field whose value is null or thatdo not contain the item field.

The Eq("item", BsonNull.Value) query using the FilterDefinitionBuilder.Eq() methodmatches documents that either contain the item field whosevalue is null or that do not contain the item field.

The { item => undef } query matches documents that eithercontain the item field whose value is null or thatdo not contain the item field.

The { item => nil } query matches documents that eithercontain the item field whose value is nil or thatdo not contain the item field.

The equal("item", BsonNull) query matches documents thateither contain the item field whose value is null _or_that do not contain the item field.

The item => nil query matches documents that eithercontain the item field whose value is nil or thatdo not contain the item field.

  • Mongo Shell
  • Compass
  • Python
  • Java (Sync)
  • Node.js
  • Other
    • PHP
    • Motor
    • Java (Async)
    • C#
    • Perl
    • Ruby
    • Scala
    • Go
  1. db.inventory.find( { item: null } )

Copy the following query filter document into thequery bar and clickFind:

  1. { item: null }

../../_images/compass-find-null-field.png

  1. cursor = db.inventory.find({"item": None})
  1. FindIterable<Document> findIterable = collection.find(eq("item", null));
  1. const cursor = db.collection('inventory').find({
  2.   item: null
  3. });
  1. $cursor = $db->inventory->find(['item' => null]);
  1. cursor = db.inventory.find({"item": None})
  1. FindPublisher<Document> findPublisher = collection.find(eq("item", null));
  1. var filter = Builders<BsonDocument>.Filter.Eq("item", BsonNull.Value);
  2. var result = collection.Find(filter).ToList();
  1. $cursor = $db->coll("inventory")->find( { item => undef } );
  1. client[:inventory].find(item: nil)
  1. var findObservable = collection.find(equal("item", BsonNull()))
  1. cursor, err := coll.Find(
  2.     context.Background(),
  3.     bson.D{
  4.         {"item", nil},
  5.     })

The query returns both documents in the collection.

Type Check

  • Mongo Shell
  • Compass
  • Python
  • Java (Sync)
  • Node.js
  • Other
    • PHP
    • Motor
    • Java (Async)
    • C#
    • Perl
    • Ruby
    • Go

The { item : { $type: 10 } } query matches _only_documents that contain the item field whose value isnull; i.e. the value of the item field is ofBSON Type Null(type number 10) :

The { item : { $type: 10 } } query matches _only_documents that contain the item field whose value isnull; i.e. the value of the item field is ofBSON Type Null(type number 10) :

The { item : { $type: 10 } } query matches _only_documents that contain the item field whose value isnull; i.e. the value of the item field is ofBSON Type Null(type number 10) :

The type("item", BsonType.NULL) query matches _only_documents that contain the item field whose value isnull; i.e. the value of the item field is ofBSON Type Null(type number 10) :

The { item : { $type: 10 } } query matches _only_documents that contain the item field whose value isnull; i.e. the value of the item field is ofBSON Type Null(type number 10) :

The [ item => [ $type => 10 ] ] query matches _only_documents that contain the item field whose value isnull; i.e. the value of the item field is ofBSON Type Null(type number 10) :

The { item : { $type: 10 } } query matches _only_documents that contain the item field whose value isnull; i.e. the value of the item field is ofBSON Type Null(type number 10) :

The type("item", BsonType.NULL) query matches _only_documents that contain the item field whose value isnull; i.e. the value of the item field is ofBSON Type Null(type number 10) :

The Type("item", BsonType.Null) query using theFilterDefinitionBuilder.Type()method matches only documents that contain the itemfield whose value is null; i.e. the value of the itemfield is of BSON Type Null(type number 10) :

The { item => { $type => 10 } } query matches _only_documents that contain the item field whose value isnull; i.e. the value of the item field is ofBSON Type Null(type number 10) :

The { item => { $type => 10 } } query matches _only_documents that contain the item field whose value isnull; i.e. the value of the item field is ofBSON Type Null(type number 10) :

The following query matches _only_documents that contain the item field whose value is ofBSON Type Null(type number 10) :

  • Mongo Shell
  • Compass
  • Python
  • Java (Sync)
  • Node.js
  • Other
    • PHP
    • Motor
    • Java (Async)
    • C#
    • Perl
    • Ruby
    • Scala
    • Go
  1. db.inventory.find( { item : { $type: 10 } } )

Copy the following query filter document into thequery bar and clickFind:

  1. { item : { $type: 10 } }

../../_images/compass-find-null-type-check.png

  1. cursor = db.inventory.find({"item": {"$type": 10}})
  1. findIterable = collection.find(type("item", BsonType.NULL));
  1. const cursor = db.collection('inventory').find({
  2.   item: { $type: 10 }
  3. });
  1. $cursor = $db->inventory->find(['item' => ['$type' => 10]]);
  1. cursor = db.inventory.find({"item": {"$type": 10}})
  1. findPublisher = collection.find(type("item", BsonType.NULL));
  1. var filter = Builders<BsonDocument>.Filter.Type("item", BsonType.Null);
  2. var result = collection.Find(filter).ToList();
  1. $cursor = $db->coll("inventory")->find( { item => { '$type' => 10 } } );
  1. client[:inventory].find(item: { '$type' => 10 })
  1. findObservable = collection.find(bsonType("item", BsonType.NULL))
  1. cursor, err := coll.Find(
  2.     context.Background(),
  3.     bson.D{
  4.         {"item", bson.D{
  5.             {"$type", 10},
  6.         }},
  7.     })

The query returns only the document where the item field has avalue of null.

Existence Check

The following example queries for documents that do not contain afield. [1]

  • Mongo Shell
  • Compass
  • Python
  • Java (Sync)
  • Node.js
  • Other
    • PHP
    • Motor
    • Java (Async)
    • C#
    • Perl
    • Ruby
    • Scala

The { item : { $exists: false } } query matches documentsthat do not contain the item field:

The { item : { $exists: false } } query matches documentsthat do not contain the item field:

The { item : { $exists: False } } query matches documentsthat do not contain the item field:

The exists("item", false) query matches documents thatdo not contain the item field:

The { item : { $exists: false } } query matches documentsthat do not contain the item field:

The [ item => [ $exists => false ] ] query matches documentsthat do not contain the item field:

The { item : { $exists: False } } query matches documentsthat do not contain the item field:

The exists("item", false) query matches documents that donot contain the item field:

The Exists("item", false) query using the FilterDefinitionBuilder.Exists()method matches documents that do not contain the item field:

The { item => { $exists => false } } query matches documentsthat do not contain the item field:

The { item => { $exists => false } } query matches documentsthat do not contain the item field:

The exists("item", exists = false) query matches documentsthat do not contain the item field:

  • Mongo Shell
  • Compass
  • Python
  • Java (Sync)
  • Node.js
  • Other
    • PHP
    • Motor
    • Java (Async)
    • C#
    • Perl
    • Ruby
    • Scala
    • Go
  1. db.inventory.find( { item : { $exists: false } } )

Copy the following query filter document into thequery bar and clickFind:

  1. { item : { $exists: false } }

../../_images/compass-find-null-existence-check.png

  1. cursor = db.inventory.find({"item": {"$exists": False}})
  1. findIterable = collection.find(exists("item", false));
  1. const cursor = db.collection('inventory').find({
  2.   item: { $exists: false }
  3. });
  1. $cursor = $db->inventory->find(['item' => ['$exists' => false]]);
  1. cursor = db.inventory.find({"item": {"$exists": False}})
  1. findPublisher = collection.find(exists("item", false));
  1. var filter = Builders<BsonDocument>.Filter.Exists("item", false);
  2. var result = collection.Find(filter).ToList();
  1. # For boolean values, use boolean.pm for 'true' and 'false'
  2. $cursor = $db->coll("inventory")->find( { item => { '$exists' => false } } );
  1. client[:inventory].find(item: { '$exists' => false })
  1. findObservable = collection.find(exists("item", exists = false))
  1. cursor, err := coll.Find(
  2.     context.Background(),
  3.     bson.D{
  4.         {"item", bson.D{
  5.             {"$exists", false},
  6.         }},
  7.     })

The query only returns the document that does not contain theitem field.

See also

Reference documentation for the $type and$exists operators.

[1]Starting in MongoDB 4.2, users can no longer use the query filter$type: 0 as a synonym for$exists:false. To query for null or missing fields, seeQuery for Null or Missing Fields.