Filter with best match approach

[AvishaiK]

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.

[spinscale (Alexander Reelsen)]

Hey,

you may want to take a look at the bool query 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 is super useful to understand how the score is calculated when you want to know why a document is scored higher than another

--Alex

[AvishaiK]

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.

[spinscale (Alexander Reelsen)]

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.