Query for Null or Missing Fields
On this page
You can query for null
or missing fields in MongoDB
using the following methods:
Your programming language's driver.
The MongoDB Atlas UI. To learn more, see Query for Null or Missing Fields with MongoDB Atlas.
➤ 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 mongosh
.
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()
Equality Filter
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 } )
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.
Non-Equality Filter
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));
Type Check
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
(BSON Type 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
(BSON Type 10):
The Type("item", BsonType.Null)
query using the
FilterDefinitionBuilder.Type()
method matches only documents that contain the item
field whose value is null
. The value of the item
field
is of BSON Type Null
(BSON Type 10):
The following query matches only
documents that contain the item
field whose value is of
BSON Type Null
(BSON Type 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
(BSON Type 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
(BSON Type 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
(BSON Type 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
(BSON Type 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
(BSON Type 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
(BSON Type 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
(BSON Type 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
(BSON Type 10):
db.inventory.find( { item : { $type: 10 } } )
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
.
Existence Check
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 } }
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.
[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. |
Query for Null or Missing Fields with MongoDB Atlas
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:
Navigate to the collection.
In the Atlas UI, click Database in the sidebar.
For the database deployment that contains the sample data, click Browse Collections.
In the left navigation pane, select the
sample_training
database.Select the
companies
collection.
Specify a query filter document.
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 } }