In ElasticSearch how to check if a field exists that it equals some value, or that the field doesn't exist?

user_90416 0
Viewed 7451

I want to find all documents in elasticsearch, where my "updated" field exists and is less that some value or where the field doesn't exist in the document at all. I can see that using a bool query, and must and must not can be utilized but how do I get the exact scenario I am trying to achieve with them?

Thank you!

kibana elasticsearch
1 Answers

Assuming updated is a date type of field the query would look as below:

GET test/_search
{
  "query": {
    "bool": {
      "should": [
        {
          "bool": {
            "must": [
              {
                "exists": {
                  "field": "updated"
                }
              },
              {
                "range": {
                  "updated": {
                    "lte": "2019-06-10"
                  }
                }
              }
            ]
          }
        },
        {
          "bool": {
            "must_not": [
              {
                "exists": {
                  "field": "updated"
                }
              }
            ]
          }
        }
      ]
    }
  }
}

Explanation of above:

Let,

  • Field updated should exists ===> A
  • Field updated should be less than X ===> B
  • Field updated should not exist at all ===> C

The required condition translates to (A AND B) OR C

Let (A AND B) be D

Now in terms of elastic it becomes:

should 
{
   D,
   C
}

OR

should
{
   must
   {
      A,
      B
   },
   C
}

In the above query only range query is sufficient and there is no requirement to check for the existence of updated field using exists query along with the range.

So the query can be rewritten as (B OR C):

GET test/_search
{
  "query": {
    "bool": {
      "should": [
        {
          "range": {
            "updated": {
              "lte": "2019-06-10"
            }
          }
        },
        {
          "bool": {
            "must_not": [
              {
                "exists": {
                  "field": "updated"
                }
              }
            ]
          }
        }
      ]
    }
  }
}
user_11363710
Related