# Range queries with should clause not working

**URL:** <https://discuss.elastic.co/t/range-queries-with-should-clause-not-working/334764>\
**Category:** Elasticsearch\
**Created:** [May 31, 2023, 7:58am UTC](https://discuss.elastic.co/t/range-queries-with-should-clause-not-working/334764 "2023-05-31T07:58:19Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![\_baba](https://avatars.discourse-cdn.com/v4/letter/_/ecb155/32.png) [@\_baba](https://discuss.elastic.co/u/_baba)\
**Post date:** [May 31, 2023, 7:58am UTC](https://discuss.elastic.co/t/range-queries-with-should-clause-not-working/334764/1 "2023-05-31T07:58:19Z")

</div>

Hi,

I'm trying below range query with must and should clause:

Product id can range from 1 to 1000.  
I'm using below query to fetch product\_id between 1 to 99 or product\_id = 100. However I can only see the must clause executed and the result has all the records with product\_id in range of 1 to 99. I also want to fetch a record with product\_id 100.

{  
"query": {  
"bool": {  
"must": [  
{  
"range": {  
"product\_id ": {  
"from": 90,  
"to": "99"  
}  
}  
}  
],  
"should": [  
{"bool": {  
"must": [  
{  
"term": {  
"product\_id ": {  
"value": "100"  
}  
}  
}]}}  
]  
}  
}  
}

Please suggest.

---

<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:** [May 31, 2023, 8:53am UTC](https://discuss.elastic.co/t/range-queries-with-should-clause-not-working/334764/2 "2023-05-31T08:53:51Z")

</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 is something anyone can **copy and paste in Kibana dev console** , click on the run button to reproduce your use case. It will help readers to understand, reproduce and if needed fix your problem. It will also most likely help to get a faster answer.

---

<div class="post-metadata">

**Author:** ![\_baba](https://avatars.discourse-cdn.com/v4/letter/_/ecb155/32.png) [@\_baba](https://discuss.elastic.co/u/_baba)\
**Post date:** [May 31, 2023, 12:16pm UTC](https://discuss.elastic.co/t/range-queries-with-should-clause-not-working/334764/3 "2023-05-31T12:16:53Z")

</div>

Hi,

Here is the recreation steps:

```auto
PUT /product
{
    "mappings" : {
        "properties" : {
			"product_id": { "type" : "long" },
			"product_name": { "type" : "keyword" }
        }}}
        

POST product/_doc
{
			"product_id": 22,
			"product_name": "abc"
}

POST product/_doc
{
			"product_id": 36,
			"product_name": "mobile"
}

POST product/_doc
{
			"product_id": 58,
			"product_name": "tablet"
}

POST product/_doc
{
			"product_id": 87,
			"product_name": "watch"
}

POST product/_doc
{
			"product_id": 100,
			"product_name": "footwear"
}

Execute below query-

GET product/_search?size=100
{
    "query": {
        "bool": {
            "must": [
                {
                    "range": {
                        "product_id": {
                            "from": 1,
                            "to": "99"
                        }
                    }
                }
            ],
            "should": [
              
                        {
                            "term": {
                                "product_id": {
                                    "value": "100"
                                }
                            }
                        }
            ]
        }
    }
}

Result:
{
  "took" : 735,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 4,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "product",
        "_type" : "_doc",
        "_id" : "P0GzcYgB6QnR66-pMZvJ",
        "_score" : 1.0,
        "_source" : {
          "product_id" : 36,
          "product_name" : "mobile"
        }
      },
      {
        "_index" : "product",
        "_type" : "_doc",
        "_id" : "3_CycYgBdvuNheCty-RK",
        "_score" : 1.0,
        "_source" : {
          "product_id" : 22,
          "product_name" : "abc"
        }
      },
      {
        "_index" : "product",
        "_type" : "_doc",
        "_id" : "4PCzcYgBdvuNheCtsuTl",
        "_score" : 1.0,
        "_source" : {
          "product_id" : 87,
          "product_name" : "watch"
        }
      },
      {
        "_index" : "product",
        "_type" : "_doc",
        "_id" : "QEGzcYgB6QnR66-pbptD",
        "_score" : 1.0,
        "_source" : {
          "product_id" : 58,
          "product_name" : "tablet"
        }
      }
    ]
  }
}

```

Expected result: Result should also display the record with product\_id - 100.  
Actual Result: Result doesn't have the record with product\_id - 100.

---

<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:** [May 31, 2023, 12:58pm UTC](https://discuss.elastic.co/t/range-queries-with-should-clause-not-working/334764/4 "2023-05-31T12:58:54Z")

</div>

> [@\_baba](#):
>
> Expected result: Result should also display the record with product\_id - 100.

Nope. Because of the **must** clause.

Put everything within the **should** array.

---

<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:** [June 28, 2023, 12:59pm UTC](https://discuss.elastic.co/t/range-queries-with-should-clause-not-working/334764/5 "2023-06-28T12:59:10Z")

</div>

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