The Open Distro project is archived. Open Distro development has moved to OpenSearch. The Open Distro plugins will continue to work with legacy versions of Elasticsearch OSS, but we recommend upgrading to OpenSearch to take advantage of the latest features and improvements.

Full-text search

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

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

Match

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