# ElasticSearch (6.5) Java high level rest client - must(QueryBuilders.rangeQuery) and mustNot(QueryBuilders.existsQuery) cmbination not working

**URL:** <https://discuss.elastic.co/t/elasticsearch-6-5-java-high-level-rest-client-must-querybuilders-rangequery-and-mustnot-querybuilders-existsquery-cmbination-not-working/174641>\
**Category:** Elasticsearch\
**Created:** [March 30, 2019, 2:22pm UTC](https://discuss.elastic.co/t/elasticsearch-6-5-java-high-level-rest-client-must-querybuilders-rangequery-and-mustnot-querybuilders-existsquery-cmbination-not-working/174641 "2019-03-30T14:22:21Z")\
**Posts on this page:** 20\
**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:** [March 30, 2019, 2:22pm UTC](https://discuss.elastic.co/t/elasticsearch-6-5-java-high-level-rest-client-must-querybuilders-rangequery-and-mustnot-querybuilders-existsquery-cmbination-not-working/174641/1 "2019-03-30T14:22:21Z")

</div>

I want to have following filter with elasticsearch (java high level rest client):  
If publish\_date exists and lower than now  
If publish\_end\_date exists and greater than now  
I already have following but it doesn't show those docs which have no publish\_date or publish\_end\_date:

[Here are the details of my codes](https://stackoverflow.com/questions/55415330/elasticsearch-mustquerybuilders-rangequery-and-mustnotquerybuilders-existsq)

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [March 30, 2019, 2:36pm UTC](https://discuss.elastic.co/t/elasticsearch-6-5-java-high-level-rest-client-must-querybuilders-rangequery-and-mustnot-querybuilders-existsquery-cmbination-not-working/174641/2 "2019-03-30T14:36:15Z")

</div>

Could you provide a full recreation script as described in [About the Elasticsearch category](https://discuss.elastic.co/t/about-the-elasticsearch-category/21). It will help to better understand what you are doing. Please, try to keep the example as simple as possible.

A full reproduction script will help readers to understand, reproduce and if needed fix your problem. It will also most likely help to get a faster answer.

Here I'm not sure on how you are calling the search endpoint.  
Having a full script that reproduces the problem with Kibana dev console would help a lot to understand what you are doing wrong.

---

<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:** [March 30, 2019, 2:47pm UTC](https://discuss.elastic.co/t/elasticsearch-6-5-java-high-level-rest-client-must-querybuilders-rangequery-and-mustnot-querybuilders-existsquery-cmbination-not-working/174641/3 "2019-03-30T14:47:36Z")

</div>

Below is the query which my queryBuilder generates finally-

```auto
POST sadaf/_search
{
  "query": {
  "bool" : {
    "must" : [
      {
        "bool" : {
          "should" : [
            {
              "match_phrase_prefix" : {
                "name" : {
                  "query" : "spi",
                  "slop" : 0,
                  "max_expansions" : 50,
                  "boost" : 1.0
                }
              }
            }
          ],
          "adjust_pure_negative" : true,
          "boost" : 1.0
        }
      },
      {
        "bool" : {
          "adjust_pure_negative" : true,
          "boost" : 1.0
        }
      },
      {
        "bool" : {
          "adjust_pure_negative" : true,
          "boost" : 1.0
        }
      }
    ],
    "filter" : [
      {
        "bool" : {
          "must_not" : [
            {
              "exists" : {
                "field" : "publish_date",
                "boost" : 1.0
              }
            }
          ],
          "should" : [
            {
              "range" : {
                "publish_date" : {
                  "from" : null,
                  "to" : "now",
                  "include_lower" : true,
                  "include_upper" : true,
                  "boost" : 1.0
                }
              }
            }
          ],
          "adjust_pure_negative" : true,
          "minimum_should_match" : "1",
          "boost" : 1.0
        }
      },
      {
        "bool" : {
          "must_not" : [
            {
              "exists" : {
                "field" : "publish_end_date",
                "boost" : 1.0
              }
            }
          ],
          "should" : [
            {
              "range" : {
                "publish_end_date" : {
                  "from" : "now",
                  "to" : null,
                  "include_lower" : true,
                  "include_upper" : true,
                  "boost" : 1.0
                }
              }
            }
          ],
          "adjust_pure_negative" : true,
          "minimum_should_match" : "1",
          "boost" : 1.0
        }
      }
    ],
    "adjust_pure_negative" : true,
    "boost" : 1.0
  }
}
}

```

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [March 30, 2019, 5:00pm UTC](https://discuss.elastic.co/t/elasticsearch-6-5-java-high-level-rest-client-must-querybuilders-rangequery-and-mustnot-querybuilders-existsquery-cmbination-not-working/174641/4 "2019-03-30T17:00:48Z")

</div>

I can't reproduce anything with that. Please follow the instructions I shared if you need assistance.

And please format your code, logs or configuration files using `</>` icon as explained in [this guide](https://discuss.elastic.co/t/about-the-elasticsearch-category/21) and not the citation button. It will make your post more readable.

Or use markdown style like:

````
```
CODE
```

````

This is the icon to use if you are not using markdown format:

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/7/e/7e6e239431ec2d71cbf1beef741f2e93e7cc762c.jpg)

There's a live preview panel for exactly this reasons.

Lots of people read these forums, and many of them will simply skip over a post that is difficult to read, because it's just too large an investment of their time to try and follow a wall of badly formatted text.  
If your goal is to get an answer to your questions, it's in your interest to make it as easy to read and understand as possible.  
Please update your post.

---

<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:** [March 30, 2019, 6:00pm UTC](https://discuss.elastic.co/t/elasticsearch-6-5-java-high-level-rest-client-must-querybuilders-rangequery-and-mustnot-querybuilders-existsquery-cmbination-not-working/174641/5 "2019-03-30T18:00:49Z")

</div>

I have edited my code ,you can check the above query-  
I want the records which have the publish\_date lte now at the same time those records which don't have the publish\_date field  
And  
the records which have the publish\_end\_date gte now at the same time those records which don't have the publish\_end\_date field

I have five records, you can find them in below image-

 ![Screenshot%20from%202019-03-27%2021-08-50](https://us1.discourse-cdn.com/elastic/original/3X/7/2/72bd3b0a598805a2f0f15d19d4126b9f56048264.png)

when I am using only the range query like below its working fine-

```auto
BoolQueryBuilder startDateQuery = new BoolQueryBuilder()
           .must(QueryBuilders.rangeQuery("publish_date").lte("now"));
BoolQueryBuilder endDateQuery = new BoolQueryBuilder()
          .must(QueryBuilders.rangeQuery("publish_end_date").gte("now"));

```

![Screenshot%20from%202019-03-29%2016-02-36](https://us1.discourse-cdn.com/elastic/original/3X/5/e/5e32eef665694072a7c72bcbf96d0503f04ef5b7.png)

When I am using only the mustNot query like below I am getting the desired results-

```auto
BoolQueryBuilder startDateQuery = new BoolQueryBuilder()
           .mustNot(QueryBuilders.existsQuery("publish_date"));
BoolQueryBuilder endDateQuery = new BoolQueryBuilder()
          .mustNot(QueryBuilders.existsQuery("publish_end_date"));

```

![Screenshot%20from%202019-03-29%2016-03-47](https://us1.discourse-cdn.com/elastic/original/3X/f/a/fa78b9fd0ac6cee8fae5acf9a5c70769ca2bf876.png)  
But the problem is when I combine both the queries like below I am not getting any result(getting an empty array)-

```auto
BoolQueryBuilder startDateQuery = new BoolQueryBuilder()
		           .should(QueryBuilders.rangeQuery("publish_date").lte("now"))
		           .mustNot(QueryBuilders.existsQuery("publish_date"))
		           .minimumShouldMatch(1);
		BoolQueryBuilder endDateQuery = new BoolQueryBuilder()
		          .should(QueryBuilders.rangeQuery("publish_end_date").gte("now"))
		          .mustNot(QueryBuilders.existsQuery("publish_end_date"))
		          .minimumShouldMatch(1);

```

I am getting empty   
 ![Screenshot%20from%202019-03-29%2016-04-22](https://us1.discourse-cdn.com/elastic/original/3X/3/8/38f7af71375467ccde0d9c57809238e43754fc59.png)

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [March 30, 2019, 6:14pm UTC](https://discuss.elastic.co/t/elasticsearch-6-5-java-high-level-rest-client-must-querybuilders-rangequery-and-mustnot-querybuilders-existsquery-cmbination-not-working/174641/6 "2019-03-30T18:14:11Z")

</div>

Please read again: [About the Elasticsearch category](https://discuss.elastic.co/t/about-the-elasticsearch-category/21)

> A typical script like this one can be copied and pasted in Kibana Dev Console by any reader. It will definitely help to play with your example and provide a fix for your script.  
> If you don't provide it, there is a chance that nobody can help.

---

<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:** [March 30, 2019, 6:23pm UTC](https://discuss.elastic.co/t/elasticsearch-6-5-java-high-level-rest-client-must-querybuilders-rangequery-and-mustnot-querybuilders-existsquery-cmbination-not-working/174641/7 "2019-03-30T18:23:20Z")

</div>

edited again now you can copy paste the query and I have give some images of my data and how I am building queries in another comment.

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [March 30, 2019, 6:48pm UTC](https://discuss.elastic.co/t/elasticsearch-6-5-java-high-level-rest-client-must-querybuilders-rangequery-and-mustnot-querybuilders-existsquery-cmbination-not-working/174641/8 "2019-03-30T18:48:15Z")

</div>

Do you mean that I need to create a dataset manually to test your query?

I can for sure. That will require some time on my end and I'm afraid I can't do it.

---

<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:** [March 30, 2019, 7:08pm UTC](https://discuss.elastic.co/t/elasticsearch-6-5-java-high-level-rest-client-must-querybuilders-rangequery-and-mustnot-querybuilders-existsquery-cmbination-not-working/174641/9 "2019-03-30T19:08:26Z")

</div>

I have given the full query and sample data , what else you want?All those datas and all in my local system how can I help you tell me?

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [March 30, 2019, 7:21pm UTC](https://discuss.elastic.co/t/elasticsearch-6-5-java-high-level-rest-client-must-querybuilders-rangequery-and-mustnot-querybuilders-existsquery-cmbination-not-working/174641/10 "2019-03-30T19:21:59Z")

</div>

Something like this:

```
DELETE index
PUT index/_doc/1
{
  "foo": "bar"
}
GET index/_search
{
  "query": {
    "match": {
      "foo": "bar"
    }
  }
}
```

---

<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:** [March 30, 2019, 7:28pm UTC](https://discuss.elastic.co/t/elasticsearch-6-5-java-high-level-rest-client-must-querybuilders-rangequery-and-mustnot-querybuilders-existsquery-cmbination-not-working/174641/11 "2019-03-30T19:28:33Z")

</div>

```auto
POST sadaf/_search
{
  "query": {
    "bool" : {
    "filter" :
      {
        "bool" : {
          "must_not" : [
            {
              "exists" : {
                "field" : "publish_date",
                "boost" : 1.0
              }
            }
          ],
          "should" : [
            {
              "range" : {
                "publish_date" : {
                  "from" : null,
                  "to" : "now",
                  "include_lower" : true,
                  "include_upper" : true,
                  "boost" : 1.0
                }
              }
            }
          ],
          "adjust_pure_negative" : true,
          "minimum_should_match" : "1",
          "boost" : 1.0
        }
      }
  }
}
}

```

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [March 30, 2019, 8:45pm UTC](https://discuss.elastic.co/t/elasticsearch-6-5-java-high-level-rest-client-must-querybuilders-rangequery-and-mustnot-querybuilders-existsquery-cmbination-not-working/174641/12 "2019-03-30T20:45:00Z")

</div>

I don't have any index named `sadaf`. So it is failing.

---

<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:** [March 31, 2019, 6:13am UTC](https://discuss.elastic.co/t/elasticsearch-6-5-java-high-level-rest-client-must-querybuilders-rangequery-and-mustnot-querybuilders-existsquery-cmbination-not-working/174641/13 "2019-03-31T06:13:51Z")

</div>

Please use below bulk API to create the index and documents (the same data on which I want these manipulations)

```auto
POST _bulk
{ "index" : { "_index" : "sadaf", "_type" : "movies", "_id" : "1" } }
{"name": "spiderman walks","storeId": "2","gener": "Action","languageId": "2","category": "Hollywood","publish_date": "2019-01-26T05:48:27Z","publish_end_date": "2019-06-16T05:48:27Z"}
{ "index" : { "_index" : "sadaf", "_type" : "movies", "_id" : "2" } }
{"name": "spiderman climbs", "storeId": "2","gener": "Action","languageId": "2","category": "Hollywood","publish_date": "2019-03-26T05:48:27Z","publish_end_date": "2019-06-16T05:48:27Z"}
{ "index" : { "_index" : "sadaf", "_type" : "movies", "_id" : "3" } }
{"name": "spiderman antman","storeId": "2","gener": "Action","languageId": "2","category": "Hollywood","publish_date": "2019-05-26T05:48:27Z","publish_end_date": "2019-06-16T05:48:27Z"}
{ "index" : { "_index" : "sadaf", "_type" : "movies", "_id" : "4" } }
{"name": "spiderman batman","storeId": "2","gener": "Action","languageId": "2","category": "Hollywood"}
{ "index" : { "_index" : "sadaf", "_type" : "movies", "_id" : "5" } }
{"name": "spiderman superman","storeId": "2","gener": "Action","languageId": "2","category": "Hollywood"}

```

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [March 31, 2019, 7:01am UTC](https://discuss.elastic.co/t/elasticsearch-6-5-java-high-level-rest-client-must-querybuilders-rangequery-and-mustnot-querybuilders-existsquery-cmbination-not-working/174641/14 "2019-03-31T07:01:35Z")

</div>

Here is what you should do:

```auto
DELETE sadaf
POST _bulk
{ "index" : { "_index" : "sadaf", "_type" : "movies", "_id" : "1" } }
{"name": "spiderman walks","storeId": "2","gener": "Action","languageId": "2","category": "Hollywood","publish_date": "2019-01-26T05:48:27Z","publish_end_date": "2019-06-16T05:48:27Z"}
{ "index" : { "_index" : "sadaf", "_type" : "movies", "_id" : "2" } }
{"name": "spiderman climbs", "storeId": "2","gener": "Action","languageId": "2","category": "Hollywood","publish_date": "2019-03-26T05:48:27Z","publish_end_date": "2019-06-16T05:48:27Z"}
{ "index" : { "_index" : "sadaf", "_type" : "movies", "_id" : "3" } }
{"name": "spiderman antman","storeId": "2","gener": "Action","languageId": "2","category": "Hollywood","publish_date": "2019-05-26T05:48:27Z","publish_end_date": "2019-06-16T05:48:27Z"}
{ "index" : { "_index" : "sadaf", "_type" : "movies", "_id" : "4" } }
{"name": "spiderman batman","storeId": "2","gener": "Action","languageId": "2","category": "Hollywood"}
{ "index" : { "_index" : "sadaf", "_type" : "movies", "_id" : "5" } }
{"name": "spiderman superman","storeId": "2","gener": "Action","languageId": "2","category": "Hollywood"}

GET sadaf/_search
{
  "query": {
    "bool": {
      "filter": {
        "bool": {
          "should": [
            {
              "bool": {
                "must_not": [
                  {
                    "exists": {
                      "field": "publish_date"
                    }
                  }
                ]
              }
            },
            {
              "range": {
                "publish_date": {
                  "to": "now"
                }
              }
            }
          ]
        }
      }
    }
  }
}

```

If you don't succeed in translating this to Java, let me know.

---

<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:** [March 31, 2019, 9:51am UTC](https://discuss.elastic.co/t/elasticsearch-6-5-java-high-level-rest-client-must-querybuilders-rangequery-and-mustnot-querybuilders-existsquery-cmbination-not-working/174641/15 "2019-03-31T09:51:31Z")

</div>

Thanks a lot,The query is working fine and yes please help me to translate it to java,I have tried but failed to make the same query as yours.  
getting problem to place both the range and exists inside should.

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [March 31, 2019, 10:23am UTC](https://discuss.elastic.co/t/elasticsearch-6-5-java-high-level-rest-client-must-querybuilders-rangequery-and-mustnot-querybuilders-existsquery-cmbination-not-working/174641/16 "2019-03-31T10:23:00Z")

</div>

What did you try so far to mimic the code I shared?

---

<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:** [March 31, 2019, 10:33am UTC](https://discuss.elastic.co/t/elasticsearch-6-5-java-high-level-rest-client-must-querybuilders-rangequery-and-mustnot-querybuilders-existsquery-cmbination-not-working/174641/17 "2019-03-31T10:33:48Z")

</div>

Yes just that part.

This is the code which I have before-

```auto
BoolQueryBuilder startDateQuery = new BoolQueryBuilder()
		           .should(QueryBuilders.rangeQuery("publish_date").lte("now"))
		           .mustNot(QueryBuilders.existsQuery("publish_date"))

```

And now I am trying to place the rangeQuery and existsQuery inside one should according to your query but failed-

```auto
BoolQueryBuilder startDateQuery = new BoolQueryBuilder()
		           .should((QueryBuilders.rangeQuery("publish_date").lte("now"),BoolQueryBuilder.mustNot(QueryBuilders.existsQuery("publish_date")))

```

I know this is not possible as should can take only one QueryBuilder in the argument,so could you please help me to do so

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [March 31, 2019, 2:51pm UTC](https://discuss.elastic.co/t/elasticsearch-6-5-java-high-level-rest-client-must-querybuilders-rangequery-and-mustnot-querybuilders-existsquery-cmbination-not-working/174641/18 "2019-03-31T14:51:23Z")

</div>

Something like this:

```auto
BoolQueryBuilder boolQuery = QueryBuilders.boolQuery()
  .should(QueryBuilders.rangeQuery("publish_date").lte("now"))
  .should(QueryBuilders.boolQuery().mustNot(QueryBuilders.existsQuery("publish_date")));

```

---

<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:** [March 31, 2019, 3:06pm UTC](https://discuss.elastic.co/t/elasticsearch-6-5-java-high-level-rest-client-must-querybuilders-rangequery-and-mustnot-querybuilders-existsquery-cmbination-not-working/174641/19 "2019-03-31T15:06:51Z")

</div>

I have just made this query before sometime and I was about to update my last reply but anyways Thanks a lot for your help.😊

---

<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:** [April 28, 2019, 3:07pm UTC](https://discuss.elastic.co/t/elasticsearch-6-5-java-high-level-rest-client-must-querybuilders-rangequery-and-mustnot-querybuilders-existsquery-cmbination-not-working/174641/20 "2019-04-28T15:07:07Z")

</div>

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