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 todayend_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.