Syntax error How to query for records where field is null or not set in MongoDB?

How to query for records where field is null or not set in MongoDB?

Let us work around two cases −

Case 1 − The syntax is as follows when the field is present and set to null.

db.yourCollectionName.count({yourFieldName: null});

Case 1 − The syntax is as follows when the field is not present and not set.

db.yourCollectionName.count({yourFieldName: {$exists: false}});

To understand both the above syntaxes, let us create a collection with the document. The query to create a collection with a document is as follows −

> db.fieldIsNullOrNotSetDemo.insertOne({"EmployeeName":"Larry","EmployeeAge":null,"EmployeeSalary":18500});
{
   "acknowledged" : true,
   "insertedId" : ObjectId("5c8a995c6cea1f28b7aa07fe")
}
> db.fieldIsNullOrNotSetDemo.insertOne({"EmployeeName":"Bob","EmployeeAge":21,"EmployeeSalary":23500});
{
   "acknowledged" : true,
   "insertedId" : ObjectId("5c8a99836cea1f28b7aa07ff")
}

> db.fieldIsNullOrNotSetDemo.insertOne({"EmployeeName":"Carol","EmployeeSalary":45500});
{
   "acknowledged" : true,
   "insertedId" : ObjectId("5c8a999b6cea1f28b7aa0800")
}
> db.fieldIsNullOrNotSetDemo.insertOne({"EmployeeName":"Mike","EmployeeAge":null,"EmployeeSalary":45500});
{
   "acknowledged" : true,
   "insertedId" : ObjectId("5c8a99bb6cea1f28b7aa0801")
}
> db.fieldIsNullOrNotSetDemo.insertOne({"EmployeeName":"Ramit","EmployeeSalary":85500});
{
   "acknowledged" : true,
   "insertedId" : ObjectId("5c8a99d76cea1f28b7aa0802")
}

Display all documents from a collection with the help of find() method. The query is as follows −

> db.fieldIsNullOrNotSetDemo.find().pretty();

The following is the output −

{
   "_id" : ObjectId("5c8a995c6cea1f28b7aa07fe"),
   "EmployeeName" : "Larry",
   "EmployeeAge" : null,
   "EmployeeSalary" : 18500
}
{
   "_id" : ObjectId("5c8a99836cea1f28b7aa07ff"),
   "EmployeeName" : "Bob",
   "EmployeeAge" : 21,
   "EmployeeSalary" : 23500
}
{
   "_id" : ObjectId("5c8a999b6cea1f28b7aa0800"),
   "EmployeeName" : "Carol",
   "EmployeeSalary" : 45500
}
{
   "_id" : ObjectId("5c8a99bb6cea1f28b7aa0801"),
   "EmployeeName" : "Mike",
   "EmployeeAge" : null,
   "EmployeeSalary" : 45500
}
{
   "_id" : ObjectId("5c8a99d76cea1f28b7aa0802"),
   "EmployeeName" : "Ramit",
   "EmployeeSalary" : 85500
}

Case 1 −

The field EmployeeAge is present and set to null.

The query is as follows −

> db.fieldIsNullOrNotSetDemo.count({EmployeeAge: null});

The following is the output −

4

Case 2 −

The field ‘EmployeeAge’ is not present and not set. The query is as follows −

> db.fieldIsNullOrNotSetDemo.count({EmployeeAge: {$exists: false}});

The following is the output −

2
Updated on: 2019-07-30T22:30:25+05:30

767 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements