# OR statement braking the "NOT" statement ElasticSearch

**URL:** <https://discuss.elastic.co/t/or-statement-braking-the-not-statement-elasticsearch/31875>\
**Category:** Elasticsearch\
**Created:** [October 8, 2015, 7:37pm UTC](https://discuss.elastic.co/t/or-statement-braking-the-not-statement-elasticsearch/31875 "2015-10-08T19:37:06Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![acv2](https://avatars.discourse-cdn.com/v4/letter/a/a4c791/32.png) [@acv2](https://discuss.elastic.co/u/acv2)\
**Post date:** [October 8, 2015, 7:37pm UTC](https://discuss.elastic.co/t/or-statement-braking-the-not-statement-elasticsearch/31875/1 "2015-10-08T19:37:06Z")

</div>

Hi Everyone,  
I'm here to give back to community, i spent hours looking for an answer for my problem without luck, so I decided to share my findings with you all, so we can all take a look on the mystic of elasticsearch.

My problem was very simple, but not easy as it seems:  
i needed to search for a field where the fields match a keyword or it were simply blank either null  
ex:

SELECT \* FROM bucket1 WHERE country="canada" AND (language=en OR language=NULL)

simple right?

for my query i constructed the following:  
bucket/document/\_search [POST]  
{  
"size": 10,  
"query": {  
"query\_string": {  
"query": "country:ca AND region1:quebec AND (language:en OR -language:\*)"  
}  
}  
}

It brought me 0 (zero) results what made me think.. because when i did the split query, it broght me around 35k results  
ex:  
{  
"size": 10,  
"query": {  
"query\_string": {  
"query": "country:ca AND region1:quebec AND (language:en)"  
}  
}  
}  
32k results  
THEN  
{  
"size": 10,  
"query": {  
"query\_string": {  
"query": "country:ca AND region1:quebec AND (-language:\*)"  
}  
}  
}  
3k results

so i decided go for different approach  
{  
"size": 10,  
"query": {  
"query\_string": {  
"query": "(region1:quebec AND -geo\_city:\* ) OR (region1:quebec AND geo\_city:montreal )"  
}  
}  
}  
35K results  
conclusion NOT statement is broking everything if set with an OR statement.  
after that i finally did the following:  
{  
"size": 10,  
"query": {  
"query\_string": {  
"query": "country:ca AND region1:quebec AND ((language:en) OR (-language:\*))"  
}  
}  
}  
35k results, and it was working

now im looking for a different approach to make this query more efficient, cuz the NOT statement is taking really long time to excecute, around x20 factor than without the NOT statement.

i hope to have some feedbacks on this =)

thanks in advance

---

<div class="post-metadata">

**Author:** ![softwaredoug](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/softwaredoug/32/22681_2.png) [@softwaredoug](https://discuss.elastic.co/u/softwaredoug)\
**Post date:** [October 9, 2015, 3:04am UTC](https://discuss.elastic.co/t/or-statement-braking-the-not-statement-elasticsearch/31875/2 "2015-10-09T03:04:01Z")

</div>

A OR NOT B is actually not a possible thing to express surprising enough in  
the query syntax.

The reason? Lucene's operators, though often appearing like "OR" "AND" etc  
are actually not binary operators. Instead of OR that takes two parameters,  
you have SHOULD that takes one parameter. Instead of AND that takes two  
parameters, you really have MUST that takes one paramater. NOT as you expect operates on a single query, using MUST\_NOT.

In other words

A OR B

is ACTUALLY

SHOULD(A) SHOULD(B)

A OR NOT B

you'd imagine would be something like

SHOULD(A) MUST\_NOT(SHOULD(B))

but you can only run MUST\_NOT(B) or you can run SHOULD(B) or you can run  
MUST(B).

MUST\_NOT(SHOULD(B)) or SHOULD(MUST\_NOT(B)) is kind of nonsensical if you think about it. Its saying Not  
having B would be nice and docs with this property should be favored in  
scoring. Sadly you can only exclude with NOT you can't bump things up or  
down via scoring with NOT. NOT is purely an exclusionary operator.

---

<div class="post-metadata">

**Author:** ![softwaredoug](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/softwaredoug/32/22681_2.png) [@softwaredoug](https://discuss.elastic.co/u/softwaredoug)\
**Post date:** [October 9, 2015, 3:15am UTC](https://discuss.elastic.co/t/or-statement-braking-the-not-statement-elasticsearch/31875/3 "2015-10-09T03:15:14Z")

</div>

If you want to be exacting and precise with boolean queries, I strongly recommend using the [bool query](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-bool-query.html) directly.

---

<div class="post-metadata">

**Author:** ![acv2](https://avatars.discourse-cdn.com/v4/letter/a/a4c791/32.png) [@acv2](https://discuss.elastic.co/u/acv2)\
**Post date:** [October 9, 2015, 3:28am UTC](https://discuss.elastic.co/t/or-statement-braking-the-not-statement-elasticsearch/31875/4 "2015-10-09T03:28:17Z")

</div>

this was really enlightening, thank you for sharing i didnt know that

---

<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:** [July 5, 2017, 11:45pm UTC](https://discuss.elastic.co/t/or-statement-braking-the-not-statement-elasticsearch/31875/5 "2017-07-05T23:45:44Z")

</div>