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

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


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"                    }                  }                ]              }            }          ]        }      }    }  }}


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.