Docs Menu
Docs Home
/
MongoDB Manual
/ /

Query for Null or Missing Fields

On this page

  • Equality Filter
  • Non-Equality Filter
  • Type Check
  • Existence Check
  • Query for Null or Missing Fields with MongoDB Atlas

You can query for null or missing fields in MongoDB using the following methods:


➤ Use the Select your language drop-down menu in the upper-right to set the language of the following examples or select MongoDB Compass.


Different query operators in MongoDB treat null values differently.

This page provides examples of operations that query for null values using the db.collection.find() method in mongo.

The examples on this page use the inventory collection. Connect to a test database in your MongoDB instance then create the inventory collection:

This page provides examples of operations that query for null values using MongoDB Compass.

The examples on this page use the inventory collection. Connect to a test database in your MongoDB instance then create the inventory collection:

This page provides examples of operations that query for null values using the MongoCollection.Find() method in the MongoDB C# Driver.

The examples on this page use the inventory collection. Connect to a test database in your MongoDB instance then create the inventory collection:

This page provides examples of operations that query for null values using the Collection.Find function in the MongoDB Go Driver.

The examples on this page use the inventory collection. Connect to a test database in your MongoDB instance then create the inventory collection:

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

The examples on this page use the inventory collection. Connect to a test database in your MongoDB instance then create the inventory collection:

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

Tip

The driver provides com.mongodb.client.model.Filters helper methods to facilitate the creation of filter documents. The examples on this page use these methods to create the filter documents.

The examples on this page use the inventory collection. Connect to a test database in your MongoDB instance then create the inventory collection:

This page provides examples of operations that query for null values using the motor.motor_asyncio.AsyncIOMotorCollection.find method in the Motor driver.

The examples on this page use the inventory collection. Connect to a test database in your MongoDB instance then create the inventory collection:

This page provides examples of operations that query for null values using the Collection.find() method in the MongoDB Node.js Driver.

The examples on this page use the inventory collection. Connect to a test database in your MongoDB instance then create the inventory collection:

This page provides examples of operations that query for null values using the MongoDB\\Collection::find() method in the MongoDB PHP Library.

The examples on this page use the inventory collection. Connect to a test database in your MongoDB instance then create the inventory collection:

This page provides examples of operations that query for null values using the pymongo.collection.Collection.find method in the PyMongo Python driver.

The examples on this page use the inventory collection. Connect to a test database in your MongoDB instance then create the inventory collection:

This page provides examples of operations that query for null values using the Mongo::Collection#find() method in the MongoDB Ruby Driver.

The examples on this page use the inventory collection. Connect to a test database in your MongoDB instance then create the inventory collection:

This page provides examples of operations that query for null values using the collection.find() method in the MongoDB Scala Driver.

The examples on this page use the inventory collection. Connect to a test database in your MongoDB instance then create the inventory collection:

Important

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

Important

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

Important

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

Important

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

Important

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

Important

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

db.inventory.insertMany([
{ _id: 1, item: null },
{ _id: 2 }
])
[
{ "_id": 1, "item": null },
{ "_id": 2 }
]

For instructions on inserting documents in MongoDB Compass, see Insert Documents.

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

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

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

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

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

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

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

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

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

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

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

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

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

db.inventory.find( { item: null } )

Copy the following query filter document into the query bar and click Find:

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

The query returns both documents in the collection.

To query for fields that exist and are not null, use the { $ne : null } filter. The { item : { $ne : null } } query matches documents where the item field exists and has a non-null value.

db.inventory.find( { item: { $ne : null } } )
{ item: { $ne : null } }
var filter = Builders<BsonDocument>.Filter.Ne("item", BsonNull.Value);
var result = collection.Find(filter).ToList();
cursor, err := coll.Find(
context.TODO(),
bson.D{
{"item", bson.D{"$ne": nil}},
})
db.inventory.find( { item: { $ne : nul l} } )
collection.find($ne("item", null));
cursor = db.inventory.find( { "item": { "$ne": None } } )
const cursor = db.collection('inventory')
.find({ item: { $ne : null }
});
$cursor = $db->inventory->find(['item' => ['$ne' => null ]]);
cursor = db.inventory.find( { "item": { "$ne": None } } )
client[:inventory].find(item: { '$ne' => nil })
collection.find($ne("item", null));

The { item : { $type: 10 } } query matches only documents that contain the item field whose value is null; i.e. the value of the item field is of BSON Type Null (type number 10) :

The { item : { $type: 10 } } query matches only documents that contain the item field whose value is null; i.e. the value of the item field is of BSON Type Null (type number 10) :

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

The following query matches only documents that contain the item field whose value is of BSON Type Null (type number 10) :

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

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

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

The { item : { $type: 10 } } query matches only documents that contain the item field whose value is null; i.e. the value of the item field is of BSON Type Null (type number 10) :

The { item : { $type: 10 } } query matches only documents that contain the item field whose value is null; i.e. the value of the item field is of BSON Type Null (type number 10) :

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

The { item : { $type: 10 } } query matches only documents that contain the item field whose value is null; i.e. the value of the item field is of BSON Type Null (type number 10) :

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

db.inventory.find( { item : { $type: 10 } } )

Copy the following query filter document into the query bar and click Find:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

db.inventory.find( { item : { $exists: false } } )

Copy the following query filter document into the query bar and click Find:

{ item : { $exists: false } }
Query for null value
var filter = Builders<BsonDocument>.Filter.Exists("item", false);
var result = collection.Find(filter).ToList();
cursor, err := coll.Find(
context.TODO(),
bson.D{
{"item", bson.D{
{"$exists", false},
}},
})
findPublisher = collection.find(exists("item", false));
findIterable = collection.find(exists("item", false));
cursor = db.inventory.find({"item": {"$exists": False}})
const cursor = db.collection('inventory').find({
item: { $exists: false }
});
$cursor = $db->inventory->find(['item' => ['$exists' => false]]);
cursor = db.inventory.find({"item": {"$exists": False}})
client[:inventory].find(item: { '$exists' => false })
findObservable = collection.find(exists("item", exists = false))

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

Tip

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, see Query for Null or Missing Fields.

The example in this section uses the sample training dataset. To learn how to load the sample dataset into your MongoDB Atlas deployment, see Load Sample Data.

To query for a null or missing field in MongoDB Atlas, follow these steps:

1
  1. In the Atlas UI, click Database in the sidebar.

  2. For the database deployment that contains the sample data, click Browse Collections.

  3. In the left navigation pane, select the sample_training database.

  4. Select the companies collection.

2

Click the Insert Document button to display the dialog box, and then click Insert to insert a document with only the _id field.

3

To find a document that contains a null or missing value, specify a query filter document in the Filter field. A query filter document uses query operators to specify search conditions.

Different query operators in MongoDB treat null values differently. To apply a query filter, copying each of the following documents into the Filter search bar and click Apply.

Use the following query filter to match documents that either contain a description field with a null value or do not contain the description field:

{ description : null }

Use the following query filter to match only documents that contain a description field with a null value. This filter specifies that the value of the field must be BSON Type Null (BSON Type 10):

{ description : { $type: 10 } }

Use the following query filter to match only documents that do not contain the description field. Only the document that you inserted earlier should appear:

{ description : { $exists: false } }

Back

Project Fields to Return from Query