Full-Text Search Functionsedit
This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.
Search functions should be used when performing full-text search, namely
when the MATCH or QUERY predicates are being used.
Outside a, so-called, search context, these functions will return default values
such as 0 or NULL.
SCOREedit
Input: None, Output: double
Returns the relevance of a given input to the executed query. The higher score, the more relevant the data.
When doing multiple text queries in the WHERE clause then, their scores will be
combined using the same rules as Elasticsearch’s
bool query.
Typically SCORE is used for ordering the results of a query based on their relevance:
SELECT SCORE(), * FROM library WHERE MATCH(name, 'dune') ORDER BY SCORE() DESC;
SCORE() | author | name | page_count | release_date
---------------+---------------+-------------------+---------------+--------------------
2.288635 |Frank Herbert |Dune |604 |1965-06-01T00:00:00Z
1.8893257 |Frank Herbert |Dune Messiah |331 |1969-10-15T00:00:00Z
1.6086555 |Frank Herbert |Children of Dune |408 |1976-04-21T00:00:00Z
1.4005898 |Frank Herbert |God Emperor of Dune|454 |1981-05-28T00:00:00Z
However, it is perfectly fine to return the score without sorting by it:
SELECT SCORE() AS score, name, release_date FROM library WHERE QUERY('dune') ORDER BY YEAR(release_date) DESC;
score | name | release_date
---------------+-------------------+--------------------
1.4005898 |God Emperor of Dune|1981-05-28T00:00:00Z
1.6086555 |Children of Dune |1976-04-21T00:00:00Z
1.8893257 |Dune Messiah |1969-10-15T00:00:00Z
2.288635 |Dune |1965-06-01T00:00:00Z