- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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
Advertisements