You're viewing version 1.2 of the OpenSearch documentation. This version is no longer maintained. For the latest version, see the current documentation. For information about OpenSearch version maintenance, see Release Schedule and Maintenance Policy.

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

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:

GET my_index/_search
{
  "query": {
    "match": {
      "message": "this is a test"
    }
  }
}

SQL query:

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

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

GET my_index/_search
{
  "query": {
    "match": {
      "message": {
        "query": "this is a test",
        "operator": "and"
      }
    }
  }
}

SQL query:

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:

GET my_index/_search
{
  "query": {
    "match": {
      "message": {
        "query": "to be or not to be",
        "operator": "and",
        "zero_terms_query": "all"
      }
    }
  }
}

SQL query:

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.

SELECT account_number, address
FROM accounts
WHERE MATCH_QUERY(address, 'Holmes')

Alternate syntax:

SELECT account_number, address
FROM accounts
WHERE address = MATCH_QUERY('Holmes')

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:

SELECT firstname, lastname
FROM accounts
WHERE MULTI_MATCH('query'='Dale', 'fields'='*name')

Query string

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

SELECT account_number, address
FROM accounts
WHERE QUERY('address:Lane OR address: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.

SELECT account_number, address
FROM accounts
WHERE MATCH_PHRASE(address, '880 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).

SELECT account_number, address, _score
FROM accounts
WHERE SCORE(MATCH_QUERY(address, 'Lane'), 0.5) OR
  SCORE(MATCH_QUERY(address, 'Street'), 100)
ORDER BY _score