/ /

$ifNull (expression operator)

Definition

$ifNull

The $ifNull expression evaluates input expressions for null values and returns:

The first non-null input expression value found.
A replacement expression value if all input expressions evaluate to null.

$ifNull treats undefined values and missing fields as null.

Compatibility

You can use $ifNull for deployments hosted in the following environments:

MongoDB Atlas: The fully managed service for MongoDB deployments in the cloud

MongoDB Enterprise: The subscription-based, self-managed version of MongoDB
MongoDB Community: The source-available, free-to-use, and self-managed version of MongoDB

Syntax

{
   $ifNull: [
      <input-expression-1>,
      ...
      <input-expression-n>,
      <replacement-expression-if-null>
   ]
}

Examples

The examples on this page use data from the sample_mflix sample dataset. For details on how to load this dataset into your self-managed MongoDB deployment, see Load the sample dataset. If you made any modifications to the sample databases, you may need to drop and recreate the databases to run the examples on this page.

Single Input Expression

The following example uses $ifNull to return:

rated if the rated field is non-null.
"Not Rated" string if rated is null or missing.

db.movies.aggregate(
   [
      { $match: { year: { $lt: 1910 } } },
      {
         $project: {
            _id: 0,
            title: 1,
            rated: { $ifNull: [ "$rated", "Not Rated" ] }
         }
      },
      { $sort: { title: 1 } }
   ]
)

[
  { title: 'A Corner in Wheat', rated: 'G' },
  { title: 'The Great Train Robbery', rated: 'TV-G' },
  { title: 'The Kiss', rated: 'Not Rated' },
  { title: 'The Kiss', rated: 'Not Rated' }
]

Multiple Input Expressions

New in version 5.0.

The following example uses $ifNull to return:

tomatoes.critic.rating if it's non-null.
tomatoes.viewer.rating if tomatoes.critic.rating is null or missing and tomatoes.viewer.rating is non-null.
0 if both tomatoes.critic.rating and tomatoes.viewer.rating are null or missing.

db.movies.aggregate(
   [
      { $match: { year: { $lt: 1910 } } },
      {
         $project: {
            _id: 0,
            title: 1,
            rating: { $ifNull: [ "$tomatoes.critic.rating", "$tomatoes.viewer.rating", 0 ] }
         }
      },
      { $sort: { title: 1 } }
   ]
)

[
  { title: 'A Corner in Wheat', rating: 3.6 },
  { title: 'The Great Train Robbery', rating: 7.6 },
  { title: 'The Kiss', rating: 4 },
  { title: 'The Kiss', rating: 0 }
]

Back

$hour

$in