search document with null/empty object field in elasticsearch

Question

I have an elasticsearch index with following mapping, some documents contain objects of status {id:1, status:"failed"} and some are null, cant seem to find a way where i can search for documents having "status.name" as ["failed", "null", "passed"] (docs where either status is failed, passed or not set/null). e.g doing a term query like below gives empty resultset

{
"name":{
"type":"keyword"
}
 "status": {
                "properties": {
                    "id": {
                        "type": "integer"
                    },
                    "status": {
                        "type": "keyword"
                    }
                }
            }
}

query tried:

{
"terms": {
  "status.name": [  "failed", "null" ]
   }

Also tried setting the mapping of status.name as "null_value": "null"

Daniel Schneiter · Accepted Answer · 2020-01-30 23:40:34Z

3

Use a bool query with only should clauses, making it a requirement that at least one of your queries must match. You can query for documents not having a field or having a null-value in that field by putting an exists-query into the must_not-clause of a bool-query (see Elasticsearch Reference: Exists-query).

GET myindex/_search
{
  "query": {
    "bool": {
      "should": [
        {"term": {"status.name": {"value": "failed"}}},
        {"term": {"status.name": {"value": "passed"}}},
        {"bool": {"must_not": {"exists": {"field": "status.name"}}}}
      ]
    }
  }
}

answered Jan 30, 2020 at 23:40

Daniel Schneiter

2,0061 gold badge16 silver badges20 bronze badges

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

1 Comment

Kamboh Over a year ago

Instead of doing exists on "status.name", it should be on "status" (object) only, otherwise get empty/un-expected results results

Collectives™ on Stack Overflow

search document with null/empty object field in elasticsearch

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related