MongoDB - Query for Null and Missing fields

In this MongoDB tutorial we will learn to query for Null and missing fields.

Login to your MongoDB server and insert the following documents.

For this tutorial I will insert the documents in the deliveryAddress collection.

> db.deliveryAddress.insertMany([
  {
    "addressLine1": "House #1, 2nd Street, 1st Main Road",
    "addressLine2": "Opposite Bakery",
    "city": "Bangalore",
    "state": "KA",
    "country": "India",
    "pincode": "560001",
    "contactphone": "919800000000",
    "contactperson": "John Doe"
  },
  {
    "addressLine1": "House #20, 3rd Street, 5th Main Road",
    "addressLine2": null,
    "city": "Bangalore",
    "state": "KA",
    "country": "India",
    "pincode": "560001",
    "contactperson": "Jane Doe"
  },
  {
    "addressLine1": "House #20, 5th Street, 10th Main Road",
    "addressLine2": null,
    "city": "Bangalore",
    "state": "KA",
    "country": "India",
    "pincode": "560001",
    "contactphone": null,
    "contactperson": "Jim Doe"
  }
])

Select all documents having null field

In the following example we are fetching all the documents that have addressLine2 field set to null value.

> db.deliveryAddress.find({ "addressLine2": null }).pretty()


{
  "_id" : ObjectId("5d76170f89ccf6fae0c7974b"),
  "addressLine1" : "House #20, 3rd Street, 5th Main Road",
  "addressLine2" : null,
  "city" : "Bangalore",
  "state" : "KA",
  "country" : "India",
  "pincode" : "560001",
  "contactperson" : "Jane Doe"
}
{
  "_id" : ObjectId("5d76170f89ccf6fae0c7974c"),
  "addressLine1" : "House #20, 5th Street, 10th Main Road",
  "addressLine2" : null,
  "city" : "Bangalore",
  "state" : "KA",
  "country" : "India",
  "pincode" : "560001",
  "contactphone" : null,
  "contactperson" : "Jim Doe"
}

The $exists operator

To check if a document does or does not contains a given field we use the $exists operator.

Syntax

We use the following syntax to fetch all the documents of a given collection that does contains a given field.

db.collectionName.find({ field: { $exists: true } })

We use the following syntax to fetch all the documents of a given collection that does not contains a given field.

db.collectionName.find({ field: { $exists: false } })

Select all the documents without a given field

In the following example we are fetching all the documents without contactphone field.

> db.deliveryAddress.find({ "contactphone": { $exists: false } }).pretty()


{
  "_id" : ObjectId("5d76170f89ccf6fae0c7974b"),
  "addressLine1" : "House #20, 3rd Street, 5th Main Road",
  "addressLine2" : null,
  "city" : "Bangalore",
  "state" : "KA",
  "country" : "India",
  "pincode" : "560001",
  "contactperson" : "Jane Doe"
}