Query syntax is a syntax to specify search condition for common Web search form. It is similar to the syntax of Google's search form. For example, word1 word2 means that groonga searches records that contain both word1 and word2. word1 OR word2 means that groogna searches records that contain either word1 or word2.
Query syntax consists of conditional expression, combind expression and assignment expression. Nomrally assignment expression can be ignored. Because assignment expression is disabled in --query option of select. You can use it if you use groonga as library and customize query syntax parser options.
Conditinal expression specifies an condition. Combinded expression consists of one or more conditional expression, combined expression or assignment expression. Assignment expression can assigns a column to a value.
Here are a schema definition and sample data to show usage.
Execution example:
日本語版はこちらtable_create Entries TABLE_PAT_KEY ShortText
# [[0, 1337566253.89858, 0.000355720520019531], true]
column_create Entries content COLUMN_SCALAR Text
# [[0, 1337566253.89858, 0.000355720520019531], true]
column_create Entries n_likes COLUMN_SCALAR UInt32
# [[0, 1337566253.89858, 0.000355720520019531], true]
table_create Terms TABLE_PAT_KEY|KEY_NORMALIZE ShortText --default_tokenizer TokenBigram
# [[0, 1337566253.89858, 0.000355720520019531], true]
column_create Terms entries_key_index COLUMN_INDEX|WITH_POSITION Entries _key
# [[0, 1337566253.89858, 0.000355720520019531], true]
column_create Terms entries_content_index COLUMN_INDEX|WITH_POSITION Entries content
# [[0, 1337566253.89858, 0.000355720520019531], true]
load --table Entries
[
{"_key": "The first post!",
"content": "Welcome! This is my first post!",
"n_likes": 5},
{"_key": "Groonga",
"content": "I started to use groonga. It's very fast!",
"n_likes": 10},
{"_key": "Mroonga",
"content": "I also started to use mroonga. It's also very fast! Really fast!",
"n_likes": 15},
{"_key": "Good-bye Senna",
"content": "I migrated all Senna system!",
"n_likes": 3},
{"_key": "Good-bye Tritonn",
"content": "I also migrated all Tritonn system!",
"n_likes": 3}
]
# [[0, 1337566253.89858, 0.000355720520019531], 5]
There is a table, Entries, for blog entries. An entry has title, content and the number of likes for the entry. Title is key of Entries. Content is value of Entries.content column. The number of likes is value of Entries.n_likes column.
Entries._key column and Entries.content column are indexed using TokenBigram tokenizer. So both Entries._key and Entries.content are fulltext search ready.
OK. The schema and data for examples are ready.
There are special characters in query syntax. To use a special character as itself, it should be escaped by prepending \. For example, " is a special character. It is escaped as \".
Here is a special character list:
- [space] (escaped as [backquote][space]) (You should substitute [space] with a white space character that is 0x20 in ASCII and [backquote] with \\.)
- " (escaped as \")
- ' (escaped as \')
- ( (escaped as \()
- ) (escaped as \))
- \ (escaped as \\)
You can use quote instead of escape. Quote syntax is "..." or '...'. You need escape " as \" in "..." quote syntax. You need escape ' as \' in '...' quote syntax. For example, Alice's brother (Bob) can be quoted "Alice's brother (Bob)" or 'Alice\'s brother (Bob)'.
Here is available conditional expression list.
Its syntax is keyword.
Full text search condition specifies a full text search condition against the default match columns. Match columns are full text search target columns.
You should specify the default match columns for full text search. They can be specified by --match_columns option of select. If you don't specify the default match columns, this conditional expression fails.
This conditional expression does full text search with keyword. keyword should not contain any spaces. If keyword contains a space such as search keyword, it means two full text search conditions; search and keyword. If you want to specifies a keyword that contains one or more spaces, you can use phrase search condition that is described below.
Here is a simple exmaple.
Execution example:
select Entries --match_columns content --query fast
# [
# [
# 0,
# 1337566253.89858,
# 0.000355720520019531
# ],
# [
# [
# [
# 2
# ],
# [
# [
# "_id",
# "UInt32"
# ],
# [
# "_key",
# "ShortText"
# ],
# [
# "content",
# "Text"
# ],
# [
# "n_likes",
# "UInt32"
# ]
# ],
# [
# 2,
# "Groonga",
# "I started to use groonga. It's very fast!",
# 10
# ],
# [
# 3,
# "Mroonga",
# "I also started to use mroonga. It's also very fast! Really fast!",
# 15
# ]
# ]
# ]
# ]
The expression matches records that contain a word fast in content column value.
content column is the default match column.
Its syntax is "search keyword".
Phrase search condition specifies a phrase search condition against the default match columns.
You should specify the default match columns for full text search. They can be specified by --match_columns option of select. If you don't specify the default match columns, this conditional expression fails.
This conditional expression does phrase search with search keyword. Phrase search searches records that contain search and keyword and those terms are appeared in the same order and adjacent. Thus, Put a search keyword in the form is matched but Search by the keyword and There is a keyword. Search by it! aren't matched.
Here is a simple exmaple.
Execution example:
select Entries --match_columns content --query '"I started"'
# [
# [
# 0,
# 1337566253.89858,
# 0.000355720520019531
# ],
# [
# [
# [
# 1
# ],
# [
# [
# "_id",
# "UInt32"
# ],
# [
# "_key",
# "ShortText"
# ],
# [
# "content",
# "Text"
# ],
# [
# "n_likes",
# "UInt32"
# ]
# ],
# [
# 2,
# "Groonga",
# "I started to use groonga. It's very fast!",
# 10
# ]
# ]
# ]
# ]
The expression matches records that contain a phrase I started in content column value. I also started isn't matched because I and started aren't adjacent.
content column is the default match column.
Its syntax is column:@keyword.
It's similar to full text search condition but it doesn't require the default match columns. You need to specify match column for the full text search condition by column: instead of --match_columns option of select.
This condtional expression is useful when you want to use two or more full text search against different columns. The default match columns specified by --match_columns option can't be specified multiple times. You need to specify the second match column by this conditional expression.
The different between full text search condition and full text search condition (with explicit match column) is whether advanced match columns are supported or not. Full text search condition supports advanced match columns but full text search condition (with explicit match column) isn't supported. Advanced match columns has the following features:
- Weight is supported.
- Using multiple columns are supported.
- Using index column as a match column is supported.
See description of --match_columns option of select about them.
Here is a simple exmaple.
Execution example:
select Entries --query content:@fast
# [
# [
# 0,
# 1337566253.89858,
# 0.000355720520019531
# ],
# [
# [
# [
# 2
# ],
# [
# [
# "_id",
# "UInt32"
# ],
# [
# "_key",
# "ShortText"
# ],
# [
# "content",
# "Text"
# ],
# [
# "n_likes",
# "UInt32"
# ]
# ],
# [
# 2,
# "Groonga",
# "I started to use groonga. It's very fast!",
# 10
# ],
# [
# 3,
# "Mroonga",
# "I also started to use mroonga. It's also very fast! Really fast!",
# 15
# ]
# ]
# ]
# ]
The expression matches records that contain a word fast in content column value.
Its syntax is column:@"search keyword".
It's similar to phrase search condition but it doesn't require the default match columns. You need to specify match column for the phrase search condition by column: instead of --match_columns option of select.
The different between phrase search condition and phrase search condition (with explicit match column) is similar to between full text search condition and full text search condition (with explicit match column). Phrase search condition supports advanced match columns but phrase search condition (with explicit match column) isn't supported. See description of full text search condition (with explicit match column) about advanced match columns.
Here is a simple exmaple.
Execution example:
select Entries --query 'content:@"I started"'
# [
# [
# 0,
# 1337566253.89858,
# 0.000355720520019531
# ],
# [
# [
# [
# 1
# ],
# [
# [
# "_id",
# "UInt32"
# ],
# [
# "_key",
# "ShortText"
# ],
# [
# "content",
# "Text"
# ],
# [
# "n_likes",
# "UInt32"
# ]
# ],
# [
# 2,
# "Groonga",
# "I started to use groonga. It's very fast!",
# 10
# ]
# ]
# ]
# ]
The expression matches records that contain a phrase I started in content column value. I also started isn't matched because I and started aren't adjacent.
Its syntax is column:^value or value*.
This conditional expression does prefix search with value. Prefix search searches records that contain a word that starts with value.
You can use fast prefix search against a column. The column must be indexed and index table must be patricia trie table (TABLE_PAT_KEY) or double array trie table (TABLE_DAT_KEY). You can also use fast prefix search against _key pseudo column of patricia trie table or double array trie table. You don't need to index _key.
Prefix search can be used with other table types but it causes all records scan. It's not problem for small records but it spends more time for large records.
It doesn't require the default match columns such as full text search condition and phrase search condition.
Here is a simple exmaple.
Execution example:
select Entries --query '_key:^Goo'
# [
# [
# 0,
# 1337566253.89858,
# 0.000355720520019531
# ],
# [
# [
# [
# 2
# ],
# [
# [
# "_id",
# "UInt32"
# ],
# [
# "_key",
# "ShortText"
# ],
# [
# "content",
# "Text"
# ],
# [
# "n_likes",
# "UInt32"
# ]
# ],
# [
# 5,
# "Good-bye Tritonn",
# "I also migrated all Tritonn system!",
# 3
# ],
# [
# 4,
# "Good-bye Senna",
# "I migrated all Senna system!",
# 3
# ]
# ]
# ]
# ]
The expression matches records that contain a word that starts with Goo in _key pseudo column value. Good-bye Senna and Good-bye Tritonn are matched with the expression.
Its syntax is column:$value.
This conditional expression does suffix search with value. Suffix search searches records that contain a word that ends with value.
You can use fast suffix search against a column. The column must be indexed and index table must be patricia trie table (TABLE_PAT_KEY) with KEY_WITH_SIS flag. You can also use fast suffix search against _key pseudo column of patricia trie table (TABLE_PAT_KEY) with KEY_WITH_SIS flag. You don't need to index _key. We recommended that you use index column based fast suffix search instead of _key based fast suffix search. _key based fast suffix search returns automatically registered substrings. (TODO: write document about suffix search and link to it from here.)
Note
Fast suffix search can be used only for non-ASCII characters such as hiragana in Japanese. You cannot use fast suffix search for ASCII character.
Suffix search can be used with other table types or patricia trie table without KEY_WITH_SIS flag but it causes all records scan. It's not problem for small records but it spends more time for large records.
It doesn't require the default match columns such as full text search condition and phrase search condition.
Here is a simple exmaple. It uses fast suffix search for hiragana in Japanese that is one of non-ASCII characters.
Execution example:
table_create Titles TABLE_NO_KEY
# [[0, 1337566253.89858, 0.000355720520019531], true]
column_create Titles content COLUMN_SCALAR ShortText
# [[0, 1337566253.89858, 0.000355720520019531], true]
table_create SuffixSearchTerms TABLE_PAT_KEY|KEY_WITH_SIS ShortText
# [[0, 1337566253.89858, 0.000355720520019531], true]
column_create SuffixSearchTerms index COLUMN_INDEX Titles content
# [[0, 1337566253.89858, 0.000355720520019531], true]
load --table Titles
[
{"content": "ぐるんが"},
{"content": "むるんが"},
{"content": "せな"},
{"content": "とりとん"}
]
# [[0, 1337566253.89858, 0.000355720520019531], 4]
select Titles --query 'content:$んが'
# [
# [
# 0,
# 1337566253.89858,
# 0.000355720520019531
# ],
# [
# [
# [
# 2
# ],
# [
# [
# "_id",
# "UInt32"
# ],
# [
# "content",
# "ShortText"
# ]
# ],
# [
# 2,
# "むるんが"
# ],
# [
# 1,
# "ぐるんが"
# ]
# ]
# ]
# ]
The expression matches records that have value that ends with んが in content column value. ぐるんが and むるんが are matched with the expression.
Its syntax is column:value.
It matches records that column value is equal to value.
It doesn't require the default match columns such as full text search condition and phrase search condition.
Here is a simple exmaple.
Execution example:
select Entries --query _key:Groonga
# [
# [
# 0,
# 1337566253.89858,
# 0.000355720520019531
# ],
# [
# [
# [
# 1
# ],
# [
# [
# "_id",
# "UInt32"
# ],
# [
# "_key",
# "ShortText"
# ],
# [
# "content",
# "Text"
# ],
# [
# "n_likes",
# "UInt32"
# ]
# ],
# [
# 2,
# "Groonga",
# "I started to use groonga. It's very fast!",
# 10
# ]
# ]
# ]
# ]
The expression matches records that _key column value is equal to Groonga.
Its syntax is column:!value.
It matches records that column value isn't equal to value.
It doesn't require the default match columns such as full text search condition and phrase search condition.
Here is a simple exmaple.
Execution example:
select Entries --query _key:!Groonga
# [
# [
# 0,
# 1337566253.89858,
# 0.000355720520019531
# ],
# [
# [
# [
# 4
# ],
# [
# [
# "_id",
# "UInt32"
# ],
# [
# "_key",
# "ShortText"
# ],
# [
# "content",
# "Text"
# ],
# [
# "n_likes",
# "UInt32"
# ]
# ],
# [
# 4,
# "Good-bye Senna",
# "I migrated all Senna system!",
# 3
# ],
# [
# 5,
# "Good-bye Tritonn",
# "I also migrated all Tritonn system!",
# 3
# ],
# [
# 3,
# "Mroonga",
# "I also started to use mroonga. It's also very fast! Really fast!",
# 15
# ],
# [
# 1,
# "The first post!",
# "Welcome! This is my first post!",
# 5
# ]
# ]
# ]
# ]
The expression matches records that _key column value is not equal to Groonga.
Its syntax is column:<value.
It matches records that column value is less than value.
If column type is numerical type such as Int32, column value and value are compared as number. If column type is text type such as ShortText, column value and value are compared as bit sequence.
It doesn't require the default match columns such as full text search condition and phrase search condition.
Here is a simple exmaple.
Execution example:
select Entries --query n_likes:<10
# [
# [
# 0,
# 1337566253.89858,
# 0.000355720520019531
# ],
# [
# [
# [
# 3
# ],
# [
# [
# "_id",
# "UInt32"
# ],
# [
# "_key",
# "ShortText"
# ],
# [
# "content",
# "Text"
# ],
# [
# "n_likes",
# "UInt32"
# ]
# ],
# [
# 4,
# "Good-bye Senna",
# "I migrated all Senna system!",
# 3
# ],
# [
# 5,
# "Good-bye Tritonn",
# "I also migrated all Tritonn system!",
# 3
# ],
# [
# 1,
# "The first post!",
# "Welcome! This is my first post!",
# 5
# ]
# ]
# ]
# ]
The expression matches records that n_likes column value is less than 10.
Its syntax is column:>value.
It matches records that column value is greater than value.
If column type is numerical type such as Int32, column value and value are compared as number. If column type is text type such as ShortText, column value and value are compared as bit sequence.
It doesn't require the default match columns such as full text search condition and phrase search condition.
Here is a simple exmaple.
Execution example:
select Entries --query n_likes:>10
# [
# [
# 0,
# 1337566253.89858,
# 0.000355720520019531
# ],
# [
# [
# [
# 1
# ],
# [
# [
# "_id",
# "UInt32"
# ],
# [
# "_key",
# "ShortText"
# ],
# [
# "content",
# "Text"
# ],
# [
# "n_likes",
# "UInt32"
# ]
# ],
# [
# 3,
# "Mroonga",
# "I also started to use mroonga. It's also very fast! Really fast!",
# 15
# ]
# ]
# ]
# ]
The expression matches records that n_likes column value is greater than 10.
Its syntax is column:<=value.
It matches records that column value is less than or equal to value.
If column type is numerical type such as Int32, column value and value are compared as number. If column type is text type such as ShortText, column value and value are compared as bit sequence.
It doesn't require the default match columns such as full text search condition and phrase search condition.
Here is a simple exmaple.
Execution example:
select Entries --query n_likes:<=10
# [
# [
# 0,
# 1337566253.89858,
# 0.000355720520019531
# ],
# [
# [
# [
# 4
# ],
# [
# [
# "_id",
# "UInt32"
# ],
# [
# "_key",
# "ShortText"
# ],
# [
# "content",
# "Text"
# ],
# [
# "n_likes",
# "UInt32"
# ]
# ],
# [
# 4,
# "Good-bye Senna",
# "I migrated all Senna system!",
# 3
# ],
# [
# 5,
# "Good-bye Tritonn",
# "I also migrated all Tritonn system!",
# 3
# ],
# [
# 2,
# "Groonga",
# "I started to use groonga. It's very fast!",
# 10
# ],
# [
# 1,
# "The first post!",
# "Welcome! This is my first post!",
# 5
# ]
# ]
# ]
# ]
The expression matches records that n_likes column value is less than or equal to 10.
Its syntax is column:>=value.
It matches records that column value is greater than or equal to value.
If column type is numerical type such as Int32, column value and value are compared as number. If column type is text type such as ShortText, column value and value are compared as bit sequence.
It doesn't require the default match columns such as full text search condition and phrase search condition.
Here is a simple exmaple.
Execution example:
select Entries --query n_likes:>=10
# [
# [
# 0,
# 1337566253.89858,
# 0.000355720520019531
# ],
# [
# [
# [
# 2
# ],
# [
# [
# "_id",
# "UInt32"
# ],
# [
# "_key",
# "ShortText"
# ],
# [
# "content",
# "Text"
# ],
# [
# "n_likes",
# "UInt32"
# ]
# ],
# [
# 2,
# "Groonga",
# "I started to use groonga. It's very fast!",
# 10
# ],
# [
# 3,
# "Mroonga",
# "I also started to use mroonga. It's also very fast! Really fast!",
# 15
# ]
# ]
# ]
# ]
The expression matches records that n_likes column value is greater than or equal to 10.
Here is available combined expression list.
Its syntax is a OR b.
a and b are conditional expressions, conbinded expressions or assignment expressions.
If at least one of a and b are matched, a OR b is matched.
Here is a simple exmaple.
Execution example:
select Entries --query 'n_likes:>10 OR content:@senna'
# [
# [
# 0,
# 1337566253.89858,
# 0.000355720520019531
# ],
# [
# [
# [
# 2
# ],
# [
# [
# "_id",
# "UInt32"
# ],
# [
# "_key",
# "ShortText"
# ],
# [
# "content",
# "Text"
# ],
# [
# "n_likes",
# "UInt32"
# ]
# ],
# [
# 3,
# "Mroonga",
# "I also started to use mroonga. It's also very fast! Really fast!",
# 15
# ],
# [
# 4,
# "Good-bye Senna",
# "I migrated all Senna system!",
# 3
# ]
# ]
# ]
# ]
The expression matches records that n_likes column value is greater than 10 or contain a word senna in content column value.
Its syntax is a + b or just a b.
a and b are conditional expressions, conbinded expressions or assignment expressions.
If both a and b are matched, a + b is matched.
You can specify + the first expression such as +a. The + is just ignored.
Here is a simple exmaple.
Execution example:
select Entries --query 'n_likes:>=10 + content:@groonga'
# [
# [
# 0,
# 1337566253.89858,
# 0.000355720520019531
# ],
# [
# [
# [
# 1
# ],
# [
# [
# "_id",
# "UInt32"
# ],
# [
# "_key",
# "ShortText"
# ],
# [
# "content",
# "Text"
# ],
# [
# "n_likes",
# "UInt32"
# ]
# ],
# [
# 2,
# "Groonga",
# "I started to use groonga. It's very fast!",
# 10
# ]
# ]
# ]
# ]
The expression matches records that n_likes column value is greater than or equal to 10 and contain a word groonga in content column value.
Its syntax is a - b.
a and b are conditional expressions, conbinded expressions or assignment expressions.
If a is matched and b is not matched, a - b is matched.
You can not specify - the first expression such as -a. It's syntax error.
Here is a simple exmaple.
Execution example:
select Entries --query 'n_likes:>=10 - content:@groonga'
# [
# [
# 0,
# 1337566253.89858,
# 0.000355720520019531
# ],
# [
# [
# [
# 1
# ],
# [
# [
# "_id",
# "UInt32"
# ],
# [
# "_key",
# "ShortText"
# ],
# [
# "content",
# "Text"
# ],
# [
# "n_likes",
# "UInt32"
# ]
# ],
# [
# 3,
# "Mroonga",
# "I also started to use mroonga. It's also very fast! Really fast!",
# 15
# ]
# ]
# ]
# ]
The expression matches records that n_likes column value is greater than or equal to 10 and don't contain a word groonga in content column value.
Its syntax is (...). ... is space separated expression list.
(...) groups one ore more expressions and they can be processed as an expression. a b OR c means that a and b are matched or c is matched. a (b OR c) means that a and one of b and c are matched.
Here is a simple exmaple.
Execution example:
select Entries --query 'n_likes:<5 content:@senna OR content:@fast'
# [
# [
# 0,
# 1337566253.89858,
# 0.000355720520019531
# ],
# [
# [
# [
# 3
# ],
# [
# [
# "_id",
# "UInt32"
# ],
# [
# "_key",
# "ShortText"
# ],
# [
# "content",
# "Text"
# ],
# [
# "n_likes",
# "UInt32"
# ]
# ],
# [
# 4,
# "Good-bye Senna",
# "I migrated all Senna system!",
# 3
# ],
# [
# 2,
# "Groonga",
# "I started to use groonga. It's very fast!",
# 10
# ],
# [
# 3,
# "Mroonga",
# "I also started to use mroonga. It's also very fast! Really fast!",
# 15
# ]
# ]
# ]
# ]
select Entries --query 'n_likes:<5 (content:@senna OR content:@fast)'
# [
# [
# 0,
# 1337566253.89858,
# 0.000355720520019531
# ],
# [
# [
# [
# 1
# ],
# [
# [
# "_id",
# "UInt32"
# ],
# [
# "_key",
# "ShortText"
# ],
# [
# "content",
# "Text"
# ],
# [
# "n_likes",
# "UInt32"
# ]
# ],
# [
# 4,
# "Good-bye Senna",
# "I migrated all Senna system!",
# 3
# ]
# ]
# ]
# ]
The first expression doesn't use grouping. It matches records that n_likes:<5 and content:@senna are matched or content:@fast is matched.
The second expression uses grouping. It matches records that n_likes:<5 and one of content:@senna or content:@fast are matched.
This section is for advanced users. Because assignment expression is disabled in --query option of select by default. You need to specify ALLOW_COLUMN|ALLOW_UPDATE as --query_flags option value to enable assignment expression.
Assignment expression in query syntax has some limitations. So you should use Script syntax instead of query syntax for assignment.
There is only one syntax for assignment expression. It's column:=value.
value is assigend to column. value is always processed as string in query syntax. value is casted to the type of column automatically. It causes some limitations. For example, you cannot use boolean literal such as true and false for Bool type column. You need to use empty string for false but query syntax doesn't support column:= syntax.
See Cast about cast.