Full-Text Search Functionsedit

This functionality is in beta and is subject to change. The design and code is less mature than official GA features and is being provided as-is with no warranties. Beta features 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

Synopsis:

SCORE()

Input: none

Output: double numeric value

Description: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