Search API: Weighted Score and Ordering

Question

Hi Yuuvis Team

I are trying to return search results ordered by relevance. We have encountered the following questions/issues:

1. Can we sort by relevance/score without returning the column?
2. Is there a default sort when there is no ORDER BY defined or is the order random?
3. Sorting by Score does not work for us. We use Winter 2020. Is this supported in Winter 2020? We get an error:

POST: {"query":{"statement":"SELECT name, SCORE() s FROM person WHERE (name CONTAINS('xxx') AND name CONTAINS('xxx')) OR name CONTAINS('xxx') OR name CONTAINS('xxx') ORDER BY s ASC","skipCount":10,"maxItems":5}}

    "service": "search",

    "httpErrorCode": 422,

    "errorMessage": "ResponseException: method [GET], host [http://elasticsearch:9200], URI [/yuuvis/_search?request_cache=true], status line [HTTP/1.1 400 Bad Request]\n{\"error\":{\"root_cause\":[{\"type\":\"x_content_parse_exception\",\"reason\":\"[1:923] [_score] unknown field [unmapped_type]\"}],\"type\":\"x_content_parse_exception\",\"reason\":\"[1:923] [_score] unknown field [unmapped_type]\"},\"status\":400}",

To sort by the score value, you must assign an alias name to the SCORE () function.

Select all invoices and their score value where the field 'status' either contains 'reminder', 'discount' or 'delay', with the result list sorted in ascending order of alias s (score value).

SELECT *,SCORE() s FROM invoice WHERE status CONTAINS('reminder discount delay') ORDER BY s ASC;

Thank you in advance for any clarifications.

Kind regards

Nicole

Answer 1

Hi Nicole,

1. unfortunately it's not (yet) possible to sort results by it's score without returning the column
2. without using 'ORDER BY ...' there is no explicit sorting in default. for that reason ES uses it's '_score' sorting which is some kind of 'random', due to the constant_score which is used in filter context requests
3. this seems like a problem we've to solve, because it should be possible to sort by the _score field. I'll take a deep look into it

Kind Regards,

Jan

Comments

Hi Jan
Thanks for your answers/clarifications. Please let me know if I should file a separate bug report via E-Mail.
Kind regards
Nicole