# Boolean query with two conditions on a field **URL:** \ **Category:** Elasticsearch\ **Created:** [March 19, 2020, 9:01pm UTC](https://discuss.elastic.co/t/boolean-query-with-two-conditions-on-a-field/224313 "2020-03-19T21:01:28Z")\ **Posts on this page:** 15\ **Page:** 1
**Author:** ![Titan](https://avatars.discourse-cdn.com/v4/letter/t/7ba0ec/32.png) [@Titan](https://discuss.elastic.co/u/Titan)\ **Post date:** [March 19, 2020, 9:01pm UTC](https://discuss.elastic.co/t/boolean-query-with-two-conditions-on-a-field/224313/1 "2020-03-19T21:01:28Z")
**Applying range filter on a boolean field** In a search query, I am trying to get product records that are either not suppressed or if suppressed then the suppression range is either before current time or after current time. Here is the query that I came up with, which certainly have some issue. I could not find any relevant article on this/similar issue thus posting it here to get community/Elastic team's help: ```auto { "_source": { "includes": [ "ProductId", "Active", "ActiveFrom", "ActiveTo", "Suppressed", "SuppressedFrom", "SuppressedTo" ] }, "size": 1000, "query": { "constant_score": { "filter": { "bool": { "must": [ { "match": { "Active": true } }, { "range": { "ActiveFrom": { "lte": **CurrentTime** } } }, { "range": { "ActiveTo": { "gte": **CurrentTime** } } }, { "match": { "Suppressed": false } }, { "match": { "Suppressed": true }, "range": { "SuppressedFrom": { "gte": **CurrentTime** } } }, { "match": { "Suppressed": true }, "range": { "SuppressedTo": { "lte": **CurrentTime** } } } ] } } } } } ``` I am getting parsing exception **[match] malformed query, expected [END\_OBJECT] but found [FIELD\_NAME]** for the use of range clause after match clause where Suppressed is true. Thanks, T ---
**Author:** ![Opster\_Community1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/opster_community1/32/59062_2.png) [@Opster\_Community1](https://discuss.elastic.co/u/Opster_Community1)\ **Post date:** [March 20, 2020, 3:25am UTC](https://discuss.elastic.co/t/boolean-query-with-two-conditions-on-a-field/224313/2 "2020-03-20T03:25:56Z")
You query seem to be quite different from the condition you explained. Can you explain with sample documents. ---
**Author:** ![Titan](https://avatars.discourse-cdn.com/v4/letter/t/7ba0ec/32.png) [@Titan](https://discuss.elastic.co/u/Titan)\ **Post date:** [March 20, 2020, 1:45pm UTC](https://discuss.elastic.co/t/boolean-query-with-two-conditions-on-a-field/224313/3 "2020-03-20T13:45:48Z")
Thanks Nishant for the reply. Perhaps, breaking it down will help a bit and you may update it with the clauses causing the error. Here is the query that worked fine without the Suppressed True and related range conditions: ```auto { "_source": { "includes": [ "ProductId", "Active", "ActiveFrom", "ActiveTo", "Suppressed", "SuppressedFrom", "SuppressedTo" ] }, "size": 1000, "query": { "constant_score": { "filter": { "bool": { "must": [ { "match": { "Active": true } }, { "range": { "ActiveFrom": { "lte": 1584646746467 } } }, { "range": { "ActiveTo": { "gte": 1584646746467 } } }, { "match": { "Suppressed": false } } ] } } } } } ``` The response of this query is: ```auto { "took": 2, "timed_out": false, "_shards": { "total": 5, "successful": 5, "skipped": 0, "failed": 0 }, "hits": { "total": 0, "max_score": null, "hits": [] } } ``` Can you extend this working query to accommodate the true Suppressed and SuppressedFrom and SuppressedTo ranges? ---
**Author:** ![itizir](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/itizir/32/63552_2.png) [@itizir](https://discuss.elastic.co/u/itizir)\ **Post date:** [March 20, 2020, 1:58pm UTC](https://discuss.elastic.co/t/boolean-query-with-two-conditions-on-a-field/224313/4 "2020-03-20T13:58:25Z")
Clauses in a `bool` `must`/`filter` are ANDed together, for OR you need to use `should`. Have a look [here](https://www.elastic.co/guide/en/elasticsearch/reference/7.6/query-dsl-bool-query.html). And you can nest `bool` queries... ---
**Author:** ![Opster\_Community1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/opster_community1/32/59062_2.png) [@Opster\_Community1](https://discuss.elastic.co/u/Opster_Community1)\ **Post date:** [March 21, 2020, 5:15am UTC](https://discuss.elastic.co/t/boolean-query-with-two-conditions-on-a-field/224313/5 "2020-03-21T05:15:26Z")
What you need is two bool queries withing the must clause either of which is true. So lets understand in pseudo code way. Lets assume you existing query as below: - `Active : true` =\> **A** - `ActiveFrom <= 1584646746467` =\> **B** - `ActiveTo >= 1584646746467` =\> **C** - `Suppressed: false` =\> **D** Current query : **A** AND **B** AND **C** AND **D** =\> **X** Another condition that you want to add: - `Suppressed: true` =\> **A'** - `SuppressedFrom <= 1584646746467` =\> **B'** - `SuppressedTo >= 1584646746467` =\> **C'** Required second query: **A'** AND **B'** AND **C'** =\> **Y** Final required query: **X** OR **Y** Query dsl: ``` { "query": { "bool": { "filter": [ { "bool": { "should": [ { X }, { Y } ] } } ] } } } ``` Further expanding **X** , **Y** ``` { "query": { "bool": { "filter": [ { "bool": { "should": [ { "bool": { "must": [ { A }, { B }, { C }, { D } ] } }, { "bool": { "must": [ { A' }, { B' }, { C' } ] } } ] } } ] } } } ``` I hope this will help you writing the correct query. ---
**Author:** ![Titan](https://avatars.discourse-cdn.com/v4/letter/t/7ba0ec/32.png) [@Titan](https://discuss.elastic.co/u/Titan)\ **Post date:** [March 21, 2020, 5:26am UTC](https://discuss.elastic.co/t/boolean-query-with-two-conditions-on-a-field/224313/6 "2020-03-21T05:26:04Z")
Terrific! This was basically what I had in mind but did not know the Elastic way! Got so spoiled by plain and traditional SQL. In fact, in this manner the suppressed True need to separate bool conditions, 1) Supressed True and SuppressedFrom \> specified date time, 2) Suppressed True and SuppressedTo \< specified date time, means three bool conditions but all based upon your idea!! Will give it a shot. Thanks so much! ---
**Author:** ![Titan](https://avatars.discourse-cdn.com/v4/letter/t/7ba0ec/32.png) [@Titan](https://discuss.elastic.co/u/Titan)\ **Post date:** [March 21, 2020, 5:27am UTC](https://discuss.elastic.co/t/boolean-query-with-two-conditions-on-a-field/224313/7 "2020-03-21T05:27:42Z")
Thanks so much for the tip and reference link, somehow I got the results I was looking for, but still not convinced why Elastic treats should as OR! Here is my updated query: ```auto { "_source": { "includes": [ "ProductId", "Active", "ActiveFrom", "ActiveTo", "Suppressed", "SuppressedFrom", "SuppressedTo" ] }, "size": 1000, "query": { "constant_score": { "filter": { "bool": { "must": [ { "match": { "Active": true } }, { "range": { "ActiveFrom": { "lte": 1584646746467 } } }, { "range": { "ActiveTo": { "gte": 1584646746467 } } }, { "match": { "Suppressed": false } } ], "should": [ { "match": { "Suppressed": true } }, { "range": { "SuppressedFrom": { "gte": 1584646746467 } } }, { "range": { "SuppressedTo": { "lte": 1584646746467 } } } ] } } } } } ``` ---
**Author:** ![Opster\_Community1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/opster_community1/32/59062_2.png) [@Opster\_Community1](https://discuss.elastic.co/u/Opster_Community1)\ **Post date:** [March 21, 2020, 5:35am UTC](https://discuss.elastic.co/t/boolean-query-with-two-conditions-on-a-field/224313/8 "2020-03-21T05:35:21Z")
> [@Titan](#): > > still not convinced why Elastic treats should as OR Actually `must`, `should` should not be taken as traditional sql way of `AND`, `OR` respectively. Instead they behave more of like `intersection` and `union`. Since I felt that you were stuck thinking in traditional way of SQL I tried to explain in that way 😉 ---
**Author:** ![Titan](https://avatars.discourse-cdn.com/v4/letter/t/7ba0ec/32.png) [@Titan](https://discuss.elastic.co/u/Titan)\ **Post date:** [March 22, 2020, 1:14am UTC](https://discuss.elastic.co/t/boolean-query-with-two-conditions-on-a-field/224313/9 "2020-03-22T01:14:08Z")
Thanks Nishant for bringing further clarity and the concept of intersections and unions which makes sense. Now comes another challenge, the record count of query 2 and query 3 matches but the record count of query 1 is different, however the returned records in query 1 satisfies the expectations. Can you throw some light how Elastic processes query 1, 2, and 3 probably with a reference to a tradition SQL execution plan? Query 1: ```auto { "_source": { "includes": [ "ProductId", "Active", "ActiveFrom", "ActiveTo", "Suppressed", "SuppressedFrom", "SuppressedTo" ] }, "size": 1000, "query": { "constant_score": { "filter": { "bool": { "must": [ { "match": { "Active": true } }, { "range": { "ActiveFrom": { "lte": 1584646746467 } } }, { "range": { "ActiveTo": { "gte": 1584646746467 } } }, { "match": { "Suppressed": false } } ], "should": [ { "match": { "Suppressed": true } }, { "range": { "SuppressedFrom": { "gte": 1584646746467 } } }, { "range": { "SuppressedTo": { "lte": 1584646746467 } } } ] } } } } } ``` Query 2: ```auto { "_source": { "includes": [ "ProductId", "Active", "ActiveFrom", "ActiveTo", "Suppressed", "SuppressedFrom", "SuppressedTo" ] }, "size": 1000, "query": { "constant_score": { "filter": { "bool": { "should": [ { "bool": { "must": [ { "match": { "Active": true } }, { "range": { "ActiveFrom": { "lte": 1584646746467 } } }, { "range": { "ActiveTo": { "gte": 1584646746467 } } }, { "match": { "Suppressed": false } } ] } }, { "bool": { "must": [ { "match": { "Active": true } }, { "range": { "ActiveFrom": { "lte": 1584646746467 } } }, { "range": { "ActiveTo": { "gte": 1584646746467 } } }, { "match": { "Suppressed": true } }, { "range": { "SuppressedFrom": { "gte": 1584646746467 } } } ] } }, { "bool": { "must": [ { "match": { "Active": true } }, { "range": { "ActiveFrom": { "lte": 1584646746467 } } }, { "range": { "ActiveTo": { "gte": 1584646746467 } } }, { "match": { "Suppressed": true } }, { "range": { "SuppressedTo": { "lte": 1584646746467 } } } ] } } ] } } } } } ``` Query 3: ```auto { "_source": { "includes": [ "ProductId", "Active", "ActiveFrom", "ActiveTo", "Suppressed", "SuppressedFrom", "SuppressedTo" ] }, "size": 1000, "query": { "constant_score": { "filter": { "bool": { "should": [ { "bool": { "must": [ { "match": { "Active": true } }, { "range": { "ActiveFrom": { "lte": 1584646746467 } } }, { "range": { "ActiveTo": { "gte": 1584646746467 } } }, { "match": { "Suppressed": false } } ] } }, { "bool": { "must": [ { "match": { "Active": true } }, { "range": { "ActiveFrom": { "lte": 1584646746467 } } }, { "range": { "ActiveTo": { "gte": 1584646746467 } } }, { "match": { "Suppressed": true } }, { "range": { "SuppressedFrom": { "gte": 1584646746467 } } }, { "range": { "SuppressedTo": { "lte": 1584646746467 } } } ] } } ] } } } } } ``` It may be a bit stretched out discussion now, but will help me knowing how does Elastic works. Its a new world for me! 😉 Thanks ---
**Author:** ![itizir](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/itizir/32/63552_2.png) [@itizir](https://discuss.elastic.co/u/itizir)\ **Post date:** [March 23, 2020, 9:31am UTC](https://discuss.elastic.co/t/boolean-query-with-two-conditions-on-a-field/224313/10 "2020-03-23T09:31:55Z")
Looks like your query 2 is the only one doing what you want: if query 3 returns the same, that may mean you don't have documents where only `SuppressedFrom` or `SuppressedTo` matches. Query 1 won't be returning documents that are `Suppressed`... In query 1, try removing `"Suppressed": false` from `must`, replacing `"Suppressed": true` with `"Suppressed": false` in the `should`, and adding `"minimum_should_match" : 1` to the bool query. ---
**Author:** ![Titan](https://avatars.discourse-cdn.com/v4/letter/t/7ba0ec/32.png) [@Titan](https://discuss.elastic.co/u/Titan)\ **Post date:** [March 23, 2020, 3:46pm UTC](https://discuss.elastic.co/t/boolean-query-with-two-conditions-on-a-field/224313/12 "2020-03-23T15:46:01Z")
Hi Diego, Thanks for the suggestion. After making the changes in query 1 as you suggested: Revised Query 1: ```auto { "_source": { "includes": [ "ProductId", "Active", "ActiveFrom", "ActiveTo", "Suppressed", "SuppressedFrom", "SuppressedTo" ] }, "size": 1000, "query": { "constant_score": { "filter": { "bool": { "must": [ { "match": { "Active": true } }, { "range": { "ActiveFrom": { "lte": 1584646746467 } } }, { "range": { "ActiveTo": { "gte": 1584646746467 } } } ], "should": [ { "match": { "Suppressed": false } }, { "range": { "SuppressedFrom": { "gte": 1584646746467 } } }, { "range": { "SuppressedTo": { "lte": 1584646746467 } } } ], "minimum_should_match" : 1 } } } } } ``` The record count of revised query 1 and Query 2 matched and meets the criteria, seems like original query 1 did not pick up some records, query 1 retrieved 22878 and revised query retrieved 23206. Query 2 seems to be formed in logical order and makes straight forward sense to me, but revised query 1 is a lot smaller and less complicated, just need to get my head around it. Can you share some details to understand the changes you suggested for query 1? Thanks ---
**Author:** ![itizir](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/itizir/32/63552_2.png) [@itizir](https://discuss.elastic.co/u/itizir)\ **Post date:** [March 23, 2020, 4:04pm UTC](https://discuss.elastic.co/t/boolean-query-with-two-conditions-on-a-field/224313/13 "2020-03-23T16:04:14Z")
What it does now, essentially, is `Active AND ActiveFromnow AND (!Suppressed OR SuppressedFrom>now OR SuppressedTo **Author:** ![Titan](https://avatars.discourse-cdn.com/v4/letter/t/7ba0ec/32.png) [@Titan](https://discuss.elastic.co/u/Titan)\ **Post date:** [March 24, 2020, 4:32am UTC](https://discuss.elastic.co/t/boolean-query-with-two-conditions-on-a-field/224313/14 "2020-03-24T04:32:53Z") Thanks a lot Diego for the further clarification and help. I have marked Revised Query 1 as the preferred solution and considering Query 2 as another solution with many thanks to Nishant as well for the breakthrough pseudo code and ideas. Cheers! ---
**Author:** ![itizir](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/itizir/32/63552_2.png) [@itizir](https://discuss.elastic.co/u/itizir)\ **Post date:** [March 24, 2020, 10:15am UTC](https://discuss.elastic.co/t/boolean-query-with-two-conditions-on-a-field/224313/15 "2020-03-24T10:15:48Z")
You're welcome. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [April 21, 2020, 10:15am UTC](https://discuss.elastic.co/t/boolean-query-with-two-conditions-on-a-field/224313/16 "2020-04-21T10:15:49Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.