QUERY
Introduced or updated: v1.2.619
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.
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. |
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.