Elasticsearch query apply range if the field exists

Question

My index has a "valid_until" field, which may be either null or a valid datetime. I want to write a query to get all documents from the index and apply a range filter only if "valid_until" has a valid date.

Some Index Data:

[
   ['name' => 'book1', 'valid_until' => '2019-09-30 18:00:00'],
   ['name' => 'book2', 'valid_until' => ''],
   ['name' => 'book3', 'valid_until' => '2019-09-20 18:00:00'],
],

This is the query I have tried:

{
  "query": {
    "bool": {
      "should": [
        {
          "range": {
            "valid_until": {
              "gte": "2019-09-22 00:00:00"
            }
          }
        },
        {
          "bool": {
            "must_not": {
              "exists": {
                "field": "valid_until"
              }
            }
          }
        }
      ]
    }
  }
}

Can you please post what query you have tried?. – Sathishkumar Rakkiyasamy Sep 22 '19 at 15:07 — Sathishkumar Rakkiyasamy, Sep 22 '19 at 15:07
Provide some sample data too. – Sathishkumar Rakkiyasamy Sep 22 '19 at 15:32 — Sathishkumar Rakkiyasamy, Sep 22 '19 at 15:32
I have updated my question again – arakibi Sep 22 '19 at 15:49 — arakibi, Sep 22 '19 at 15:49

score 5 · Answer 1 · answered Sep 22 '19 at 16:32

In case somebody would need this in the future, this is how I solved it:

{
  "query": {
    "bool": {
      "filter": [
        {
          "bool": {
            "should": [
              {
                "range": {
                  "valid_until": {
                    "gte": "now"
                  }
                }
              },
              {
                "bool": {
                  "must_not": {
                    "exists": {
                      "field": "valid_until"
                    }
                  }
                }
              }
            ]
          }
        }
      ],
      "must_not": [],
      "should": [],
      "must": []
    }
  }
}

Elasticsearch query apply range if the field exists

1 Answers1