Selva
Selva

Reputation: 21

Elastic Search query to check for null values in 2 fields

What is the elastic search query to search for documents where "field1 is null OR field2 is null"..

I am using elasticSearch 5.3...

Upvotes: 2

Views: 16083

Answers (2)

Nicolás Fantone
Nicolás Fantone

Reputation: 2120

You could also get away with using a should clause in your bool filter.

    "bool": {
      "should": [{
        "bool": {
          "must_not": {
            "exists": {
              "field": "field1"
            }
          }
        }
      }, {
        "bool": {
          "must_not": {
            "exists": {
              "field": "field2"
            }
          }
        }
      }]
    }

Upvotes: 1

Pavel Vasilev
Pavel Vasilev

Reputation: 1042

This query did work for me:

curl -XGET "http://localhost:9200/my_null_val/_search?pretty" -d '
{
  "query": {
    "bool":{
      "must_not":{
        "bool": {
          "must": [
            {"exists" : { "field" : "field1" }},
            {"exists" : { "field" : "field2" }}
          ]
        }
      }
    }
  }
}'

As a hint you can think of field1 is null **OR** field2 is null to be equivalent expression to NOT (field1 is not null **AND** field2 is not null).

It is also known as De_Morgan's law.

Upvotes: 7

Related Questions