CODEWITHSUNDEEP
mongodbnullprojection
Peter Sampson
Peter Sampson

Reputation: 540

Projecting a field that doesn't always exist

Is there a way to project fields that may or may not exist? Such as having it defined as null or undefined?

For instance, I have a query with:

$project: {
  date: 1,
  name: "$person.name",
  age: "$person.age"
}

Not all documents are guaranteed to have a $person.age, but instead of the ones without an age being returned as { date: Today, name: "Bill" }, I would like it to say { date: Today, name: "Bill", age: null } or something similar.

Is there a better way than just iterating through the data afterwards and creating the fields if they don't exist?

Upvotes: 25

Views: 22941

Answers (3)

Xavier Guihot
Xavier Guihot

Reputation: 61666

Starting in Mongo 5.3, it's a nice use case for the new $fill aggregation operator:

// { name: "Bill", age: 23 }
// { name: "Peter" }
db.collection.aggregate(
  { $fill: { output: { age: { value: "Unspecified" } } } }
)
// { name: "Bill",  age: 23 }
// { name: "Peter", age: "Unspecified" }

Missing values or values set to null are filled with the given constant Unspecified.

Concerning your exact scenario, you can go for:

// { person: { name: "Bill", age: 23 } }
// { person: { name: "Peter" } }
db.collection.aggregate([
  { $project: { name: "$person.name" , age: "$person.age" } },
  { $fill: { output: { "age": { value: "Unspecified" } } } }
])
// { name: "Bill",  age: 23 }
// { name: "Peter", age: "Unspecified" }

Upvotes: 4

karthik manchala
karthik manchala

Reputation: 13640

Use $ifNull

  $project: {
     date: 1,
     name: "$person.name",
     age: { $ifNull: [ "$person.age", "null" ] }
  }

You can find more about it here

Upvotes: 52

chridam
chridam

Reputation: 103375

This is where $ifNull expression comes into the fray. From the docs, $ifNull:

Evaluates an expression and returns the value of the expression if the expression evaluates to a non-null value. If the expression evaluates to a null value, including instances of undefined values or missing fields, returns the value of the replacement expression.

In your case, the following will use the $ifNull expression to return either the non-null $person.age field value or the string "Unspecified" if the age field is null or does not exist:

 $project: {
     date: 1,
     name: "$person.name",         
     age: { $ifNull: [ "$person.age", "Unspecified" ] }
 }

Upvotes: 3

Related Questions