MongoDB: Finding all documents for which a certain field in an array is always true

Question

This is the bios collection that I found on the official MongoDB documentation:

    {
        "_id" : 1,
        "name" : {
            "first" : "John",
            "last" : "Backus"
        },
        "awards" : [
            {
                "award" : "W.W. McDowell Award",
                "year" : 1967,
                "by" : "IEEE Computer Society",
                "monetaryRewards": false
            }
        ]
    },
    {
        "_id" : 2,
        "name" : {
            "first" : "John",
            "last" : "McCarthy"
        },
        "awards" : [
            {
                "award" : "Turing Award",
                "year" : 1971,
                "by" : "ACM",
                "monetaryRewards": true
            },
            {
                "award" : "Kyoto Prize",
                "year" : 1988,
                "by" : "Inamori Foundation",
                "monetaryRewards": true
            }
        ]
    },
    {
        "_id" : 3,
        "name" : {
            "first" : "Grace",
            "last" : "Hopper"
        },
        "awards" : [
            {
                "award" : "Computer Sciences Man of the Year",
                "year" : 1969,
                "by" : "Data Processing Management Association",
                "monetaryRewards": true
            },
            {
                "award" : "Distinguished Fellow",
                "year" : 1973,
                "by" : " British Computer Society",
                "monetaryRewards": true
            },
            {
                "award" : "W. W. McDowell Award",
                "year" : 1976,
                "by" : "IEEE Computer Society",
                "monetaryRewards": false
            }
        ]
    }

I am trying to write a query that will allow me to retrieve all documents for which all monetaryRewards are true. Thus, considering the previous documents:

_id = 1 -> there is only one monetaryRewards and it is false. The query should not select it;
_id = 2 -> There are three awards and the monetaryRewards fields are always true. The query should select it;
_id = 3 -> There are three awards where monetaryRewards is twice true and once false. The query should not select it;

I wrote the following query:

    db.bios.find( { awards: {$not: {$elemMatch:{"monetaryRewards":false}}}} )

The query works correctly. Later I realised that my bios collection might also not contain the monetaryRewards field, for example there could be another document:

    {
           "_id" : 4,
           "name" : {
               "first" : "Kristen",
               "last" : "Nygaard"
           },
           "awards" : [
               {
                   "award" : "Rosing Prize",
                   "year" : 1999,
                   "by" : "Norwegian Data Association",
                   "monetaryRewards":true
               },
               {
                   "award" : "Turing Award",
                   "year" : 2001,
                   "by" : "ACM"
               }
       }

In this situation my query fails because it takes the lack of monetaryRewards as true while in my case it should be false.

How can I fix my query?

Here you can find the mongoplayground where you can see that the document with _id=4 is incorrectly selected.

Answer 1

You can $map the awards.monetaryRewards to an auxiliary array of booleans. Use $ifNull to cater the missing field case. Then use $allElementsTrue to perform the filtering.

db.collection.aggregate([
  {
    "$addFields": {
      "filterArr": {
        "$map": {
          "input": "$awards",
          "as": "a",
          "in": {
            $ifNull: [
              "$$a.monetaryRewards",
              false
            ]
          }
        }
      }
    }
  },
  {
    "$match": {
      $expr: {
        "$allElementsTrue": "$filterArr"
      }
    }
  },
  {
    $project: {
      filterArr: false
    }
  }
])

Here is the Mongo playground for your reference.