Full-text search in SQL with MATCH and SEARCH

MATCH and SEARCH put an Elasticsearch match query inside a SQL WHERE clause. The cluster does the analysis and the scoring work; the engine only sees documents that matched.

The three forms#

-- any of the words (operator or)
SELECT name, country
FROM organizations
WHERE MATCH(description, 'battery recycling')
LIMIT 50;

-- all of the words
SELECT name, country
FROM organizations
WHERE MATCH(description, 'battery recycling', 'and')
LIMIT 50;

-- an exact phrase
SELECT name, country
FROM organizations
WHERE MATCH(description, 'lithium ion battery', 'phrase')
LIMIT 50;

The third argument is the mode: 'or' (default), 'and' or 'phrase'. SEARCH is the multi-column form, with the Snowflake-style named argument:

SELECT "@timestamp", service, message
FROM "logs-*"
WHERE SEARCH((message, service), 'timeout upstream', SEARCH_MODE => 'AND')
  AND level = 'ERROR'
ORDER BY "@timestamp" DESC
LIMIT 100;

Columns are plain field names. Do not write .keyword; FlatQL strips it and picks the right sub-field itself.

What each form becomes#

The predicate is translated to Query DSL and placed in the filter list of a bool query along with the other pushed conjuncts.

The body sent for the logs example above:

{
  "query": {
    "bool": {
      "filter": [
        {"multi_match": {"query": "timeout upstream", "fields": ["message", "service"], "operator": "and"}},
        {"term": {"level": "ERROR"}}
      ]
    }
  },
  "_source": ["@timestamp", "message", "service"],
  "sort": [{"@timestamp": {"order": "desc", "missing": "_last"}}],
  "size": 100
}

You can see the exact body for any run in the Show as DSL tab.

Scoring and ordering#

Because the match query sits in filter, Elasticsearch does not compute a relevance score. FlatQL returns _source fields plus _id; there is no _score column and ORDER BY _score is not available. Sort by a field instead (ORDER BY "@timestamp" DESC), which is pushed when the statement is a single-table scan with the whole WHERE pushed.

When you want ranked results, use an Elasticsearch request in the same worksheet. Ask FlatQL in Elastic DSL mode writes it from the same wording.

Keyword versus text#

MATCH is for text fields: they are analysed at index time into terms, and the match query analyses the search words the same way. On a keyword field the whole value is one term, so MATCH(country, 'GBR') only matches the exact value and MATCH(country, 'GB') matches nothing. Use =, IN, LIKE and ILIKE on keyword fields.

The other direction matters too:

• description = 'solar' on a text field is sent as match_phrase, so it matches any document whose description contains the word. It is not an equality test.
• description LIKE '%solar%' on a text field is pushed only when the field has a .keyword sub-field (FlatQL then runs a wildcard query on it). Without one, the comparison runs in the engine over scanned rows. MATCH is the better tool either way.
• GROUP BY description on a text field also needs the .keyword sub-field. Without it the grouping happens in the engine.

DESCRIBE organizations shows which fields are text and which are keyword. The type mapping page lists what each type can do on the cluster.

When MATCH cannot be pushed#

A MATCH is pushed when every column in it belongs to one fetched table. In a join, qualify the column (o.description) so the engine knows which index to send it to. If the predicate cannot be attributed, for example MATCH(a.notes, 'x') OR MATCH(b.notes, 'x') across two tables, FlatQL runs it in the engine as a case-insensitive substring match on the scanned rows and adds a note to the result saying so. It is a fallback: the analyser, stemming and phrase semantics are not applied.

Mixing MATCH with other filters#

Everything that is pushable in the same WHERE joins the match query in the filter list, which keeps the fetch small:

SELECT name, founded_year, country
FROM organizations
WHERE MATCH(description, 'open source database', 'and')
  AND founded_year BETWEEN 2010 AND 2020
  AND status = 'active'
ORDER BY founded_year DESC
LIMIT 25;

Range, term, match: three filters, one request, 25 documents returned. Counting matches per country works the same way, as a composite aggregation over the filtered set:

SELECT country, COUNT(*) AS orgs
FROM organizations
WHERE MATCH(description, 'open source database', 'and')
GROUP BY country
ORDER BY orgs DESC;

For the wider set of things SQL can do here, see querying Elasticsearch with SQL. Function coverage is on the functions page.