MongoDB find with conditional project second param

Question

Let these commands present in the MongoDB shell:

> use testDB
> db.testCol.insertMany([ { score: 5 }, { score: 8 } ])

Now, I want to fetch all the collecrtion data, but with conditional projection with find. So, let's say I want to include score field in resulted documents, only when the score value is greater than 6.

Something (wrong) like this:

> db.testCol.find({}, { score: { $gt: 6 } })

My target result:

{ "_id" : ObjectId("...") }
{ "_id" : ObjectId("..."), "score": 8 }

How can I do it? Using only find method?

user_8987128 · Answer

You can use conditional operator $cond with $$REMOVE starting from MongoDB v3.6, to remove field if condition not match,

MongoDB v4.4: projection consistent with aggregation’s $project stage

db.testCol.find({},
{
  score: {
    $cond: [{ $gt: ["$score", 6] }, "$score", "$$REMOVE"]
  }
})

Playground

MongoDB 3.6 or Above:

db.collection.aggregate([
  {
    $project: {
      score: {
        $cond: [{ $gt: ["$score", 6] }, "$score", "$$REMOVE"]
      }
    }
  }
])

Playground

MongoDB find with conditional project second param

1 Answers