I am using mongodb atlas for full text search. My sample collection looks like this :

{
  "_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" : []
},
{
  "_id": "62fdfd7518da050007f035c8",
  "expiryDate": null
}

expiryDate is a Date type field and arrayField is an Array type field.

My goal is to get all documents where either :

1. expiryDate doesn't exists OR
2. If expiryDate does exists, then it must be null OR
3. If expiryDate does exists, then it must greater than current time.

My current atlas aggregation looks like :

{
    'compound' : {
        'should' : [
            {
                'compound' : {
                    'mustNot' : [{
                        "exists": {
                            "path": "expiryDate",
                        }
                    }]
                }
            },
            {
                "range": {
                    "path": "expiryDate",
                    'gte': new Date()
                }
            }
        ],
        'minimumShouldMatch' : 1
    }
}

This is not returning all documents where the expiryDate field have null value and it is only matching one clause of should where expiryDate is greater than or equal to current time. I want it to return all those documents too where the expiryDate is null.

Please advise.

CodePudding user response：

You can use the $exists operator (docs) to check if an element exists, and if it does, run a check on its value.

CodePudding user response：

So I tried with multiple clauses and approaches and found that there are two solutions to this problem :

1. Use combination of $must, $should and $mustNot :

{
    'compound' : {
        'should' : [
            {
                'compound' : {
                    'mustNot' : [{
                        "exists": {
                            "path": "expiryDate",
                        }
                    }]
                }
            },
            {
              "compound": {
                "must": [
                  {
                    "exists": {
                      "path": "expiryDate"
                    }
                  }
                ],
                "mustNot": [
                  {
                    "range": {
                      "path": "expiryDate",
                      "lt": new Date()
                    }
                  }
                ]
              }
            }
            {
                "range": {
                    "path": "expiryDate",
                    'gte': new Date()
                }
            }
        ],
        'minimumShouldMatch' : 1
    }
}

And the second one is rather not optimized but works. Since at the end it's and aggregation; We can use $match operator just outside the $search pipeline like so :

db.exampleCollection.aggregate([
  {
    "$search": {
      "index": "default",
      "compound": {
        "must": [
          ...some conditions
        ],
        "filter": [
          ...some clauses
        ]
      }
    }
  },
  {
    "$match": [...some other conditions]
  },
  {
    "$project": {
      ...some fields
    }
  },
  {
    "$skip": 0
  },
  {
    "$limit": 10
  },
  {
    "$sort": {
      "score": 1
    }
  }
])

Hope it helps someone