Elasticsearch - Filter by Date Range where Dates can be Null Using Filter Context

Question

I am trying to filter out any results that have an "Available Until" date in the past. However, if the "Available Until" date is null, I still want to return the result.

Here is the output that I need:

Available Until Date < today << this should not be returned

Available Until Date >= today << this should be returned

Available Until Date is null << this should be returned

I have looked at the following other stack overflow questions here and here, however, I believe both are in query context, whereas I need to use filter context.

Below is my code for what I have so far. How would I modify this to allow results with null values in "Available Until", using filter context?

var baseQuery = {
    "size": 1000,
    "query": {
        "bool": {
            "filter": [{
                "range": {
                    "Product.AvailableUntil": {
                        "gte": "now"
                    }
                }
            }],
            "must_not": [],
            "should": [],
            "must": []
        }
    }
};

Thank you for any help! :)

Val · Accepted Answer · 2017-03-24 05:09:37Z

1

The only way is to use a bool/should query to capture the constraint that

either there is no AvailableUntil date
or the AvailableUntil is later than today

The query would look like this:

var baseQuery = {
    "size": 1000,
    "query": {
        "bool": {
            "minimum_should_match": 1,
            "should": [
              {
                "bool": {
                  "must_not": {
                    "exists": {
                      "field": "Product.AvailableUntil"
                    }
                  }
                }
              },
              {
                "range": {
                    "Product.AvailableUntil": {
                        "gte": "now"
                    }
                }
              }
            ]
        }
    }
};

answered Mar 24, 2017 at 5:09

Val

218k14 gold badges377 silver badges384 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

eclaire211 · Accepted Answer · 2017-03-27 14:19:31Z

The following solution is what I ended up using. By nesting the bool clauses inside of another bool clause which is inside of the filter clause, I was able to achieve my desired output using filter context.

var baseQuery = {
    "size": 1000,
    "query": {
        "bool": {
            "filter": [{
                "bool": {
                    "should": [{
                        "range": {
                            "Product.AvailableUntil": {
                                "gte": "now"
                            }
                        }
                    }, {
                        "bool": {
                            "must_not": {
                                "exists": {
                                    "field": "Product.AvailableUntil"
                                }
                            }
                        }
                    }]
                }
            }],
            "must_not": [],
            "should": [],
            "must": []
        }
    }
};

Collectives™ on Stack Overflow

Elasticsearch - Filter by Date Range where Dates can be Null Using Filter Context

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related