# Exclude null containing key (records) while executing range Query in Elastic search JAVA Rest client

**URL:** <https://discuss.elastic.co/t/exclude-null-containing-key-records-while-executing-range-query-in-elastic-search-java-rest-client/213705>\
**Category:** Elasticsearch\
**Created:** [January 3, 2020, 12:55pm UTC](https://discuss.elastic.co/t/exclude-null-containing-key-records-while-executing-range-query-in-elastic-search-java-rest-client/213705 "2020-01-03T12:55:35Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Aswini\_Kumar\_Rout](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aswini_kumar_rout/32/42165_2.png) [@Aswini\_Kumar\_Rout](https://discuss.elastic.co/u/Aswini_Kumar_Rout)\
**Post date:** [January 3, 2020, 12:55pm UTC](https://discuss.elastic.co/t/exclude-null-containing-key-records-while-executing-range-query-in-elastic-search-java-rest-client/213705/1 "2020-01-03T12:55:35Z")

</div>

Let, I have below six records in my index.

```
{
        "min": "300",
        "max": "350",
        "contentName": "Dabangg 5"
    },
    {
        "min": "150",
        "max": "200",
        "contentName": "Dabangg 2"
    },
    {
        "min": "400",
        "max": "500",
        "contentName": "Dabangg 4"
    },
    {
        "min": null,
        "max": null,
        "contentName": "Dabangg 6"
    },
    {
        "min": "100",
        "max": "200",
        "contentName": "Dabangg"
    },
    {
        "min": "160",
        "max": "219",
        "contentName": "Dabangg 3"
    }

```

I am using the following rangeQuery to get the results by **"min"** and **"max"** field, which is working fine and I am getting proper results.

```
BoolQueryBuilder boolQuery1 = QueryBuilders.boolQuery()
  .should(QueryBuilders.rangeQuery("max").gte("100"))
  .should(QueryBuilders.boolQuery().mustNot(QueryBuilders.existsQuery("max")));

BoolQueryBuilder boolQuery2 = QueryBuilders.boolQuery()
  .should(QueryBuilders.rangeQuery("min").lte("300"))
  .should(QueryBuilders.boolQuery().mustNot(QueryBuilders.existsQuery("min")));

```

**But in every result I am getting the record which is having "max" and "min" as null(Example: Dabangg 6, according to above data set), which I want to exclude.**

So, can anyone help me with this requirement.

Thanks

---

<div class="post-metadata">

**Author:** ![William\_Brafford](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/william_brafford/32/51559_2.png) [@William\_Brafford](https://discuss.elastic.co/u/William_Brafford)\
**Post date:** [January 3, 2020, 3:58pm UTC](https://discuss.elastic.co/t/exclude-null-containing-key-records-while-executing-range-query-in-elastic-search-java-rest-client/213705/2 "2020-01-03T15:58:50Z")

</div>

Hi,

Two "should" clauses in a Boolean query work like an OR. That is, `boolQuery1` is searching for all documents where the max is greater than 100 OR documents where the `max` field does not exist.

I believe what you're looking for are `must` clauses at the top level:

```auto
BoolQueryBuilder boolQuery3 = QueryBuilders.boolQuery()
  .must(QueryBuilders.rangeQuery("max").gte("100"))
  .must(QueryBuilders.existsQuery("max"));

```

I haven't tested this Java code directly, but I have tested the following in the Kibana console:

```auto
GET dabangg/_search
{
  "query": {
    "bool": {
      "must": [
        { "range": { "max": { "gte": 250 } } },
        { "exists": { "field": "max" } }
      ]
    }
  }
}

```

I hope this helps,

-William

---

<div class="post-metadata">

**Author:** ![Aswini\_Kumar\_Rout](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aswini_kumar_rout/32/42165_2.png) [@Aswini\_Kumar\_Rout](https://discuss.elastic.co/u/Aswini_Kumar_Rout)\
**Post date:** [January 6, 2020, 6:48am UTC](https://discuss.elastic.co/t/exclude-null-containing-key-records-while-executing-range-query-in-elastic-search-java-rest-client/213705/3 "2020-01-06T06:48:17Z")

</div>

Thank you very much @William_Brafford . This is what I've been looking for. Worked.

---

<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:** [February 3, 2020, 6:48am UTC](https://discuss.elastic.co/t/exclude-null-containing-key-records-while-executing-range-query-in-elastic-search-java-rest-client/213705/4 "2020-02-03T06:48:18Z")

</div>

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