# Handling Null Values

**URL:** <https://discuss.elastic.co/t/handling-null-values/107867>\
**Category:** Elasticsearch\
**Created:** [November 16, 2017, 6:13am UTC](https://discuss.elastic.co/t/handling-null-values/107867 "2017-11-16T06:13:02Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![sidra\_89](https://avatars.discourse-cdn.com/v4/letter/s/3d9bf3/32.png) [@sidra\_89](https://discuss.elastic.co/u/sidra_89)\
**Post date:** [November 16, 2017, 6:13am UTC](https://discuss.elastic.co/t/handling-null-values/107867/1 "2017-11-16T06:13:02Z")

</div>

Hi ..

i want to search where the field value is either equal to null or have some value

like suppose if the field name is brand\_name then i want to get record where brand\_name is either equal to null (null means no value) or equal to some brand name

exists works okay alone but when i combine it with where is have some particular values it stops working

i need a bool query for it ..

i tried this query

```
 "query": {

"bool": {
  "must_not": [
      {
        "exists": {
          "field": "brand_name"
        }
      }
    ],
        
    "must": [
      {
        "match": {
            "brand_name": "hello world"
        }
      }
    ]
   }

  }

}

```

must and must\_not alone works fine but when used together in this pattern no results are returned (i am trying to get where brand\_name is "hello world" or where brand\_name is empty (a null value)

Thank you ..

---

<div class="post-metadata">

**Author:** ![forloop](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/forloop/32/9021_2.png) [@forloop](https://discuss.elastic.co/u/forloop)\
**Post date:** [November 16, 2017, 6:27am UTC](https://discuss.elastic.co/t/handling-null-values/107867/2 "2017-11-16T06:27:08Z")

</div>

Take a look at building [a `bool` query with two `should` clauses](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-bool-query.html). You can use a `bool` query as one of the `should` clauses in order to invert an `exists` query using a `must_not` clause

```json
{
  "query": {
    "bool" : {
      "should" : [
        { "bool" : { 
            "must_not" : [
              { "exists": { "field" : "brand_name" } } 
            ] 
          } 
        },
        { "term" : { "brand_name" : "some brand name" } }
      ]
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![sidra\_89](https://avatars.discourse-cdn.com/v4/letter/s/3d9bf3/32.png) [@sidra\_89](https://discuss.elastic.co/u/sidra_89)\
**Post date:** [November 16, 2017, 6:51am UTC](https://discuss.elastic.co/t/handling-null-values/107867/3 "2017-11-16T06:51:49Z")

</div>

Thank you so much it solved the problem ..

---

<div class="post-metadata">

**Author:** ![sidra\_89](https://avatars.discourse-cdn.com/v4/letter/s/3d9bf3/32.png) [@sidra\_89](https://discuss.elastic.co/u/sidra_89)\
**Post date:** [November 16, 2017, 8:24am UTC](https://discuss.elastic.co/t/handling-null-values/107867/4 "2017-11-16T08:24:52Z")

</div>

one more question if i want to and its result to places where user\_ids are 9, 10, 16, 19 how would i integrate this into this??

---

<div class="post-metadata">

**Author:** ![forloop](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/forloop/32/9021_2.png) [@forloop](https://discuss.elastic.co/u/forloop)\
**Post date:** [November 16, 2017, 8:32am UTC](https://discuss.elastic.co/t/handling-null-values/107867/5 "2017-11-16T08:32:51Z")

</div>

put the `bool` query with the `should` clauses in the `must` clause of an outer `bool` query, along with a `terms` query for the user\_ids

```json
{
  "query": {
    "bool": {
      "must": [
      {
        "bool": {
          "should": [
          {
            "bool": {
              "must_not": [
              {
                "exists": {
                  "field": "brand_name"
                }
              }]
            }
          },
          {
            "term": {
              "brand_name": "some brand name"
            }
          }]
        }
      },
      {
        "terms": {
          "user_ids": [9, 10, 16, 19]
        }
      }]
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![sidra\_89](https://avatars.discourse-cdn.com/v4/letter/s/3d9bf3/32.png) [@sidra\_89](https://discuss.elastic.co/u/sidra_89)\
**Post date:** [November 16, 2017, 8:44am UTC](https://discuss.elastic.co/t/handling-null-values/107867/6 "2017-11-16T08:44:16Z")

</div>

okay it worked perfectly fine ..

i get how it is working now .. a bit confuse when to use the bool keyword ..

---

<div class="post-metadata">

**Author:** ![forloop](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/forloop/32/9021_2.png) [@forloop](https://discuss.elastic.co/u/forloop)\
**Post date:** [November 16, 2017, 8:58am UTC](https://discuss.elastic.co/t/handling-null-values/107867/7 "2017-11-16T08:58:15Z")

</div>

`bool` queries can be arbitrarily nested, allowing the expression of more complex compound queries

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [December 14, 2017, 8:58am UTC](https://discuss.elastic.co/t/handling-null-values/107867/8 "2017-12-14T08:58:23Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.