Full-text search functions
Search functions should be used when performing full-text search, namely when theMATCH orQUERY predicates are being used. Outside a, so-called, search context, these functions will return default values such as0 orNULL.
Elasticsearch SQL optimizes all queries executed against Elasticsearch depending on the scoring needs. Usingtrack_scores on the search request or_doc sorting that disables scores calculation, Elasticsearch SQL instructs Elasticsearch not to compute scores when these are not needed. For example, every time aSCORE() function is encountered in the SQL query, the scores are computed.
MATCH( field_exp, constant_exp [, options])Input:
- field(s) to match
- matching text
- additional parameters; optional
Description: A full-text search option, in the form of a predicate, available in Elasticsearch SQL that gives the user control over powerfulmatch andmulti_match Elasticsearch queries.
The first parameter is the field or fields to match against. In case it receives one value only, Elasticsearch SQL will use amatch query to perform the search:
SELECT author, name FROM library WHERE MATCH(author, 'frank'); author | name---------------+-------------------Frank Herbert |DuneFrank Herbert |Dune MessiahFrank Herbert |Children of DuneFrank Herbert |God Emperor of DuneHowever, it can also receive a list of fields and their corresponding optionalboost value. In this case, Elasticsearch SQL will use amulti_match query to match the documents:
SELECT author, name, SCORE() FROM library WHERE MATCH('author^2,name^5', 'frank dune'); author | name | SCORE()---------------+-------------------+---------------Frank Herbert |Dune |11.443176Frank Herbert |Dune Messiah |9.446629Frank Herbert |Children of Dune |8.043278Frank Herbert |God Emperor of Dune|7.0029488Themulti_match query in Elasticsearch has the option ofper-field boosting that gives preferential weight (in terms of scoring) to fields being searched in, using the^ character. In the example above, thename field has a greater weight in the final score than theauthor field when searching forfrank dune text in both of them.
Both options above can be used in combination with the optional third parameter of theMATCH() predicate, where one can specify additional configuration parameters (separated by semicolon;) for eithermatch ormulti_match queries. For example:
SELECT author, name, SCORE() FROM library WHERE MATCH(name, 'to the star', 'operator=OR;fuzziness=AUTO:1,5;minimum_should_match=1')ORDER BY SCORE() DESC LIMIT 2; author | name | SCORE()-----------------+------------------------------------+---------------Douglas Adams |The Hitchhiker's Guide to the Galaxy|3.1756816Peter F. Hamilton|Pandora's Star |3.0997515The allowed optional parameters for a single-fieldMATCH() variant (for thematch Elasticsearch query) are:analyzer,auto_generate_synonyms_phrase_query,lenient,fuzziness,fuzzy_transpositions,fuzzy_rewrite,minimum_should_match,operator,max_expansions,prefix_length.
The allowed optional parameters for a multi-fieldMATCH() variant (for themulti_match Elasticsearch query) are:analyzer,auto_generate_synonyms_phrase_query,lenient,fuzziness,fuzzy_transpositions,fuzzy_rewrite,minimum_should_match,operator,max_expansions,prefix_length,slop,tie_breaker,type.
QUERY( constant_exp [, options])Input:
- query text
- additional parameters; optional
Description: Just likeMATCH,QUERY is a full-text search predicate that gives the user control over thequery_string query in Elasticsearch.
The first parameter is basically the input that will be passed as is to thequery_string query, which means that anything thatquery_string accepts in itsquery field can be used here as well:
SELECT author, name, SCORE() FROM library WHERE QUERY('name:dune'); author | name | SCORE()---------------+-------------------+---------------Frank Herbert |Dune |2.2886353Frank Herbert |Dune Messiah |1.8893257Frank Herbert |Children of Dune |1.6086556Frank Herbert |God Emperor of Dune|1.4005898A more advanced example, showing more of the features thatquery_string supports, of course possible with Elasticsearch SQL:
SELECT author, name, page_count, SCORE() FROM library WHERE QUERY('_exists_:"author" AND page_count:>200 AND (name:/star.*/ OR name:duna~)'); author | name | page_count | SCORE()------------------+-------------------+---------------+---------------Frank Herbert |Dune |604 |3.7164764Frank Herbert |Dune Messiah |331 |3.4169943Frank Herbert |Children of Dune |408 |3.2064917Frank Herbert |God Emperor of Dune|454 |3.0504425Peter F. Hamilton |Pandora's Star |768 |3.0Robert A. Heinlein|Starship Troopers |335 |3.0The query above uses the_exists_ query to select documents that have values in theauthor field, a range query forpage_count and regex and fuzziness queries for thename field.
If one needs to customize various configuration options thatquery_string exposes, this can be done using the secondoptional parameter. Multiple settings can be specified separated by a semicolon;:
SELECT author, name, SCORE() FROM library WHERE QUERY('dune god', 'default_operator=and;default_field=name'); author | name | SCORE()---------------+-------------------+---------------Frank Herbert |God Emperor of Dune|3.6984892The allowed optional parameters forQUERY() are:allow_leading_wildcard,analyze_wildcard,analyzer,auto_generate_synonyms_phrase_query,default_field,default_operator,enable_position_increments,escape,fuzziness,fuzzy_max_expansions,fuzzy_prefix_length,fuzzy_rewrite,fuzzy_transpositions,lenient,max_determinized_states,minimum_should_match,phrase_slop,rewrite,quote_analyzer,quote_field_suffix,tie_breaker,time_zone,type.
SCORE()Input:none
Output:double numeric value
Description: Returns therelevance of a given input to the executed query. The higher score, the more relevant the data.
When doing multiple text queries in theWHERE clause then, their scores will be combined using the same rules as Elasticsearch'sbool query.
TypicallySCORE 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.2886353 |Frank Herbert |Dune |604 |1965-06-01T00:00:00Z1.8893257 |Frank Herbert |Dune Messiah |331 |1969-10-15T00:00:00Z1.6086556 |Frank Herbert |Children of Dune |408 |1976-04-21T00:00:00Z1.4005898 |Frank Herbert |God Emperor of Dune|454 |1981-05-28T00:00:00ZHowever, 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:00Z1.6086556 |Children of Dune |1976-04-21T00:00:00Z1.8893257 |Dune Messiah |1969-10-15T00:00:00Z2.2886353 |Dune |1965-06-01T00:00:00Z