Check for missing field or null value in mongoDB atlas

MongoDB Atlas Atlas Search
,
5
  1. use $search as i am using atlas and not mongodb

I understand those are your requirements. The alternate I have suggested runs in both in Atlas and non-Atlas deployments of MongoDB. To be more specific, i’m curious to what the 3 conditions equate to as a goal - I.e. Do those 3 requirements equate to documents that have an invalid expiry date from your application’s perspective?

In saying so, there a few ways that should be able to get the document(s) you’re after although i’m not sure if you’re wanting to use $search ONLY, whether additional stages are allowed, etc.

For example, if you’re only wanting to use the $search stage and nothing else, you may be able to get these documents using the below example:

DB> var a = 
{
  '$search': {
    index: 'default',
    compound: {
      should: [
        {
          range: {
            path: 'expiryDate',
            gte: ISODate("2022-01-01T00:00:00.000Z") /// <--- Assuming this is the current date for example purposes. Modify as required.
          }
        },
        {
          compound: {
            mustNot: [ { exists: { path: 'expiryDate' } } ]
          }
        },
        {
          compound: {
            must: [ { exists: { path: 'expiryDate' } } ],
            mustNot: [
              {
                range: {
                  path: 'expiryDate',
                  lt: 1 /// <---- null is less than 1 based off the below mongosh output
                }
              }
            ]
          }
        }
      ]
    }
  }
}

The oddity here is that i’ve used a range of less than 1 to try and retrieve null values on "expiryDate". However, i’d recommend also voting for the following feedback related to indexing null data types. The mongosh output for testing if null is greater than 1:

DB> null < 1
true

Similar to the above example I provided earlier, you can also obtain the same document(s) using a $match stage equivalent to my .find() example:

var b =
{
  '$match': {
    '$or': [
      { expiryDate: { '$exists': false } },
      { expiryDate: null },
      { expiryDate: { '$gte': ISODate("2022-01-01T00:00:00.000Z") } } /// <--- Assuming this is the current date for example purposes. Modify as required.
    ]
  }
}

Output:

DB> db.collection.aggregate(b)
[
  {
    _id: '62fdfd7518da050007f035c6',
    expiryDate: null,
    arrayField: [ 'abc', 'jkl' ]
  },
  { _id: '62fdfd7518da050007f035c7', arrayField: [] },
  { _id: '62fdfd7518da050007f035c8', expiryDate: null },
  {
    _id: '62fdfd7518da050007f035c5',
    expiryDate: ISODate("2022-08-18T23:59:59.000Z"),
    arrayField: [ 'abc', 'def', 'ghi', 'jkl' ]
  }
]

You could also do a combination of a $search stage that gets the path if it exists or not followed by a $match stage for documents that have an "expiryDate" field value that is greater than the specified date or equal to null. Although there would be no index usage here for the $match stage that follows.

Please note:

  • Examples above are only based off the 4 sample documents
  • Please test thoroughly in and verify it suits all use case / requirements.
  • "expiryDate" field in 4 sample documents tested do not contain any String value types.

Regards,
Jason

3 Likes