This section provides reference information for search functions in PlaidCloud Lakehouse.
1 - MATCH
Searches for documents containing specified keywords. Please note that the MATCH function can only be used in a WHERE clause.
:::info Databend's MATCH function is inspired by Elasticsearch's MATCH. :::
SQL Syntax
MATCH( '<columns>', '<keywords>'[, '<options>'] )
| Parameter | Description |
|---|---|
<columns> | A comma-separated list of column names in the table to search for the specified keywords, with optional weighting using the syntax (^), which allows assigning different weights to each column, influencing the importance of each column in the search. |
<keywords> | The keywords to match against the specified columns in the table. This parameter can also be used for suffix matching, where the search term followed by an asterisk (*) can match any number of characters or words. |
<options> | A set of configuration options, separated by semicolons ;, that customize the search behavior. See the table below for details. |
| Option | Description | Example | Explanation |
|---|---|---|---|
| fuzziness | Allows matching terms within a specified Levenshtein distance. fuzziness can be set to 1 or 2. | SELECT id, score(), content FROM t WHERE match(content, 'box', 'fuzziness=1'); | When matching the query term "box", fuzziness=1 allows matching terms like "fox", since "box" and "fox" have a Levenshtein distance of 1. |
| operator | Specifies how multiple query terms are combined. Can be set to OR (default) or AND. OR returns results containing any of the query terms, while AND returns results containing all query terms. | SELECT id, score(), content FROM t WHERE match(content, 'action works', 'fuzziness=1;operator=AND'); | With operator=AND, the query requires both "action" and "works" to be present in the results. Due to fuzziness=1, it matches terms like "Actions" and "words", so "Actions speak louder than words" is returned. |
| lenient | Controls whether errors are reported when the query text is invalid. Defaults to false. If set to true, no error is reported, and an empty result set is returned if the query text is invalid. | SELECT id, score(), content FROM t WHERE match(content, '()', 'lenient=true'); | If the query text () is invalid, setting lenient=true prevents an error from being thrown and returns an empty result set instead. |
SQL Examples
CREATE TABLE test(title STRING, body STRING);
CREATE INVERTED INDEX idx ON test(title, body);
INSERT INTO test VALUES
('The Importance of Reading', 'Reading is a crucial skill that opens up a world of knowledge and imagination.'),
('The Benefits of Exercise', 'Exercise is essential for maintaining a healthy lifestyle.'),
('The Power of Perseverance', 'Perseverance is the key to overcoming obstacles and achieving success.'),
('The Art of Communication', 'Effective communication is crucial in everyday life.'),
('The Impact of Technology on Society', 'Technology has revolutionized our society in countless ways.');
-- Retrieve documents where the 'title' column matches 'art power'
SELECT * FROM test WHERE MATCH('title', 'art power');
┌────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ title │ body │
├───────────────────────────┼────────────────────────────────────────────────────────────────────────┤
│ The Power of Perseverance │ Perseverance is the key to overcoming obstacles and achieving success. │
│ The Art of Communication │ Effective communication is crucial in everyday life. │
└────────────────────────────────────────────────────────────────────────────────────────────────────┘
-- Retrieve documents where the 'title' column contains values that start with 'The' followed by any characters
SELECT * FROM test WHERE MATCH('title', 'The*')
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ title │ body │
│ Nullable(String) │ Nullable(String) │
├─────────────────────────────────────┼────────────────────────────────────────────────────────────────────────────────┤
│ The Importance of Reading │ Reading is a crucial skill that opens up a world of knowledge and imagination. │
│ The Benefits of Exercise │ Exercise is essential for maintaining a healthy lifestyle. │
│ The Power of Perseverance │ Perseverance is the key to overcoming obstacles and achieving success. │
│ The Art of Communication │ Effective communication is crucial in everyday life. │
│ The Impact of Technology on Society │ Technology has revolutionized our society in countless ways. │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
-- Retrieve documents where either the 'title' or 'body' column matches 'knowledge technology'
SELECT *, score() FROM test WHERE MATCH('title, body', 'knowledge technology');
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ title │ body │ score() │
├─────────────────────────────────────┼────────────────────────────────────────────────────────────────────────────────┼───────────┤
│ The Importance of Reading │ Reading is a crucial skill that opens up a world of knowledge and imagination. │ 1.1550591 │
│ The Impact of Technology on Society │ Technology has revolutionized our society in countless ways. │ 2.6830134 │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
-- Retrieve documents where either the 'title' or 'body' column matches 'knowledge technology', with weighted importance on both columns
SELECT *, score() FROM test WHERE MATCH('title^5, body^1.2', 'knowledge technology');
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ title │ body │ score() │
├─────────────────────────────────────┼────────────────────────────────────────────────────────────────────────────────┼───────────┤
│ The Importance of Reading │ Reading is a crucial skill that opens up a world of knowledge and imagination. │ 1.3860708 │
│ The Impact of Technology on Society │ Technology has revolutionized our society in countless ways. │ 7.8053584 │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
-- Retrieve documents where the 'body' column contains both "knowledge" and "imagination" (allowing for minor typos).
SELECT * FROM test WHERE MATCH('body', 'knowledg imaginatio', 'fuzziness = 1; operator = AND');
-[ RECORD 1 ]-----------------------------------
title: The Importance of Reading
body: Reading is a crucial skill that opens up a world of knowledge and imagination.
2 - QUERY
Searches for documents satisfying a specified query expression. Please note that the QUERY function can only be used in a WHERE clause.
:::info Databend's QUERY function is inspired by Elasticsearch's QUERY. :::
SQL Syntax
QUERY( '<query_expr>'[, '<options>'] )
The query expression supports the following syntaxes. Please note that <keyword> can also be used for suffix matching, where the search term followed by an asterisk (*) can match any number of characters or words.
| Syntax | Description | Examples |
|---|---|---|
<column>:<keyword> | Matches documents where the specified column contains the specified keyword. | QUERY('title:power') |
<column>:IN [<keyword1>, <keyword2>...] | Matches documents where the specified column contains any of the keywords listed within the square brackets. | QUERY('title:IN [power, art]') |
<column>:<keyword> AND / OR <keyword> | Matches documents where the specified column contains both or either of the specified keywords. In queries with both AND and OR, AND operations are prioritized over OR, meaning that 'a AND b OR c' is read as '(a AND b) OR c'. | QUERY('title:power AND art') |
<column>:+<keyword> -<keyword> | Matches documents where the specified positive keyword exists in the specified column and excludes documents where the specified negative keyword exists. | QUERY('title:+the -reading') |
<column>:"<phrase>" | Matches documents where the specified column contains the exact specified phrase. | QUERY('title:"Benefits of Exercise"') |
<column>:<keyword>^<boost> <column>:<keyword>^<boost> | Matches documents where the specified keyword exists in the specified columns with the specified boosts to increase their relevance in the search. This syntax allows setting different weights for multiple columns to influence the search relevance. | QUERY('title:art^5 body:reading^1.2') |
| Option | Description | Example | Explanation |
|---|---|---|---|
| fuzziness | Allows matching terms within a specified Levenshtein distance. fuzziness can be set to 1 or 2. | SELECT id, score(), content FROM t WHERE query('content:box', 'fuzziness=1'); | When matching the query term "box", fuzziness=1 allows matching terms like "fox", since "box" and "fox" have a Levenshtein distance of 1. |
| operator | Specifies how multiple query terms are combined. Can be set to OR (default) or AND. OR returns results containing any of the query terms, while AND returns results containing all query terms. | SELECT id, score(), content FROM t WHERE query('content:action works', 'fuzziness=1;operator=AND'); | With operator=AND, the query requires both "action" and "works" to be present in the results. Due to fuzziness=1, it matches terms like "Actions" and "words", so "Actions speak louder than words" is returned. |
| lenient | Controls whether errors are reported when the query text is invalid. Defaults to false. If set to true, no error is reported, and an empty result set is returned if the query text is invalid. | SELECT id, score(), content FROM t WHERE query('content:()', 'lenient=true'); | If the query text () is invalid, setting lenient=true prevents an error from being thrown and returns an empty result set instead. |
SQL Examples
CREATE TABLE test(title STRING, body STRING);
CREATE INVERTED INDEX idx ON test(title, body);
INSERT INTO test VALUES
('The Importance of Reading', 'Reading is a crucial skill that opens up a world of knowledge and imagination.'),
('The Benefits of Exercise', 'Exercise is essential for maintaining a healthy lifestyle.'),
('The Power of Perseverance', 'Perseverance is the key to overcoming obstacles and achieving success.'),
('The Art of Communication', 'Effective communication is crucial in everyday life.'),
('The Impact of Technology on Society', 'Technology has revolutionized our society in countless ways.');
-- Retrieve documents where the 'title' column contains the keyword 'power'
SELECT * FROM test WHERE QUERY('title:power');
┌────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ title │ body │
├───────────────────────────┼────────────────────────────────────────────────────────────────────────┤
│ The Power of Perseverance │ Perseverance is the key to overcoming obstacles and achieving success. │
└────────────────────────────────────────────────────────────────────────────────────────────────────┘
-- Retrieve documents where the 'title' column contains values that start with 'The' followed by any characters
SELECT * FROM test WHERE QUERY('title:The*');
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ title │ body │
├─────────────────────────────────────┼────────────────────────────────────────────────────────────────────────────────┤
│ The Importance of Reading │ Reading is a crucial skill that opens up a world of knowledge and imagination. │
│ The Benefits of Exercise │ Exercise is essential for maintaining a healthy lifestyle. │
│ The Power of Perseverance │ Perseverance is the key to overcoming obstacles and achieving success. │
│ The Art of Communication │ Effective communication is crucial in everyday life. │
│ The Impact of Technology on Society │ Technology has revolutionized our society in countless ways. │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
-- Retrieve documents where the 'title' column contains either the keyword 'power' or 'art'
SELECT * FROM test WHERE QUERY('title:power OR art');
┌────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ title │ body │
├───────────────────────────┼────────────────────────────────────────────────────────────────────────┤
│ The Power of Perseverance │ Perseverance is the key to overcoming obstacles and achieving success. │
│ The Art of Communication │ Effective communication is crucial in everyday life. │
└────────────────────────────────────────────────────────────────────────────────────────────────────┘
SELECT * FROM test WHERE QUERY('title:IN [power, art]')
┌────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ title │ body │
│ Nullable(String) │ Nullable(String) │
├───────────────────────────┼────────────────────────────────────────────────────────────────────────┤
│ The Power of Perseverance │ Perseverance is the key to overcoming obstacles and achieving success. │
│ The Art of Communication │ Effective communication is crucial in everyday life. │
└────────────────────────────────────────────────────────────────────────────────────────────────────┘
-- Retrieve documents where the 'title' column contains the positive keyword 'the' but not 'reading'
SELECT * FROM test WHERE QUERY('title:+the -reading');
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ title │ body │
├─────────────────────────────────────┼────────────────────────────────────────────────────────────────────────┤
│ The Benefits of Exercise │ Exercise is essential for maintaining a healthy lifestyle. │
│ The Power of Perseverance │ Perseverance is the key to overcoming obstacles and achieving success. │
│ The Art of Communication │ Effective communication is crucial in everyday life. │
│ The Impact of Technology on Society │ Technology has revolutionized our society in countless ways. │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
-- Retrieve documents where the 'title' column contains the exact phrase 'Benefits of Exercise'
SELECT * FROM test WHERE QUERY('title:"Benefits of Exercise"');
┌───────────────────────────────────────────────────────────────────────────────────────┐
│ title │ body │
├──────────────────────────┼────────────────────────────────────────────────────────────┤
│ The Benefits of Exercise │ Exercise is essential for maintaining a healthy lifestyle. │
└───────────────────────────────────────────────────────────────────────────────────────┘
-- Retrieve documents where the 'title' column contains the keyword 'art' with a boost of 5 and the 'body' column contains the keyword 'reading' with a boost of 1.2
SELECT *, score() FROM test WHERE QUERY('title:art^5 body:reading^1.2');
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ title │ body │ score() │
├───────────────────────────┼────────────────────────────────────────────────────────────────────────────────┼───────────┤
│ The Importance of Reading │ Reading is a crucial skill that opens up a world of knowledge and imagination. │ 1.3860708 │
│ The Art of Communication │ Effective communication is crucial in everyday life. │ 7.1992116 │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
-- Retrieve documents where the 'body' column contains both "knowledge" and "imagination" (allowing for minor typos).
SELECT * FROM test WHERE QUERY('body:knowledg OR imaginatio', 'fuzziness = 1; operator = AND');
-[ RECORD 1 ]-----------------------------------
title: The Importance of Reading
body: Reading is a crucial skill that opens up a world of knowledge and imagination.
3 - SCORE
Returns the relevance of the query string. The higher the score, the more relevant the data. Please note that SCORE function can only be used with the QUERY or MATCH function.
:::info Databend's SCORE function is inspired by Elasticsearch's SCORE. :::
SQL Syntax
SCORE()
SQL Examples
CREATE TABLE test(title STRING, body STRING);
CREATE INVERTED INDEX idx ON test(title, body);
INSERT INTO test VALUES
('The Importance of Reading', 'Reading is a crucial skill that opens up a world of knowledge and imagination.'),
('The Benefits of Exercise', 'Exercise is essential for maintaining a healthy lifestyle.'),
('The Power of Perseverance', 'Perseverance is the key to overcoming obstacles and achieving success.'),
('The Art of Communication', 'Effective communication is crucial in everyday life.'),
('The Impact of Technology on Society', 'Technology has revolutionized our society in countless ways.');
-- Retrieve documents where the 'title' column contains the keyword 'art' with a boost of 5 and the 'body' column contains the keyword 'reading' with a boost of 1.2, along with their relevance scores
SELECT *, score() FROM test WHERE QUERY('title:art^5 body:reading^1.2');
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ title │ body │ score() │
├───────────────────────────┼────────────────────────────────────────────────────────────────────────────────┼───────────┤
│ The Importance of Reading │ Reading is a crucial skill that opens up a world of knowledge and imagination. │ 1.3860708 │
│ The Art of Communication │ Effective communication is crucial in everyday life. │ 7.1992116 │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
-- Retrieve documents where the 'title' column contains the keyword 'reading' with a boost of 5 and the 'body' column contains the keyword 'everyday' with a boost of 1.2, along with their relevance scores
SELECT *, score() FROM test WHERE MATCH('title^5, body^1.2', 'reading everyday');
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ title │ body │ score() │
├───────────────────────────┼────────────────────────────────────────────────────────────────────────────────┼───────────┤
│ The Importance of Reading │ Reading is a crucial skill that opens up a world of knowledge and imagination. │ 8.585282 │
│ The Art of Communication │ Effective communication is crucial in everyday life. │ 1.8575745 │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘