Mongodb Query - Check Not Null, Empty and Not set with examples

Mongodb Query - Check Not Null, Empty and Not set with examples

Document contains properties or fields. First, Let’s get all documents in a MongoDB using db.employees.find()

[
  {
    _id: ObjectId("65165ba2bf2c71bd2475cec6"),
    employee_id: 1,
    name: 'john kumar',
    salary: 6000
  },
  {
    _id: ObjectId("65165ba2bf2c71bd2475cec7"),
    employee_id: 2,
    name: 'abc def',
    salary: 4000
  },
  {
    _id: ObjectId("65165ba2bf2c71bd2475cec8"),
    employee_id: 3,
    name: 'zyx kra',
    salary: 2000
  },
  {
    _id: ObjectId("65165ba2bf2c71bd2475cec9"),
    employee_id: 4,
    name: 'test user'
  },
  {
    _id: ObjectId("65165ba2bf2c71bd2475ceca"),
    employee_id: 5,
    name: 'test user',
    salary: ''
  },
  {
    _id: ObjectId("65165ba2bf2c71bd2475cecb"),
    employee_id: 6,
    name: 'test user',
    salary: null
  },
  {
    _id: ObjectId("6516b11ebf2c71bd2475cecd"),
    employee_id: 7,
    name: 'test user',
    salary: ''
  }
]

Above data is in Mongodb with

  • employee_id = 5 & 7 documents with salary is empty
  • employee_id = 6 document with salary is null
  • employee_id =4 document with salary field not exist

Below are to check

  • field does not exists or set in a document
  • fields contains null value
  • fields contains empty value

There are two Operators to check above two on the fields.

  • $exists operator: It checks the documents, contains the specific field or key exists. true: field exists and value contains valid or null values false: field does not set on the document.

Syntax:

  {field: {$exists: true/false}}
  • $ne operator: It checks field contains specific value. It is an alias for not equal
  {field: {$ne: value}}

Value can be null or empty or specific value

  • $nin operator: It used to filter the documents and checks the value is not in the given array of values. Includes an field does not exists
  { field: { $nin: [ <value1>, <value2> ... <valueN> ] } }

How to query for documents where field is not set.

To check field not exists in document, use $exists operator with false value as given below

db.employees.find({ salary: { $exists: false } });

Output:

[
  {
    _id: ObjectId("65165ba2bf2c71bd2475cec9"),
    employee_id: 4,
    name: 'test user'
  }
]

For searching and filter null values, use $ne operator with null value

db.employees.find({ salary: { $ne: null } });

Output:

[
  {
    _id: ObjectId("65165ba2bf2c71bd2475cec6"),
    employee_id: 1,
    name: 'john kumar',
    salary: 6000
  },
  {
    _id: ObjectId("65165ba2bf2c71bd2475cec7"),
    employee_id: 2,
    name: 'abc def',
    salary: 4000
  },
  {
    _id: ObjectId("65165ba2bf2c71bd2475cec8"),
    employee_id: 3,
    name: 'zyx kra',
    salary: 2000
  },
  {
    _id: ObjectId("65165ba2bf2c71bd2475ceca"),
    employee_id: 5,
    name: 'test user',
    salary: ''
  },
  {
    _id: ObjectId("6516b11ebf2c71bd2475cecd"),
    employee_id: 7,
    name: 'test user',
    salary: ''
  }
]

For filtering empty value fields, use $ne operator with empty value(”) value

db.employees.find({"salary":{$ne:"""}})

Output:

[
  {
    _id: ObjectId("65165ba2bf2c71bd2475cec6"),
    employee_id: 1,
    name: 'john kumar',
    salary: 6000
  },
  {
    _id: ObjectId("65165ba2bf2c71bd2475cec7"),
    employee_id: 2,
    name: 'abc def',
    salary: 4000
  },
  {
    _id: ObjectId("65165ba2bf2c71bd2475cec8"),
    employee_id: 3,
    name: 'zyx kra',
    salary: 2000
  },
  {
    _id: ObjectId("65165ba2bf2c71bd2475cec9"),
    employee_id: 4,
    name: 'test user'
  },
  {
    _id: ObjectId("65165ba2bf2c71bd2475cecb"),
    employee_id: 6,
    name: 'test user',
    salary: null
  }
]

use below syntax to get documents with null field values.It includes the documents that does not contains field names.

db.employees.find({ salary: null });

Output:

[
  {
    _id: ObjectId("65165ba2bf2c71bd2475cec9"),
    employee_id: 4,
    name: 'test user'
  },
  {
    _id: ObjectId("65165ba2bf2c71bd2475cecb"),
    employee_id: 6,
    name: 'test user',
    salary: null
  }

Mongodb query to filter records with not equal, null or empty ot n ot

null value indicates the field value contains null or absent value. empty value says that value exists and empty.

To check null, empty and field not exist documents

Here use $and operator for check not in null and empty string and field exists

db.employees.find({
  $and: [{ salary: { $nin: ["", null] } }, { salary: { $exists: true } }],
});

Output:

[
  {
    _id: ObjectId("65165ba2bf2c71bd2475cec6"),
    employee_id: 1,
    name: 'john kumar',
    salary: 6000
  },
  {
    _id: ObjectId("65165ba2bf2c71bd2475cec7"),
    employee_id: 2,
    name: 'abc def',
    salary: 4000
  },
  {
    _id: ObjectId("65165ba2bf2c71bd2475cec8"),
    employee_id: 3,
    name: 'zyx kra',
    salary: 2000
  }
]