Find documents with field that doesn't exist or field is less than value

Question

I have some employees and am trying to find those people that either don't have a birthday entered or have a birthday earlier than 1963-03-01. I have:

GET employees/_search
{
  "query": {
    "bool": {
      "must_not": {
        "exists": {
          "field": "birthday"
        }
      },
      "filter": {
        "range": {"birthday": {"lte": 19630301}}
      }
    }
  }
}

Right now I have 250 employees and none of them have a birthday field yet this query returns 0 results.

Answer 1

You can try a bool query with a should occurrence for this.

The first clause can apply the check for documents without the birthday field whilst the second clause can filter documents based on your given range.

The use of the should query will act as the logical OR operator and retrieve either of the two.

{
  "query": {
    "bool": {
      "should": [
        {
           "bool": { 
              "must_not": [{ "exists": { "field": "birthday" } }]
           }
        },
        {
           "bool": {
              "filter": [{ "range": {"birthday": { "lte": 19630301 }} }]
           }
        }
      ]
    }
  }
}

Since scores aren't calculated for filters you may want to keep all scores consistent with one another. You could opt to use the Constant Score Query or you could add an additional query onto the birthday filtered clause that inverses the must_not within the first clause like so:

{
  "query": {
    "bool": {
      "should": [
        {
           "bool": { 
              "must_not": [{ "exists": { "field": "birthday" } }]
           }
        },
        {
           "bool": {
              "must": [{ "exists": { "field": "birthday" } }],
              "filter": [{ "range": {"birthday": { "lte": 19630301 }} }]
           }
        }
      ]
    }
  }
}