Check for missing field or null value in mongoDB atlas

MongoDB Atlas Atlas Search
,
6

Hi @Jason_Tran .
Thank you for taking out the time.
I went through the docs of atlas and found that we should avoid using $match along with $search : * Query Performance , maybe in future the product gets evolved so much that it wouldn’t matter but for now the $match impacts the performance a lot.

I did a example benchmark on the collection, it have more than 70 million records and :

  1. Without $match the execution time : 51ms
  2. With $match alongwith $search : 893ms

Now that being tested, i am planning to stick with only $search and above resolution works : execution time : 90ms

The above resolution depicts that we used range operator (which can be used for numbers and date values).
I have one other use case where the types of fields are String and Array.

How can i achieve same thing for string and array types also ?

{
  "_id": "62fdfd7518da050007f035c5",
  "expiryDate": "2022-08-18T23:59:59+05:30",
  "arrayField" : ['abc', 'def', 'ghi', 'jkl']
},
{
  "_id": "62fdfd7518da050007f035c6",
  "expiryDate": null,
  "arrayField" : ['abc','jkl']
},
{
  "_id": "62fdfd7518da050007f035c7",
  "arrayField" : []
  "status" : ""
},
{
  "_id": "62fdfd7518da050007f035c8",
  "expiryDate": null,
  "status" : null
}

arrayField is an array of objectIds : [ObjectId(123…), ObjectId(456…)]

My Goal is :

  1. arrayField doesn’t exists OR
  2. If arrayField does exists, then it must be empty array or null OR
  3. If arrayField does exists, then it must be some specified value

My current query for arrayField is :

{
    "should" : [
        {
            "compound": {
                "mustNot": [{
                    'exists': {
                        'path': 'arrayField'
                    }
                }]
            }
        },
        { // <--- i assume it checks for empty array like []
            "compound": {
                "must": [{
                    'exists': {
                        'path': 'arrayField'
                    }
                }],
                "mustNot": [
                    {
                        "exists": {
                            "path": "arrayField.0"
                        }
                    }]
            }
        },
        {
            "equals": {
                "value": mongoose.Types.ObjectId(someExampleValue),
                "path": "arrayField"
            }
        }
    ],
        "minimumShouldMatch" : 1
}

Problem with above query is it checks for empty array , but not for null.
Same case for string type also.

1 Like