Full-text search

Use SQL commands for full-text search. The SQL plugin supports a subset of full-text queries available in OpenSearch.

To learn about full-text queries in OpenSearch, see Full-text queries.

Match

Use the MATCH function to search documents that match a string, number, date, or boolean value for a given field.

Syntax

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

You can specify the following options in any order:

  • analyzer
  • auto_generate_synonyms_phrase
  • fuzziness
  • max_expansions
  • prefix_length
  • fuzzy_transpositions
  • fuzzy_rewrite
  • lenient
  • operator
  • minimum_should_match
  • zero_terms_query
  • boost

Please, refer to match query documentation for parameter description and supported values.

Example 1: Search the message field for the text “this is a test”:

  1. GET my_index/_search
  2. {
  3. "query": {
  4. "match": {
  5. "message": "this is a test"
  6. }
  7. }
  8. }

SQL query:

  1. SELECT message FROM my_index WHERE match(message, "this is a test")

PPL query:

  1. SOURCE=my_index | WHERE match(message, "this is a test") | FIELDS message

Example 2: Search the message field with the operator parameter:

  1. GET my_index/_search
  2. {
  3. "query": {
  4. "match": {
  5. "message": {
  6. "query": "this is a test",
  7. "operator": "and"
  8. }
  9. }
  10. }
  11. }

SQL query:

  1. SELECT message FROM my_index WHERE match(message, "this is a test", operator='and')

PPL query:

  1. SOURCE=my_index | WHERE match(message, "this is a test", operator='and') | FIELDS message

Example 3: Search the message field with the operator and zero_terms_query parameters:

  1. GET my_index/_search
  2. {
  3. "query": {
  4. "match": {
  5. "message": {
  6. "query": "to be or not to be",
  7. "operator": "and",
  8. "zero_terms_query": "all"
  9. }
  10. }
  11. }
  12. }

SQL query:

  1. SELECT message FROM my_index WHERE match(message, "this is a test", operator='and', zero_terms_query='all')

PPL query:

  1. SOURCE=my_index | WHERE match(message, "this is a test", operator='and', zero_terms_query='all') | FIELDS message

Multi-match

To search for text in multiple fields, use MULTI_MATCH function. This function maps to the multi_match query used in search engine, to returns the documents that match a provided text, number, date or boolean value with a given field or fields.

Syntax

The MULTI_MATCH function lets you boost certain fields using ^ character. Boosts are multipliers that weigh matches in one field more heavily than matches in other fields. The syntax allows to specify the fields in double quotes, single quotes, surrounded by backticks, or unquoted. Use star "*" to search all fields. Star symbol should be quoted.

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

The weight is optional and is specified after the field name. It could be delimited by the caret character – ^ or by whitespace. Please, refer to examples below:

  1. multi_match(["Tags" ^ 2, 'Title' 3.4, `Body`, Comments ^ 0.3], ...)
  2. multi_match(["*"], ...)

You can specify the following options for MULTI_MATCH in any order:

  • analyzer
  • auto_generate_synonyms_phrase
  • cutoff_frequency
  • fuzziness
  • fuzzy_transpositions
  • lenient
  • max_expansions
  • minimum_should_match
  • operator
  • prefix_length
  • tie_breaker
  • type
  • slop
  • zero_terms_query
  • boost

Please, refer to multi_match query documentation for parameter description and supported values.

For example, REST API search for Dale in either the firstname or lastname fields:

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

could be called from SQL using multi_match function

  1. SELECT firstname, lastname
  2. FROM accounts
  3. WHERE multi_match(['*name'], 'Dale')

or multi_match PPL function

  1. SOURCE=accounts | WHERE multi_match(['*name'], 'Dale') | fields firstname, lastname
firstnamelastname
DaleAdams

Query string

To split text based on operators, use the QUERY_STRING function. The QUERY_STRING function supports logical connectives, wildcard, regex, and proximity search. This function maps to the to the query_string query used in search engine, to return the documents that match a provided text, number, date or boolean value with a given field or fields.

Syntax

The QUERY_STRING function has syntax similar to MATCH_QUERY and lets you boost certain fields using ^ character. Boosts are multipliers that weigh matches in one field more heavily than matches in other fields. The syntax allows to specify the fields in double quotes, single quotes, surrounded by backticks, or unquoted. Use star "*" to search all fields. Star symbol should be quoted.

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

The weight is optional and is specified after the field name. It could be delimited by the caret character – ^ or by whitespace. Please, refer to examples below:

  1. query_string(["Tags" ^ 2, 'Title' 3.4, `Body`, Comments ^ 0.3], ...)
  2. query_string(["*"], ...)

You can specify the following options for QUERY_STRING in any order:

  • analyzer
  • allow_leading_wildcard
  • analyze_wildcard
  • auto_generate_synonyms_phrase_query
  • boost
  • default_operator
  • enable_position_increments
  • fuzziness
  • fuzzy_rewrite
  • escape
  • fuzzy_max_expansions
  • fuzzy_prefix_length
  • fuzzy_transpositions
  • lenient
  • max_determinized_states
  • minimum_should_match
  • quote_analyzer
  • phrase_slop
  • quote_field_suffix
  • rewrite
  • type
  • tie_breaker
  • time_zone

Please, refer to query_string query documentation for parameter description and supported values.

Example of using query_string in SQL and PPL queries:

The REST API search request

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

could be called from SQL

  1. SELECT account_number, address
  2. FROM accounts
  3. WHERE query_string(['address'], 'Lane Street', default_operator='OR')

or from PPL

  1. SOURCE=accounts | WHERE query_string(['address'], 'Lane Street', default_operator='OR') | fields account_number, address
account_numberaddress
1880 Holmes Lane
6671 Bristol Street
13789 Madison Street

Match phrase

To search for exact phrases, use MATCHPHRASE or MATCH_PHRASE functions.

Syntax

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

The MATCHPHRASE/MATCH_PHRASE functions let you specify the following options in any order:

  • analyzer
  • slop
  • zero_terms_query
  • boost

Please, refer to match_phrase query documentation for parameter description and supported values.

Example of using match_phrase in SQL and PPL queries:

The REST API search request

  1. GET accounts/_search
  2. {
  3. "query": {
  4. "match_phrase": {
  5. "address": {
  6. "query": "880 Holmes Lane"
  7. }
  8. }
  9. }
  10. }

could be called from SQL

  1. SELECT account_number, address
  2. FROM accounts
  3. WHERE match_phrase(address, '880 Holmes Lane')

or PPL

  1. SOURCE=accounts | WHERE match_phrase(address, '880 Holmes Lane') | FIELDS account_number, address
account_numberaddress
1880 Holmes Lane

Simple query string

The simple_query_string function maps to the simple_query_string query in OpenSearch. It returns the documents that match a provided text, number, date or boolean value with a given field or fields. The ^ lets you boost certain fields. Boosts are multipliers that weigh matches in one field more heavily than matches in other fields.

Syntax

The syntax allows to specify the fields in double quotes, single quotes, surrounded by backticks, or unquoted. Use star "*" to search all fields. Star symbol should be quoted.

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

The weight is optional and is specified after the field name. It could be delimited by the caret character – ^ or by whitespace. Please, refer to examples below:

  1. simple_query_string(["Tags" ^ 2, 'Title' 3.4, `Body`, Comments ^ 0.3], ...)
  2. simple_query_string(["*"], ...)

You can specify the following options for SIMPLE_QUERY_STRING in any order:

  • analyze_wildcard
  • analyzer
  • auto_generate_synonyms_phrase_query
  • boost
  • default_operator
  • flags
  • fuzzy_max_expansions
  • fuzzy_prefix_length
  • fuzzy_transpositions
  • lenient
  • minimum_should_match
  • quote_field_suffix

Please, refer to simple_query_string query documentation to check parameter meanings and available values.

Example of using simple_query_string in SQL and PPL queries:

The REST API search request

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

could be called from SQL

  1. SELECT account_number, address
  2. FROM accounts
  3. WHERE simple_query_string(['address'], 'Lane Street', default_operator='OR')

or from PPL

  1. SOURCE=accounts | WHERE simple_query_string(['address'], 'Lane Street', default_operator='OR') | fields account_number, address
account_numberaddress
1880 Holmes Lane
6671 Bristol Street
13789 Madison Street

Match phrase prefix

To search for phrases by given prefix, use MATCH_PHRASE_PREFIX function to make a prefix query out of the last term in the query string.

Syntax

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

The MATCH_PHRASE_PREFIX function lets you specify the following options in any order:

  • analyzer
  • slop
  • max_expansions
  • zero_terms_query
  • boost

Please, refer to match_phrase_prefix query documentation for parameter description and supported values.

Example of using match_phrase_prefix in SQL and PPL queries:

The REST API search request

  1. GET accounts/_search
  2. {
  3. "query": {
  4. "match_phrase_prefix": {
  5. "author": {
  6. "query": "Alexander Mil"
  7. }
  8. }
  9. }
  10. }

could be called from SQL

  1. SELECT author, title
  2. FROM books
  3. WHERE match_phrase_prefix(author, 'Alexander Mil')

or PPL

  1. source=books | where match_phrase_prefix(author, 'Alexander Mil') | fields author, title
authortitle
Alan Alexander MilneThe House at Pooh Corner
Alan Alexander MilneWinnie-the-Pooh

Match boolean prefix

Use the match_bool_prefix function to search documents that match text only for a given field prefix.

Syntax

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

The MATCH_BOOL_PREFIX function lets you specify the following options in any order:

  • minimum_should_match
  • fuzziness
  • prefix_length
  • max_expansions
  • fuzzy_transpositions
  • fuzzy_rewrite
  • boost
  • analyzer
  • operator

Please, refer to match_bool_prefix query documentation for parameter description and supported values.

Example of using match_bool_prefix in SQL and PPL queries:

The REST API search request

  1. GET accounts/_search
  2. {
  3. "query": {
  4. "match_bool_prefix": {
  5. "address": {
  6. "query": "Bristol Stre"
  7. }
  8. }
  9. }
  10. }

could be called from SQL

  1. SELECT firstname, address
  2. FROM accounts
  3. WHERE match_bool_prefix(address, 'Bristol Stre')

or PPL

  1. source=accounts | where match_bool_prefix(address, 'Bristol Stre') | fields firstname, address
firstnameaddress
Hattie671 Bristol Street
Nanette789 Madison Street