Full-text search

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

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

Match

Use the match command 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:

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

Example 1: Search the message field:

  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")

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)

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)

To search for text in a single field, use MATCHQUERY or MATCH_QUERY functions.

Pass in your search query and the field name that you want to search against.

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

Alternate syntax:

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

Multi match

To search for text in multiple fields, use MULTI_MATCH, MULTIMATCH, or MULTIMATCHQUERY functions.

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

  1. SELECT firstname, lastname
  2. FROM accounts
  3. WHERE MULTI_MATCH('query'='Dale', 'fields'='*name')
firstnamelastname
DaleAdams

Query string

To split text based on operators, use the QUERY function.

  1. SELECT account_number, address
  2. FROM accounts
  3. WHERE QUERY('address:Lane OR address:Street')
account_numberaddress
1880 Holmes Lane
6671 Bristol Street
13789 Madison Street

The QUERY function supports logical connectives, wildcard, regex, and proximity search.

Match phrase

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

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

Score query

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

You need to pass in two arguments. The first is the MATCH_QUERY expression. The second is an optional floating point number to boost the score (default value is 1.0).

  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
account_numberaddressscore
1880 Holmes Lane0.5
6671 Bristol Street100
13789 Madison Street100