# Filter with best match approach

**URL:** <https://discuss.elastic.co/t/filter-with-best-match-approach/182936>\
**Category:** Elasticsearch\
**Created:** [May 27, 2019, 2:59pm UTC](https://discuss.elastic.co/t/filter-with-best-match-approach/182936 "2019-05-27T14:59:40Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![AvishaiK](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/avishaik/32/46902_2.png) [@AvishaiK](https://discuss.elastic.co/u/AvishaiK)\
**Post date:** [May 27, 2019, 2:59pm UTC](https://discuss.elastic.co/t/filter-with-best-match-approach/182936/1 "2019-05-27T14:59:40Z")

</div>

Hello,

I'm struggling with the correct operator for this query logic:  
I need to match the exact value or to treat it as a null and filter/demote that option  
For example i have this data in elastic:

## document | field1 | field2 | field3 | field4| field5 |

```
  #1 | must | 10 | 20 | 30 | 40 |  
  #2 | must | 10 | 50 | null | 40 |
  #3 | must | 10 | null | 30 | 90 |
  #4 | must | 10 | null | 30 | 100 |
  #5 | must | 10 | 20 | null | null |
  #6 | must | null | null | null | null |

```

**The user asked for:**  
field1: must (must be in the document)  
field2: 10  
field3: 20  
field4: 90  
field5: 100

**Expected scoring result:**  
document #5 (best)  
document #6  
**all other should be filtered**

**Behavior should be like this:**  
When querying a document it should ask every field if the field has the exact data that the user requested or the field is null (with no [other] data), both will be elected, in addition document #5 will get a higher score because that document had a 2/4 fields match which is better then #6 (non matched)

Regards,  
Avishai.

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [May 29, 2019, 7:23am UTC](https://discuss.elastic.co/t/filter-with-best-match-approach/182936/2 "2019-05-29T07:23:00Z")

</div>

Hey,

you may want to take a look at the [bool query](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-bool-query.html) and especially at the should clauses, which can influence scoreing. This way you could increase the score for an exact match.

Also the [explain API](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-explain.html) is super useful to understand how the score is calculated when you want to know why a document is scored higher than another

--Alex

---

<div class="post-metadata">

**Author:** ![AvishaiK](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/avishaik/32/46902_2.png) [@AvishaiK](https://discuss.elastic.co/u/AvishaiK)\
**Post date:** [May 29, 2019, 9:42am UTC](https://discuss.elastic.co/t/filter-with-best-match-approach/182936/3 "2019-05-29T09:42:26Z")

</div>

Hi,

Thanks for the answer.  
I've tried bool query, but i still did not get the expected results.

This is basically what i need it to do:

SELECT \*  
FROM t  
WHERE  
(field1 == 10 OR field1 IS NULL)  
AND  
(field2 == 10 OR field2 IS NULL)  
AND  
(field3 == 10 OR field3 IS NULL)  
AND  
(field4 == 10 OR field4 IS NULL)  
AND  
(field5 == 10 OR field5 IS NULL)

Regards,  
Avishai.

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [May 29, 2019, 11:37am UTC](https://discuss.elastic.co/t/filter-with-best-match-approach/182936/4 "2019-05-29T11:37:10Z")

</div>

try a bool query with a `must` clause. Inside of that must clause you have five `bool` queries with two should clauses, one a match query, and one with another `bool` query that contains `must_not` clause, which contains an `exists` query. There may be easier ways, but this way it should work.

Note that exists really checks if that field exists. If the value of that field is an empty string, this is something different.

---

<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 26, 2019, 11:37am UTC](https://discuss.elastic.co/t/filter-with-best-match-approach/182936/5 "2019-06-26T11:37:19Z")

</div>

Closed on June 26, 2019, 11:37am UTC

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