MongoDB - Query for Null and Missing fields

MongoDB

mongodb logo - tutorial - dyclassroom

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"
}