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

MongoDBDatabaseBig Data Analytics

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
Published on 29-Mar-2019 15:02:22