MongoDB $ifNull Operator

While using operators, we can not only make comparisons among operands but we can also search for the missing values, update the records, and many more. The ifNull operator of MongoDB, one such operator, has its special place because it checks if the specific field contains a null value or not. If the particular field does have a null value, we can perform accordingly: replace the value with a new value or make it empty. In this tutorial, you are going to learn the use of the ifNull operator of MongoDB using MongoDB shell. Therefore, you should have MongoDB installed along with its dependencies.

Create Database & Collection

Let us move towards the implementation of some queries in the MongoDB shell to display the usage of the “ifNull” operator of MongoDB. Before that, we are taking a look at the availability of current databases. Currently, we have 3 built-in databases available in our MongoDB as per the “show dbs” instruction. We need to create the dummy database that we will be using in our upcoming examples. For this, we should create a database collection first.

On checking the total collections, we have in our database, we found that there is no collection for now and we need to create one which is the “show collections” instruction.

To create a new collection, you should be executing the “createCollection” method of MongoDB in its shell. It should take the name of a newly created collection in the parameters, “Data”. The output result like {ok: 1} shows that your collection “Data” is successfully generated.

When you search for all the collections now, you will see a newly made collection listed there.

After generating a new collection in the MongoDB database, when we searched for the database, the “test” database that we used without even creating is displayed in the list.

Insert Records to Collection

For now, you should be sure that the collection “Data” is empty as we did not add any records to it. Therefore, an empty collection makes no sense and we need to insert documents into it. As we have been working for the “$ifNull” operator, we should add the fields with null values as well. Therefore, starting with the insertion of new documented records in the “Data” collection, we have tried the insertMany() function of MongoDB to insert more than 1 record at once. The insertion of a total of 6 records has taken place in one step.

The records should be in an array format or [] brackets at the start and end of the records. Each record should be separated by a comma “,” and curly “{}” brackets. Almost all the records have 2 fields at least: field “Name” and “Age”. While all the “Name” fields contain a string value there are some records where the field “Age” does not contain any record, null or undefined value. After the successful execution of this insert query for the “Data” collection, you will get the acknowledgment at the MongoDB shell screen displaying the automatically generated ids for each record.

After inserting a total of 6 records successfully in the “Data” collection of the MongoDB “test” database, you need to have a look at all these records as well. For this, you need to display each of the “Data” collection records in the JSON format using the forEach() function. For this, you should find all the records via the “find” function and then apply the forEach() function as displayed in the output below. The “printjson” argument is a must-have here. The output for displaying all 6 records of “Data” collection in the Json format is attached below.

Apply the “ifNull” operator on the “Age” field of the “Data” collection as it contains undefined and null values in documents. The aggregate() function of a MongoDB should be utilized to use the “ifNull” operator on the “Age” field. Therefore, we started the query with the name of a collection. It is followed by the “aggregate” function containing the project operator to project at the particular field on which the “ifNull” operator is going to be applied. The “Name” field is set to 1 so it can be displayed. The “ifNull” operator contains two arguments as [“”, “”] applied to the “Age” field. The output of this query shows Age: ” which replaced Age values with nothing. This is because we have not defined the name of an “Age” field in the first argument of an ifNull operator.

When you use any string expression in the first argument of the “ifNull” operator, it is going to replace all the values of an “Age” field with that string. In the output shown below, we have used the string “undefined” and it has taken the place of original values in the Age field of all records.

To avoid such a thing, we will have to use the “Age” field as the first argument to the ifNull operator along with the “$” sign. Then, let the second argument be empty i.e., second argument as its value. On execution of this aggregate instruction, we will be displaying the output attached below. Now, you can see that the Age field has been displaying its original values where there is no “null” value. Also, where the values are null or undefined the Age field is replaced with empty ”.

Let us have a look at different output values for the null values of the “Age” field using the ifNull operator in the aggregate instruction. This time, we are using the “NULL VALUE” string as the second argument to the ifNull operator to replace null values in the field “Age”. The result attached below displayed the replacement of null values with the “NULL VALUE” string.

Conclusion

This article contains a discussion on the use of the ifNull operator starting from its introduction to the end illustration. The illustrations demonstrate how an original “null” value of a certain field can be replaced with a new value. After discussing the whole procedure of using the ifNull operator in the MongoDB aggregate function, we came to the result that the ifNull operator’s first argument should be a field name with a “$” character to avoid complications.