Elasticsearch - Bool filter: date range OR field can be nul

Question

I'm trying to create a bool filter in ElasticSearch that checks the start_date and end_date compared to today. If start_date or end_date is null, the result should still be returned.

So for example, today is 2016-08-09.

• Item 1: start_date: 2016-08-04 end_date: 2016-08-08 SHOULD NOT BE RETURNED
• Item 2: start_date: 2016-08-08 end_date: 2016-08-12 SHOULD BE RETURNED
• Item 3: start_date: null end_date: null SHOULD BE RETURNED

Can't seem to get it to work with my current code:

POST _search
{
"query":{
    "filtered": {
        "query": {
            "match_all": {}
        },
        "filter" : {
            "bool": {
                "must": [{
                    "range": {
                        "start_date": {"lte": "2016-08-09"}         
                    }
                },
                {
                    "range": {
                        "end_date": {"gte": "2016-08-09"}     
                    }
                }],
                "should": [{
                    "missing": {
                        "field": "start_date"         
                    }
                },
                {
                    "missing": {
                        "field": "end_date"      
                    }
                }]
            }
        }
    }
}
}

Answer 1

It looks like you need the following query:

(start_date <= date AND date <= end_date) OR (start_date == null) OR (end_date == null)

Following the example at elasticsearch documentation, I would write the following clause:

"bool": {
    "should": [
        {"bool": 
            "must": [{
                "range": {
                    "start_date": {
                        "lte": "2016-08-09"
                    }
                }
            },
            {
                "range": {
                    "end_date": {
                        "gte": "2016-08-09"
                    }
                }
            }]
        },
        "missing": {
            "field": "start_date"
        },
        "missing": {
            "field": "end_date"
        }
    ]
}

If it did not work exactly as written, I would try to see if each part of the should clause works (i.e. date is inside a range, start_date is not present, end_date is not present) by executing queries with these individual pieces. If all of them work, combining them inside should clause should work too.

Answer 2

You probably need something like this, i.e.:

• start_date must either be null or before today
• end_date must either be null or after today

Revised query:

POST _search
{
  "query": {
    "filtered": {
      "filter": {
        "bool": {
          "must": [
            {
              "bool": {
                "should": [
                  {
                    "range": {
                      "start_date": {
                        "lte": "now"
                      }
                    }
                  },
                  {
                    "missing": {
                      "field": "start_date"
                    }
                  }
                ]
              }
            },
            {
              "bool": {
                "should": [
                  {
                    "range": {
                      "end_date": {
                        "gte": "now"
                      }
                    }
                  },
                  {
                    "missing": {
                      "field": "end_date"
                    }
                  }
                ]
              }
            }
          ]
        }
      }
    }
  }
}