Functions

The SQL language supports all SQL plugin common functions, including relevance search, but also introduces a few function synonyms, which are available in SQL only. These synonyms are provided by the V1 engine. For more information, see Limitations.

Match query

The MATCHQUERY and MATCH_QUERY functions are synonyms for the MATCH relevance function. They don’t accept additional arguments but provide an alternate syntax.

Syntax

To use matchquery or match_query, pass in your search query and the field name that you want to search against:

  1. match_query(field_expression, query_expression[, option=<option_value>]*)
  2. matchquery(field_expression, query_expression[, option=<option_value>]*)
  3. field_expression = match_query(query_expression[, option=<option_value>]*)
  4. field_expression = matchquery(query_expression[, option=<option_value>]*)

You can specify the following options in any order:

  • analyzer
  • boost

Example

You can use MATCHQUERY to replace MATCH:

  1. SELECT account_number, address
  2. FROM accounts
  3. WHERE MATCHQUERY(address, 'Holmes')

Alternatively, you can use MATCH_QUERY to replace MATCH:

  1. SELECT account_number, address
  2. FROM accounts
  3. WHERE address = MATCH_QUERY('Holmes')

The results contain documents in which the address contains “Holmes”:

account_numberaddress
1880 Holmes Lane

Multi-match

There are three synonyms for MULTI_MATCH, each with a slightly different syntax. They accept a query string and a fields list with weights. They can also accept additional optional parameters.

Syntax

  1. multimatch('query'=query_expression[, 'fields'=field_expression][, option=<option_value>]*)
  2. multi_match('query'=query_expression[, 'fields'=field_expression][, option=<option_value>]*)
  3. multimatchquery('query'=query_expression[, 'fields'=field_expression][, option=<option_value>]*)

The fields parameter is optional and can contain a single field or a comma-separated list (white space characters are not allowed). The weight for each field is optional and is specified after the field name. It should be delimited by the caret character – ^ – without white space.

Example

The following queries show the fields parameter of a multi-match query with a single field and a field list:

  1. multi_match('fields' = "Tags^2,Title^3.4,Body,Comments^0.3", ...)
  2. multi_match('fields' = "Title", ...)

You can specify the following options in any order:

  • analyzer
  • boost
  • slop
  • type
  • tie_breaker
  • operator

Query string

The QUERY function is a synonym for QUERY_STRING.

Syntax

  1. query('query'=query_expression[, 'fields'=field_expression][, option=<option_value>]*)

The fields parameter is optional and can contain a single field or a comma-separated list (white space characters are not allowed). The weight for each field is optional and is specified after the field name. It should be delimited by the caret character – ^ – without white space.

Example

The following queries show the fields parameter of a multi-match query with a single field and a field list:

  1. query('fields' = "Tags^2,Title^3.4,Body,Comments^0.3", ...)
  2. query('fields' = "Tags", ...)

You can specify the following options in any order:

  • analyzer
  • boost
  • slop
  • default_field

Example of using query_string in SQL and PPL queries:

The following is a sample REST API search request in OpenSearch DSL.

  1. GET accounts/_search
  2. {
  3. "query": {
  4. "query_string": {
  5. "query": "Lane Street",
  6. "fields": [ "address" ],
  7. }
  8. }
  9. }

The request above is equivalent to the following query function:

  1. SELECT account_number, address
  2. FROM accounts
  3. WHERE query('address:Lane OR address:Street')

The results contain addresses that contain “Lane” or “Street”:

account_numberaddress
1880 Holmes Lane
6671 Bristol Street
13789 Madison Street

Match phrase

The MATCHPHRASEQUERY function is a synonym for MATCH_PHRASE.

Syntax

  1. matchphrasequery(query_expression, field_expression[, option=<option_value>]*)

You can specify the following options in any order:

  • analyzer
  • boost
  • slop

Score query

To return a relevance score along with every matching document, use the SCORE, SCOREQUERY, or SCORE_QUERY functions.

Syntax

The SCORE function expects two arguments. The first argument is the MATCH_QUERY expression. The second argument is an optional floating-point number to boost the score (the default value is 1.0):

  1. SCORE(match_query_expression, score)
  2. SCOREQUERY(match_query_expression, score)
  3. SCORE_QUERY(match_query_expression, score)

Example

The following example uses the SCORE function to boost the documents’ scores:

  1. SELECT account_number, address, _score
  2. FROM accounts
  3. WHERE SCORE(MATCH_QUERY(address, 'Lane'), 0.5) OR
  4. SCORE(MATCH_QUERY(address, 'Street'), 100)
  5. ORDER BY _score

The results contain matches with corresponding scores:

account_numberaddressscore
1880 Holmes Lane0.5
6671 Bristol Street100
13789 Madison Street100

Wildcard query

To search documents by a given wildcard, use the WILDCARDQUERY or WILDCARD_QUERY functions.

Syntax

  1. wildcardquery(field_expression, query_expression[, boost=<value>])
  2. wildcard_query(field_expression, query_expression[, boost=<value>])

Example

The following example uses a wildcard query:

  1. SELECT account_number, address
  2. FROM accounts
  3. WHERE wildcard_query(address, '*Holmes*');

The results contain documents that match the wildcard expression:

account_numberaddress
1880 Holmes Lane