Question 116980 in Mongodb Question posted in
The official documentation can be found here.

Check for missing field or null value in mongoDB atlas

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.

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    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 🙂


  2. 0

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

    Login or Signup to reply.
  3. 0

    @PawanSaxena I’m trying to do something similar, i.e., finding documents whose date field exists (and) with null value. Did you test out just the 2nd goal in your original post. Somehow it didn’t work for me. Here is my testing code:

    {
  "compound": {
    "must": [
      {
        "exists": {
          "path": "expiryDate"
        }
      }
    ],
    "mustNot": [   /// should it be "must"?
      {
        "range": {
          "path": "expiryDate",
          "lt": new Date()  /// see below _MONGOSH, new Date() will output current date 
          // "lt": 1   /// this doesn't work, either
        }
      }
    ]
  }
}

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search