SenseiDB - BQL

Browse Query Language (BQL)

The query lanaguage for Sensei

What is BQL?

BQL stands for Browsing Query Language. It is an SQL-like language that provides Sensei users easy access to the database if they have experience using traditional relational databases.

BQL was originally proposed as a query language for Bobo. Now it is being developed and bundled with Sensei, and used by both Sensei and Sin.

BNF Grammar for BQL

BNF Notation

We use a modified BNF notation to describe BQL. The following table lists the meaning of all the meta-symbols we use.

Symbol	Meaning
\|	Or. Choose one of the items.
[ ]	Enclose optional items.
*	Flags items that can be repeated 0 or more times.
( )	Group items so that they can be marked with one of the symbols: [ ], \|, or *.

BQL Grammar

Below is an overview of the BQL grammar in BNF. Detailed documentation of statements and clauses can be found in later sections.

<statement> ::= ( <select_stmt> | <describe_stmt> ) [';']

<select_stmt> ::= SELECT <select_list> [<from_clause>] [<where_clause>] [<given_clause>]
                  [<additional_clauses>]

<describe_stmt> ::= ( DESC | DESCRIBE ) [<index_name>]

<select_list> ::= '*' | <column_name_list>

<column_name_list> ::= <column_name> ( ',' <column_name> )*

<from_clause> ::= FROM <index_name>

<index_name> ::= <identifier> | <quoted_string>

<where_clause> ::= WHERE <search_expr>

<search_expr> ::= <term_expr> ( OR <term_expr> )*

<term_expr> ::= <facet_expr> ( AND <facet_expr> )*

<facet_expr> ::= <predicate> 
               | '(' <search_expr> ')'

<predicates> ::= <predicate> ( AND <predicate> )*

<predicate> ::= <in_predicate>
              | <contains_all_predicate>
              | <equal_predicate>
              | <not_equal_predicate>
              | <query_predicate>
              | <between_predicate>
              | <range_predicate>
              | <time_predicate>
              | <match_predicate>
              | <like_predicate>
              | <null_predicate>

<in_predicate> ::= <column_name> [NOT] IN <value_list> [<except_clause>] [<predicate_props>]

<contains_all_predicate> ::= <column_name> CONTAINS ALL <value_list> [<except_clause>]
                             [<predicate_props>]

<equal_predicate> ::= <column_name> '=' <value> [<predicate_props>]

<not_equal_predicate> ::= <column_name> '<>' <value> [<predicate_props>]

<query_predicate> ::= QUERY IS <quoted_string>

<between_predicate> ::= <column_name> [NOT] BETWEEN <value> AND <value>

<range_predicate> ::= <column_name> <range_op> <numeric>

<time_predicate> ::= <column_name> IN LAST <time_span>
                   | <column_name> ( SINCE | AFTER | BEFORE ) <time_expr>

<match_predicate> ::= [NOT] MATCH '(' <column_name_list> ')' AGAINST '(' <quoted_string> ')'

<like_predicate> ::= <column_name> [NOT] LIKE <quoted_string>

<null_predicate> ::= <column_name> IS [NOT] NULL

<value_list> ::= <non_variable_value_list> | <variable>

<non_variable_value_list> ::= '(' <value> ( ',' <value> )* ')'

<python_style_list> ::= '[' <python_style_value> ( ',' <python_style_value> )* ']'

<python_style_dict> ::= '{' <key_value_pair> ( ',' <key_value_pair> )* '}'

<python_style_value> ::= <value>
                       | <python_style_list>
                       | <python_style_dict>

<value> ::= <numeric>
          | <quoted_string>
          | TRUE
          | FALSE
          | <variable>

<range_op> ::= '<' | '<=' | '>=' | '>'

<except_clause> ::= EXCEPT <value_list>

<predicate_props> ::= WITH <prop_list>

<prop_list> ::= '(' <key_value_pair> ( ',' <key_value_pair> )* ')'

<key_value_pair> ::= <quoted_string> ':' 
                     ( <value> | <python_style_list> | <python_style_dict> )

<given_clause> ::= GIVEN FACET PARAM <facet_param_list>

<facet_param_list> ::= <facet_param> ( ',' <facet_param> )*

<facet_param> ::= '(' <facet_name> <facet_param_name> <facet_param_type> <facet_param_value> ')'

<facet_param_name> ::= <quoted_string>

<facet_param_type> ::= BOOLEAN | INT | LONG | STRING | BYTEARRAY | DOUBLE

<facet_param_value> ::= <quoted_string>

<additional_clauses> ::= ( <additional_clause> )+

<additional_clause> ::= <order_by_clause>
                      | <limit_clause>
                      | <group_by_clause>
                      | <browse_by_clause>
                      | <fetching_stored_clause>
                      | <route_by_clause>
                      | <relevance_model_clause>

<order_by_clause> ::= ORDER BY <sort_specs>

<sort_specs> ::= <sort_spec> ( ',' <sort_spec> )*

<sort_spec> ::= <column_name> [<ordering_spec>]

<ordering_spec> ::= ASC | DESC

<group_by_clause> ::= GROUP BY <group_spec>

<group_spec> ::= <or_column_name_list> [TOP <max_per_group>]

<or_column_name_list> ::= <column_name> ( OR <column_name> )*

<limit_clause> ::= LIMIT [<offset> ','] <count>

<offset> ::= ( <digit> )+

<count> ::= ( <digit> )+

<browse_by_clause> ::= BROWSE BY <facet_specs>

<facet_specs> ::= <facet_spec> ( ',' <facet_spec> )*

<facet_spec> ::= <facet_name> [<facet_expression>]

<facet_expression> ::= '(' <expand_flag> <count> <count> <facet_ordering> ')'

<expand_flag> ::= TRUE | FALSE

<facet_ordering> ::= HITS | VALUE

<fetching_stored_clause> ::= FETCHING STORED [<fetching_flag>]

<fetching_flag> ::= TRUE | FALSE

<route_by_clause> ::= ROUTE BY <quoted_string>

<relevance_model_clause> ::= USING RELEVANCE MODEL <identifier> <prop_list>
                             [<relevance_model>]

<relevance_model> ::= DEFINED AS <formal_parameters> BEGIN <model_block> END

<formal_parameters> ::= '(' <formal_parameter_decls> ')'

<formal_parameter_decls> ::= <formal_parameter_decl> ( ',' <formal_parameter_decl> )*

<formal_parameter_decl> ::= <variable_modifiers> <type> <variable_declarator_id>

<variable_modifiers> ::= ( <variable_modifier> )*

<variable_modifier> ::= 'final'

<type> ::= <class_or_interface_type> ('[' ']')*
         | <primitive_type> ('[' ']')*
         | <boxed_type> ('[' ']')*
         | <limited_type> ('[' ']')*

<class_or_interface_type> ::= <fast_util_data_type>

<fast_util_data_type> ::= 'IntOpenHashSet'
                        | 'FloatOpenHashSet'
                        | 'DoubleOpenHashSet'
                        | 'LongOpenHashSet'
                        | 'ObjectOpenHashSet'
                        | 'Int2IntOpenHashMap'
                        | 'Int2FloatOpenHashMap'
                        | 'Int2DoubleOpenHashMap'
                        | 'Int2LongOpenHashMap'
                        | 'Int2ObjectOpenHashMap'
                        | 'Object2IntOpenHashMap'
                        | 'Object2FloatOpenHashMap'
                        | 'Object2DoubleOpenHashMap'
                        | 'Object2LongOpenHashMap'
                        | 'Object2ObjectOpenHashMap'

<primitive_type> ::= 'boolean' | 'char' | 'byte' | 'short' 
                   | 'int' | 'long' | 'float' | 'double'

<boxed_type> ::= 'Boolean' | 'Character' | 'Byte' | 'Short' 
               | 'Integer' | 'Long' | 'Float' | 'Double'

<limited_type> ::= 'String' | 'System' | 'Math'

<model_block> ::= ( <block_statement> )+

<block_statement> ::= <local_variable_declaration_stmt>
                    | <java_statement>

<local_variable_declaration_stmt> ::= <local_variable_declaration> ';'

<local_variable_declaration> ::= <variable_modifiers> <type> <variable_declarators>

<java_statement> ::= <block>
                   | 'if' <par_expression> <java_statement> [ <else_statement> ]
                   | 'for' '(' <for_control> ')' <java_statement>
                   | 'while' <par_expression> <java_statement>
                   | 'do' <java_statement> 'while> <par_expression> ';'
                   | 'switch' <par_expression> '{' <switch_block_statement_groups> '}'
                   | 'return' <expression> ';'
                   | 'break' [<identifier>] ';'
                   | 'continue' [<identifier>] ';'
                   | ';'
                   | <statement_expression> ';'

<block> ::= '{' ( <block_statement> )* '}'

<else_statement> ::= 'else' <java_statement>

<switch_block_statement_groups> ::= ( <switch_block_statement_group> )*

<switch_block_statement_group> ::= ( <switch_label> )+ ( <block_statement> )*

<switch_label> ::= 'case' <constant_expression> ':'
                 | 'case' <enum_constant_name> ':'
                 | 'default' ':'

<for_control> ::= <enhanced_for_control>
                | [<for_init>] ';' [<expression>] ';' [<for_update>]

<for_init> ::= <local_variable_declaration>
             | <expression_list>

<enhanced_for_control> ::= <variable_modifiers> <type> <identifier> ':' <expression>

<for_update> ::= <expression_list>

<par_expression> ::= '(' <expression> ')'

<expression_list> ::= <expression> ( ',' <expression> )*

<statement_expression> ::= <expression>

<constant_expression> ::= <expression>

<enum_constant_name> ::= <identifier>

<variable_declarators> ::= <variable_declarator> ( ',' <variable_declarator> )*

<variable_declarator> ::= <variable_declarator_id> '=' <variable_initializer>

<variable_declarator_id> ::= <identifier> ('[' ']')*

<variable_initializer> ::= <array_initializer>
                         | <expression>

<array_initializer> ::= '{' [ <variable_initializer> ( ',' <variable_initializer> )* [','] ] '}'

<expression> ::= <conditional_expression> [ <assignment_operator> <expression> ]

<assignment_operator> ::= '=' | '+=' | '-=' | '*=' | '/=' | '&=' | '|=' | '^=' |
                        | '%=' | '<<=' | '>>>=' | '>>='

<conditional_expression> ::= <conditional_or_expression> [ '?' <expression> ':' <expression> ]

<conditional_or_expression> ::= <conditional_and_expression> ( '||' <conditional_and_expression> )*

<conditional_and_expression> ::= <inclusive_or_expression> ('&&' <inclusive_or_expression> )*

<inclusive_or_expression> ::= <exclusive_or_expression> ('|' <exclusive_or_expression> )*

<exclusive_or_expression> ::= <and_expression> ('^' <and_expression> )*

<and_expression> ::= <equality_expression> ( '&' <equality_expression> )*

<equality_expression> ::= <instanceof_expression> ( ('==' | '!=') <instanceof_expression> )*

<instanceof_expression> ::= <relational_expression> [ 'instanceof' <type> ]

<relational_expression> ::= <shift_expression> ( <relational_op> <shift_expression> )*

<shift_expression> ::= <additive_expression> ( <shift_op> <additive_expression> )*

<relational_op> ::= '<=' | '>=' | '<' | '>'

<shift_op> ::= '<<' | '>>>' | '>>'

<additive_expression> ::= <multiplicative_expression> ( ('+' | '-') <multiplicative_expression> )*

<multiplicative_expression> ::= <unary_expression> ( ( '*' | '/' | '%' ) <unary_expression> )*

<unary_expression> ::= '+' <unary_expression>
                     | '-' <unary_expression>
                     | '++' <unary_expression>
                     | '--' <unary_expression>
                     | <unary_expression_not_plus_minus>

<unary_expression_not_plus_minus> ::= '~' <unary_expression>
                                    | '!' <unary_expression>
                                    | <cast_expression>
                                    | <primary> <selector>* [ ('++'|'--') ]

<cast_expression> ::= '(' <primitive_type> ')' <unary_expression>
                    | '(' (<type> | <expression>) ')' <unary_expression_not_plus_minus>

<primary> ::= <par_expression>
            | <literal>
            | <java_ident> ('.' <java_method>)* [<identifier_suffix>]

<java_ident> ::= <boxed_type>
               | <limited_type>
               | <identifier>

<java_method> ::= <identifier>

<identifier_suffix> ::= ('[' ']')+ '.' 'class'
                      | <arguments>
                      | '.' 'class'
                      | '.' 'this'
                      | '.' 'super' <arguments>

<literal> ::= <integer>
            | <real>
            | <floating_point_literal>
            | <character_literal>
            | <quoted_string>
            | <boolean_literal>
            | 'null'

<boolean_literal> ::= 'true' | 'false'

<selector> ::= '.' <identifier> <arguments>
             | '.' 'this'
             | '[' <expression> ']'

<arguments> ::= '(' [<expression_list>] ')'

<quoted_string> ::= '"' ( <char> )* '"'
                  | "'" ( <char> )* "'"

<identifier> ::= <identifier_start> ( <identifier_part> )*

<identifier_start> ::= <alpha> | '-' | '_'

<identifier_part> ::= <identifier_start> | <digit>

<variable> ::= '$' ( <alpha> | <digit> | '_' )+

<column_name> ::= <identifier> | <quoted_string>

<facet_name> ::= <identifier>

<alpha> ::= <alpha_lower_case> | <alpha_upper_case>

<alpha_upper_case> ::= A | B | C | D | E | F | G | H | I | J | K | L | M | N | O
                     | P | Q | R | S | T | U | V | W | X | Y | Z

<alpha_lower_case> ::= a | b | c | d | e | f | g | h | i | j | k | l | m | n | o
                     | p | q | r | s | t | u | v | w | x | y | z

<digit> ::= 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9

<numeric> ::= <time_expr> 
            | <integer>
            | <real>

<integer> ::= ( <digit> )+

<real> ::= ( <digit> )+ '.' ( <digit> )+

<time_expr> ::= <time_span> AGO
              | <date_time_string>
              | NOW

<time_span> ::= [<time_week_part>] [<time_day_part>] [<time_hour_part>]
                [<time_minute_part>] [<time_second_part>] [<time_millisecond_part>]

<time_week_part> ::= <integer> ( 'week' | 'weeks' )

<time_day_part>  ::= <integer> ( 'day'  | 'days' )

<time_hour_part> ::= <integer> ( 'hour' | 'hours' )

<time_minute_part> ::= <integer> ( 'minute' | 'minutes' | 'min' | 'mins')

<time_second_part> ::= <integer> ( 'second' | 'seconds' | 'sec' | 'secs')

<time_millisecond_part> ::= <integer> ( 'millisecond' | 'milliseconds' | 'msec' | 'msecs')

<date_time_string> ::= <date> [<time>]

<date> ::= <digit><digit><digit><digit> ('-' | '/' | '.') <digit><digit>
           ('-' | '/' | '.') <digit><digit>

<time> ::= DIGIT DIGIT ':' DIGIT DIGIT ':' DIGIT DIGIT

BQL Statement Syntax

DESCRIBE Statement

DESCRIBE statement provides the information about the index (or table in RDBM terminology). In Sensei, the index name is optional because when a Sensei client sends a query to a Sensei REST endpoint, only one index is accessible. The index name in a DESCRIBE statement is simply ignored.

The keyword "DESCRIBE" can also be written as "DESC".

Example Statement:

DESC cars;

Results:

+------------+------------+---------+-----------+-------------+---------+
| facet_name | facet_type | runtime | column    | column_type | depends |
+------------+------------+---------+-----------+-------------+---------+
| category   | simple     | false   | category  | string      | []      |
| groupid    | simple     | false   | groupid   | long        | []      |
| city       | path       | false   | city      | string      | []      |
| color      | simple     | false   | color     | string      | []      |
| tags       | multi      | false   | tags      | string      | []      |
| price      | range      | false   | price     | float       | []      |
| makemodel  | path       | false   | makemodel | string      | []      |
| mileage    | range      | false   | mileage   | int         | []      |
| year       | range      | false   | year      | int         | []      |
+------------+------------+---------+-----------+-------------+---------+

SELECT Statement

SELECT is the only Data Manipulation Language (DML) that Sensei supports today. It is used to retrieve a set of documents based on conditions specified in the WHERE clause.

The SELECT statement takes the following form:

SELECT <select_list>
[ FROM <index> ]
[ WHERE <search_expr> ]
[ GIVEN FACET PARAM <facet_param_list> ]
( <order_by_clause>
  | <group_by_clause>
  | <limit_clause>
  | <browse_by_clause>
  | <fetching_stored_clause>
)*

Most of the syntax is similar to SQL. So if you are familiar with SQL, you should be ready to write some basic BQL queries right away. In addition to the basic SQL syntax, BQL includes several new features to support faceted search and syntactic sugar to make real-time search queries easier.

We'll describe different pieces of the SELECT statement in more details in the following sections.

Select List

The select list in a SELECT statement can simply be a "*" or a comma-separated column names. If "*" is given, it means all columns in the index are selected.

A column in the select list is either a facet name or a field name in the index. Column names are case-sensitive in BQL because they have to match what are defined in the index schema. If a column is unknown to Sensei server, then it is discarded on the server side.

Sensei reserves several internal column names, which are all named with a leading "_", e.g. "_uid". Try to avoid naming any column with a leading "_".

FROM Clause

Just like the index name in a DESCRIBE statement is optional, the FROM clause in a SELECT statement is also optional.

WHERE Clause

The WHERE clause, if given, specifies the search conditions that all documents must satisfy to be selected. Conditions are expressed as a Boolean expression consisting of one or more predicates. The following predicates are supported in BQL.

IN predicate
CONTAINS ALL predicate
Equal predicate
Not equal predicate
Query predicate
Range predicate
BETWEEN predicate
Time predicate
MATCH AGAINST predicate
LIKE predicate

Predicate

IN Predicate

This predicate specifies that the value(s) of a column has to have, or not have, in the selected documents. For example:

color IN ("red", "blue")

Each document in the search results much have color "red" or "blue".
tags IN ("cool", "leather") EXCEPT ("moon-roof", "automatic")

Each document in the search results should have a tag of "cool" or "leather", but cannot have a tag of "moon-roof" or "automatic".
age IN (10, 20, 30)

Each document in the search results must have a age of 10, 20, or 30.

CONTAINS ALL Predicate

This predicate specifies that a multi-value column has to contain all the given values in the selected documents. For example:

tags CONTAINS ALL ("cool", "hybrid") EXCEPT("favorite")

Each document in the search results must have two tags: "cool" and "hybrid". However, it cannot have the tag of "favorite".

Equal Predicate

This predicate specifies that a column has to have the given value in the selected documents. For example:

color = "red"

Each document in the search results must have a color of "red".

Not Equal Predicate

This predicate specifies the value that a column cannot have in the selected documents. For example:

color <> "red"

Query Predicate

This predicate is used to do full text search on the internal contents column. All Lucene query syntax is supported. For example:

QUERY IS "hybrid AND (cool or moon-roof)"

Range Predicate

This predicate is used to specify a value range for a range facet. For example:

year > 1999
year <= 2002
price < 17500
time >= 2011-10-20 15:30:00
time < 2011/12/31 10:00:00

BETWEEN Predicate

This predicate specifies that a range facet column has to be within a bound for all selected documents. Both upper bound and lower bound are inclusive. For example:

year BETWEEN 1995 AND 2000

Time Predicate

This predicate is used on a column that contains time stamp values (and the column should be a range facet too). This is useful to search documents that are indexed in real-time. For example:

time IN LAST 2 hours 10 mins 30 secs 100 msecs
time SINCE 2 weeks AGO
time NOT BEFORE 2 weeks AGO
time AFTER 2011-10-20 15:30:00

MATCH AGAINST Predicate

This predicate is used to perform full text searches on one or more columns that have a string data type. When more than one columns are given in the predicate, then any column in the list that contains specified searched string would make a document satisfy this condition. For example:

MATCH(color) AGAINST("bl*")

All cars that have a "black" or "blue" color would satisfy this predicate.
MATCH(category) AGAINST("*van")

All cars in the category of "van" or "mini-van" would satisfy this predicate.
MATCH(color, category) AGAINST("*ac*")

A car with a "black" or "blue" color, or with a category of "compact" would satisfy this condition.

LIKE Predicate

This is a predicate to do wildcard text match. Two wildcards are supported: % and _. Wildcard % matches zero or more characters, while _ matches exactly one character. For example:

color LIKE "bl%"

All cars that have a "black" or "blue" color would satisfy this predicate.
color LIKE "bl__"

All cars with a "blue" color would satisfy this condition, but the cars with a "black" color would not.

Note that wildcards % and _, not Lucene's * and ? are used in BQL. This is mainly to make BQL more compatible with SQL. However, if * or ? is used, it is also accepted.

ORDER BY Clause

ORDER BY clause specifies how you want to have the search results sorted. You can sort the results based on one or multiple columns, in either ascending or descending order.

The clause takes the following form:

ORDER BY <column_name> [(ASC | DESC)] (',' <column_name> [(ASC | DESC)])*

ORDER BY clause can only be specified once in a SELECT statement.

LIMIT Clause

LIMIT clause specifies how many results you want to get and what the starting offset is in the entire matched document set.

A LIMIT clause takes one of the following two formats:

LIMIT <count>

LIMIT <offset> ',' <count>

By default, the offset is set to 0 and the count is set to 10.

LIMIT clause can only be specified once in a SELECT statement.

GROUP BY Clause

GROUP BY clause supports the grouping of search results based on a facet. It takes the following form:

GROUP BY <facet_name> [TOP <max_per_group>]

The facet name in a GROUP BY clause has to be a defined facet in the index. A non-facet column cannot be used to do group-by. [TOP <max_per_group>] specifies how many results you want to have for each group. If it is omitted, the default value is 10.

GROUP BY clause can only be specified once in a SELECT statement.

BROWSE BY Clause

This clause is a BQL feature for faceted search. You can use it to get the facet count information for one or multiple facets returned with the search results. A BROWSE BY clause takes the following form:

BROWSE BY <facet_spec> ( ',' <facet_spec> )*

where each <facet_spec> is defined as:

<facet_name> ['(' <expand_flag> <count><count> <facet_ordering> ')']

Here, each <facet_spec> optionally specifies the following information about the facet you want to get the facet count information for:

whether the selection is expanded (default false)
the minimum number of hit counts (the first <count>)
the maximum number of hit counts (the second <count>)
facet ordering method: "hits" or "value" (default "hits")

BROWSE BY clause can only be specified once in a SELECT statement.

GIVEN FACET PARAM Clause

In a faceted search system, a run-time facet handler usually requires initialization parameter(s) to be provided at search time for each query. For example, in a search system that searches LinkedIn shares or Twitter tweets, one or more run-time facets usually require the searcher to provide his/her user Id (or user name) and the time when the search is performed.

One run-time facet may need multiple initialization parameters, each of which has a different name and/or a different data type. These parameters can be specified in the SELECT statement using the GIVEN FACET PARAM clause, which takes the following form:

GIVEN FACET PARAM <facet_param> ( ',' <facet_param> )*

Each <facet_param> is specified as a 4-typle:

'(' <facet_name> <facet_param_name> <facet_param_type> <facet_param_value> ')'

All the information in this tuple should match what is defined in the schema for this facet.

GIVEN FACET PARAM clause can only be specified once in a SELECT statement.

FETCHING STORED Clause

This clause specifies if you want to retrieve the source data of the documents in the search results. It take the following format:

FETCHING STORED (TRUE | FALSE)?

When the TRUE or FALSE value is not specified, the value is set to TRUE. If this clause itself is not specified in the SELECT statement, the flag is implicitly set to FALSE.

FETCHING STORED clause can only be specified once in a SELECT statement.

Comments in BQL Statements

You can include comments in your BQL statements. Comments may be needed for some complicated queries.

Two comment styles are supported:

Block comments

Block comments in BQL are similar to the block comments in C. A block comment can span multiple lines. All characters inside a pair of /* and */ are treated as part of a block comment and ignored by BQL parser.

Example:
```
SELECT *
FROM   cars
WHERE  /* Here is my comment, we are going to
          add some search conditions here.
        */
       color = 'red'
AND    year > 1999
    
```
Line comments

A line comment in BQL starts with --. All characters after -- on the same line will be treated as comments and ignored by BQL parser.

Example:
```
SELECT *
FROM   cars    -- Comment: the index name "cars" is optional
WHERE  color = 'red'
    
```

Query Examples

The best way to learn about BQL is to learn from BQL query examples. In this section, we are going to present a series of BQL queries that cover most of the main features.

The Simplest Query

Find the first 10 cars in the index:

SELECT * FROM cars;

This query searches for the top 10 cars, sorted by time, from index cars. All columns are selected. Number 10 is the default number of results to be returned, and time is the default order-by column.

Results:

+---------------+---------------------------------+ ... +-------+
| category      | city                            |     | _uid  |
+---------------+---------------------------------+ ... +-------+
| station wagon | u.s.a./florida/tampa            |     | 14999 |
| compact       | u.s.a./florida/tampa            |     | 0     |
| exotic        | u.s.a./florida/tampa            |     | 242   |
| compact       | u.s.a./texas/houston            |     | 1     |
| compact       | u.s.a./california/sunnyvale     |     | 241   |
| compact       | u.s.a./texas/houston            |     | 2     |
| exotic        | u.s.a./california/san francisco |     | 244   |
| compact       | u.s.a./california/san francisco |     | 243   |
| compact       | u.s.a./california/sacramento    |     | 3     |
| compact       | u.s.a./california/san diego     |     | 4     |
+---------------+---------------------------------+ ... +-------+
10 rows in set, 15000 hits, 15000 total docs (server: 20ms, total: 68ms)

Queries with Limited Number of Results

You can use the limit clause in a query to specify the starting offset and number of documents to be returned in the results. By default, the starting offset is 0, and number of results to be returned is 10. If only one number is specified in the limit clause, then it is treated the number of results.

The limit clause is mainly used for pagination on search result age.

Find the next 10 cars starting from offset 5:

SELECT *
FROM   cars
LIMIT  5, 10

Results:

+----------+---------------------------------+ ... +-----+
| category | city                            |     | _uid|
+----------+---------------------------------+ ... +-----+
| compact  | u.s.a./texas/houston            |     | 2   |
| exotic   | u.s.a./california/san francisco |     | 244 |
| compact  | u.s.a./california/sacramento    |     | 3   |
| exotic   | china/beijing                   |     | 245 |
| compact  | u.s.a./california/san diego     |     | 4   |
| exotic   | u.s.a./new york/rochester       |     | 246 |
| compact  | china/shanghai                  |     | 5   |
| exotic   | u.s.a./new york/binghamton      |     | 247 |
| compact  | u.s.a./utah/salt lake city      |     | 6   |
| exotic   | china/shanghai                  |     | 248 |
+----------+---------------------------------+ ... +-----+
10 rows in set, 15000 hits, 15000 total docs (server: 10ms, total: 60ms)

Just get the top 3 results:

SELECT *
FROM   cars
LIMIT  3

Results:

+----------+-----------------------------+ ... +-----+
| category | city                        |     | _uid|
+----------+-----------------------------+ ... +-----+
| compact  | u.s.a./california/sunnyvale |     | 1   |
| compact  | u.s.a./florida/tampa        |     | 0   |
| exotic   | u.s.a./florida/tampa        |     | 242 |
+----------+-----------------------------+ ... +-----+
3 rows in set, 15001 hits, 15001 total docs (server: 7ms, total: 27ms)

Queries with Basic Conditions

Find all red cars:

SELECT color, year, makemodel
FROM   cars
WHERE  color = "red"

Results:

+-------+----------------------+------------------------+
| color | year                 | makemodel              |
+-------+----------------------+------------------------+
| red   | 00000000000000001996 | european/bentley/azure |
| red   | 00000000000000001995 | european/bentley/azure |
| red   | 00000000000000002000 | european/bentley/azure |
| red   | 00000000000000001995 | european/bentley/azure |
| red   | 00000000000000001994 | european/bentley/azure |
| red   | 00000000000000001995 | asian/acura/3.2tl      |
| red   | 00000000000000001996 | asian/acura/3.2tl      |
| red   | 00000000000000001999 | european/bentley/azure |
| red   | 00000000000000002002 | european/bentley/azure |
| red   | 00000000000000001996 | asian/acura/integra    |
+-------+----------------------+------------------------+
10 rows in set, 2160 hits, 15000 total docs (server: 5ms, total: 41ms)

Find all red and blue cars:

SELECT color, year, makemodel
FROM   cars
WHERE  color IN ("red", "blue")

Results:

+-------+----------------------+------------------------+
| color | year                 | makemodel              |
+-------+----------------------+------------------------+
| blue  | 00000000000000001999 | asian/acura/tl         |
| red   | 00000000000000001996 | european/bentley/azure |
| blue  | 00000000000000001998 | asian/acura/tl         |
| red   | 00000000000000001995 | european/bentley/azure |
| blue  | 00000000000000001995 | asian/acura/1.6el      |
| red   | 00000000000000002000 | european/bentley/azure |
| blue  | 00000000000000001993 | asian/acura/3.2tl      |
| blue  | 00000000000000001998 | asian/acura/tl         |
| red   | 00000000000000001995 | european/bentley/azure |
| red   | 00000000000000001994 | european/bentley/azure |
+-------+----------------------+------------------------+
10 rows in set, 3264 hits, 15000 total docs (server: 4ms, total: 33ms)

Find all 1999 or 2000 cars that are not black nor red:

SELECT color, year, makemodel
FROM   cars
WHERE  color NOT IN ("black", "red")
AND    year BETWEEN 1999 AND 2000

Results:

+--------+----------------------+------------------------+
| color  | year                 | makemodel              |
+--------+----------------------+------------------------+
| blue   | 00000000000000001999 | asian/acura/tl         |
| white  | 00000000000000001999 | asian/acura/1.6el      |
| yellow | 00000000000000001999 | asian/acura/3.2tl      |
| silver | 00000000000000002000 | asian/acura/3.5rl      |
| silver | 00000000000000002000 | asian/acura/3.5rl      |
| yellow | 00000000000000002000 | asian/acura/integra    |
| yellow | 00000000000000002000 | asian/acura/integra    |
| yellow | 00000000000000002000 | european/bentley/azure |
| yellow | 00000000000000002000 | european/bentley/azure |
| yellow | 00000000000000002000 | asian/acura/tl         |
+--------+----------------------+------------------------+
10 rows in set, 1934 hits, 15000 total docs (server: 4ms, total: 35ms)

Find all cars in New York state:

SELECT color, city, price
FROM   cars
WHERE  city in ("u.s.a./new york")

Results:

+--------+----------------------------+-------------------------+
| color  | city                       | price                   |
+--------+----------------------------+-------------------------+
| white  | u.s.a./new york/albany     | 00000000000000007500.00 |
| red    | u.s.a./new york/rochester  | 00000000000000014500.00 |
| green  | u.s.a./new york/syracuse   | 00000000000000009500.00 |
| yellow | u.s.a./new york/binghamton | 00000000000000007200.00 |
| blue   | u.s.a./new york/new york   | 00000000000000009300.00 |
| yellow | u.s.a./new york/new york   | 00000000000000015400.00 |
| yellow | u.s.a./new york/new york   | 00000000000000015200.00 |
| black  | u.s.a./new york/albany     | 00000000000000009200.00 |
| gold   | u.s.a./new york/new york   | 00000000000000011100.00 |
| red    | u.s.a./new york/rochester  | 00000000000000009500.00 |
+--------+----------------------------+-------------------------+
10 rows in set, 2781 hits, 15000 total docs (server: 5ms, total: 37ms)

Queries with AND, OR, and NOT Logic in Value Selections

Find all cars tagged with both "cool" and "hybrid" but not "favorite":

SELECT tags, price from cars
WHERE  tags CONTAINS ALL ("cool", "hybrid") EXCEPT("favorite")
LIMIT  5

Results:

+----------------------------------+-------------------------+
| tags                             | price                   |
+----------------------------------+-------------------------+
| automatic,cool,hybrid,reliable   | 00000000000000009400.00 |
| cool,hybrid,moon-roof,navigation | 00000000000000011500.00 |
| automatic,cool,hybrid,reliable   | 00000000000000006300.00 |
| cool,hybrid,moon-roof,reliable   | 00000000000000006500.00 |
| cool,hybrid,moon-roof,reliable   | 00000000000000007100.00 |
+----------------------------------+-------------------------+
5 rows in set, 491 hits, 15000 total docs (server: 9ms, total: 28ms)

Find all cars tagged with either "cool" or "hybrid" but not "mp3":

SELECT tags, price
FROM   cars
WHERE  tags IN ("cool", "hybrid") EXCEPT ("mp3")
LIMIT  5

Results:

+----------------------------------------------+-------------------------+
| tags                                         | price                   |
+----------------------------------------------+-------------------------+
| hybrid,leather,moon-roof,reliable            | 00000000000000007500.00 |
| automatic,chick magnet,cool,highend,reliable | 00000000000000014200.00 |
| cool,electric,favorite,navigation            | 00000000000000007300.00 |
| cool,electric,favorite,reliable              | 00000000000000007200.00 |
| automatic,hybrid,leather,reliable            | 00000000000000007100.00 |
+----------------------------------------------+-------------------------+
5 rows in set, 8176 hits, 15000 total docs (server: 6ms, total: 25ms)

Having Search Results Sorted

You can sort the search result based on one or more columns, in either ascending (the default) or descending order.

Find the top 5 cheapest but newest cars.

SELECT   year, makemodel, price
FROM     cars
ORDER BY year desc, price
LIMIT    5

Results:

+----------------------+-------------------------------+-------------------------+
| year                 | makemodel                     | price                   |
+----------------------+-------------------------------+-------------------------+
| 00000000000000002002 | asian/subaru/justy            | 00000000000000002100.00 |
| 00000000000000002002 | asian/subaru/justy            | 00000000000000002100.00 |
| 00000000000000002002 | north american/dodge/colt     | 00000000000000002400.00 |
| 00000000000000002002 | north american/mercury/tracer | 00000000000000002400.00 |
| 00000000000000002002 | north american/mercury/tracer | 00000000000000002500.00 |
+----------------------+-------------------------------+-------------------------+
5 rows in set, 15000 hits, 15000 total docs (server: 22ms, total: 50ms)

Find the top 5 most expensive but oldest cars:

SELECT   year, makemodel, price
FROM     cars
ORDER BY year asc, price desc
LIMIT    5

Results:

+----------------------+-----------------------------+-------------------------+
| year                 | makemodel                   | price                   |
+----------------------+-----------------------------+-------------------------+
| 00000000000000001993 | european/ferrari/360 modena | 00000000000000019500.00 |
| 00000000000000001993 | asian/acura/nsx             | 00000000000000019500.00 |
| 00000000000000001993 | european/aston martin/db7   | 00000000000000019500.00 |
| 00000000000000001993 | european/ferrari/360 modena | 00000000000000019500.00 |
| 00000000000000001993 | asian/acura/nsx             | 00000000000000019500.00 |
+----------------------+-----------------------------+-------------------------+
5 rows in set, 15000 hits, 15000 total docs (server: 5ms, total: 27ms)

Queries Using MATCH AGAINST Predicate

Find all the cars with color "black" or "blue":

SELECT color, year, makemodel
FROM   cars
WHERE  MATCH(color) AGAINST("bl*")

Results:

+-------+----------------------+---------------------+
| color | year                 | makemodel           |
+-------+----------------------+---------------------+
| blue  | 00000000000000001993 | asian/acura/3.2tl   |
| black | 00000000000000001994 | asian/acura/3.2tl   |
| black | 00000000000000001999 | asian/acura/3.5rl   |
| black | 00000000000000002002 | asian/acura/3.5rl   |
| black | 00000000000000002000 | asian/acura/3.5rl   |
| black | 00000000000000001996 | asian/acura/integra |
| black | 00000000000000001996 | asian/acura/integra |
| black | 00000000000000001993 | asian/acura/integra |
| black | 00000000000000001999 | asian/acura/integra |
| black | 00000000000000001994 | asian/acura/integra |
+-------+----------------------+---------------------+
10 rows in set, 4245 hits, 15000 total docs (server: 8ms, total: 46ms)

Find all the cars with color column or category column containing substring "ac" (like color "black" or category "compact"):

SELECT color, category, year, makemodel
FROM   cars
WHERE  MATCH(color, category) AGAINST("*ac*")
LIMIT  20

Results:

+--------+------------+----------------------+---------------------+
| color  | category   | year                 | makemodel           |
+--------+------------+----------------------+---------------------+
| white  | compact    | 00000000000000001993 | asian/acura/1.6el   |
| white  | compact    | 00000000000000001999 | asian/acura/1.6el   |
| gold   | compact    | 00000000000000001996 | asian/acura/1.6el   |
| silver | compact    | 00000000000000001997 | asian/acura/1.6el   |
| blue   | compact    | 00000000000000001993 | asian/acura/3.2tl   |
| gold   | compact    | 00000000000000002001 | asian/acura/3.2tl   |
| black  | compact    | 00000000000000001994 | asian/acura/3.2tl   |
| green  | compact    | 00000000000000001995 | asian/acura/3.2tl   |
| yellow | compact    | 00000000000000001999 | asian/acura/3.2tl   |
| gold   | compact    | 00000000000000001993 | asian/acura/3.2tl   |
| red    | compact    | 00000000000000001996 | asian/acura/3.2tl   |
| black  | luxury     | 00000000000000001999 | asian/acura/3.5rl   |
| black  | luxury     | 00000000000000002002 | asian/acura/3.5rl   |
| black  | luxury     | 00000000000000002000 | asian/acura/3.5rl   |
| black  | sports car | 00000000000000001996 | asian/acura/integra |
| black  | sports car | 00000000000000001996 | asian/acura/integra |
| black  | sports car | 00000000000000001993 | asian/acura/integra |
| black  | sports car | 00000000000000001999 | asian/acura/integra |
| black  | sports car | 00000000000000001994 | asian/acura/integra |
| black  | sports car | 00000000000000001993 | asian/acura/integra |
+--------+------------+----------------------+---------------------+
20 rows in set, 7178 hits, 15000 total docs (server: 4ms, total: 134ms)

Find all the car categories that are not van nor mini-van:

SELECT   color, category, year, makemodel
FROM     cars
WHERE    NOT MATCH(color, category) AGAINST("*van")
GROUP BY category TOP 1
LIMIT    1000

Results:

+--------+---------------+----------------------+-----------------------------+
| color  | category      | year                 | makemodel                   |
+--------+---------------+----------------------+-----------------------------+
| white  | compact       | 00000000000000001993 | asian/acura/1.6el           |
| white  | luxury        | 00000000000000001995 | asian/acura/3.5rl           |
| red    | sports car    | 00000000000000001996 | asian/acura/integra         |
| yellow | exotic        | 00000000000000001997 | european/aston martin/db7   |
| black  | sedan         | 00000000000000001997 | north american/eagle/vision |
| green  | suv           | 00000000000000002002 | north american/ford/bronco  |
| yellow | truck         | 00000000000000002001 | asian/isuzu/pickup          |
| green  | station wagon | 00000000000000001995 | north american/saturn/sw    |
| gold   | sub-compact   | 00000000000000001993 | north american/ford/aspire  |
+--------+---------------+----------------------+-----------------------------+
9 rows in set, 6980 hits, 7500 total docs (server: 5ms, total: 84ms)

You can compare the above results (containing 9 rows) with the ones from the following query (containing 11 rows):

SELECT   color, category, year, makemodel
FROM     cars
GROUP BY category TOP 1
LIMIT    1000

Results:

+--------+---------------+----------------------+------------------------------------+
| color  | category      | year                 | makemodel                          |
+--------+---------------+----------------------+------------------------------------+
| white  | compact       | 00000000000000001993 | asian/acura/1.6el                  |
| white  | luxury        | 00000000000000001995 | asian/acura/3.5rl                  |
| red    | sports car    | 00000000000000001996 | asian/acura/integra                |
| yellow | exotic        | 00000000000000001997 | european/aston martin/db7          |
| black  | sedan         | 00000000000000001997 | north american/eagle/vision        |
| green  | suv           | 00000000000000002002 | north american/ford/bronco         |
| green  | van           | 00000000000000001994 | north american/ford/club wagon     |
| yellow | truck         | 00000000000000002001 | asian/isuzu/pickup                 |
| green  | station wagon | 00000000000000001995 | north american/saturn/sw           |
| white  | mini-van      | 00000000000000001993 | north american/chevrolet/astro van |
| gold   | sub-compact   | 00000000000000001993 | north american/ford/aspire         |
+--------+---------------+----------------------+------------------------------------+
11 rows in set, 15000 hits, 15000 total docs (server: 7ms, total: 50ms)

Queries Using LIKE Predicate

Find all the cars with color "black" or "blue":

SELECT color, year, makemodel
FROM   cars
WHERE  color LIKE "bl%"

Results:

+-------+------------+----------------------+---------------------+
| color | category   | year                 | makemodel           |
+-------+------------+----------------------+---------------------+
| blue  | compact    | 00000000000000001993 | asian/acura/3.2tl   |
| black | compact    | 00000000000000001994 | asian/acura/3.2tl   |
| black | luxury     | 00000000000000001999 | asian/acura/3.5rl   |
| black | luxury     | 00000000000000002002 | asian/acura/3.5rl   |
| black | luxury     | 00000000000000002000 | asian/acura/3.5rl   |
| black | sports car | 00000000000000001996 | asian/acura/integra |
| black | sports car | 00000000000000001996 | asian/acura/integra |
| black | sports car | 00000000000000001993 | asian/acura/integra |
| black | sports car | 00000000000000001999 | asian/acura/integra |
| black | sports car | 00000000000000001994 | asian/acura/integra |
+-------+------------+----------------------+---------------------+
10 rows in set, 4245 hits, 15000 total docs (server: 3ms, total: 78ms)

Find all cars with color matching pattern "bl__" (4 characters only):

SELECT color, category, year, makemodel
FROM   cars
WHERE  color like "bl__"

Results:

+-------+----------+----------------------+-----------------------------+
| color | category | year                 | makemodel                   |
+-------+----------+----------------------+-----------------------------+
| blue  | compact  | 00000000000000001993 | asian/acura/3.2tl           |
| blue  | compact  | 00000000000000001999 | asian/acura/tl              |
| blue  | compact  | 00000000000000001998 | asian/acura/tl              |
| blue  | compact  | 00000000000000001998 | asian/acura/tl              |
| blue  | compact  | 00000000000000001997 | asian/acura/tl              |
| blue  | compact  | 00000000000000001998 | asian/acura/vigor           |
| blue  | compact  | 00000000000000001999 | north american/eagle/2000   |
| blue  | sedan    | 00000000000000001993 | north american/eagle/vision |
| blue  | sedan    | 00000000000000002000 | north american/eagle/vision |
| blue  | sedan    | 00000000000000001995 | north american/eagle/vision |
+-------+----------+----------------------+-----------------------------+
10 rows in set, 1104 hits, 15000 total docs (server: 3ms, total: 59ms)

Queries with Full Text Search

In the WHERE clause, you can add a condition for full text search, which is called a (text) query. This condition is to find the documents that contain matching text. You can use Lucene Query Syntax in the text string that you search on.

Find all the cars that are tagged with "hybrid" and "navigation":

SELECT tags, makemodel
FROM   cars
WHERE  QUERY IS "hybrid AND navigation"

Results:

+-------------------------------------+----------------------------+
| tags                                | makemodel                  |
+-------------------------------------+----------------------------+
| hybrid,leather,moon-roof,navigation | asian/acura/tl             |
| favorite,hybrid,mp3,navigation      | asian/acura/tl             |
| favorite,hybrid,mp3,navigation      | asian/acura/3.2tl          |
| favorite,hybrid,mp3,navigation      | asian/acura/tl             |
| cool,hybrid,moon-roof,navigation    | asian/acura/tl             |
| cool,favorite,hybrid,navigation     | asian/acura/vigor          |
| automatic,cool,hybrid,navigation    | asian/acura/vigor          |
| cool,hybrid,moon-roof,navigation    | asian/acura/vigor          |
| cool,hybrid,moon-roof,navigation    | north american/asuna/se/gt |
| automatic,hybrid,leather,navigation | european/saab/900          |
+-------------------------------------+----------------------------+
10 rows in set, 778 hits, 15000 total docs (server: 186ms, total: 209ms)

A more complicated example:

SELECT color, tags, city
FROM   cars
WHERE  QUERY IS "(hybrid OR moon-roof) AND mp3 NOT cool NOT navigation"
AND    city in ("u.s.a./new york")
AND    color = "red"

Results:

+-------+-------------------------------+----------------------------+
| color | tags                          | city                       |
+-------+-------------------------------+----------------------------+
| red   | automatic,hybrid,mp3,reliable | u.s.a./new york/new york   |
| red   | hybrid,moon-roof,mp3,reliable | u.s.a./new york/buffalo    |
| red   | favorite,hybrid,mp3,reliable  | u.s.a./new york/syracuse   |
| red   | favorite,hybrid,mp3,reliable  | u.s.a./new york/buffalo    |
| red   | automatic,hybrid,mp3,reliable | u.s.a./new york/syracuse   |
| red   | favorite,hybrid,mp3,reliable  | u.s.a./new york/binghamton |
| red   | automatic,hybrid,mp3,reliable | u.s.a./new york/syracuse   |
+-------+-------------------------------+----------------------------+
7 rows in set, 7 hits, 15000 total docs (server: 17ms, total: 44ms)

Queries with GROUP BY

You can group the search results of a query by one facet, which can be a simple column or a facet built upon a group of columns. You can also specify how many hits you want to keep in each group (the default is 10).

Find the first 10 groups of cars, with 2 hits in each group:

SELECT   category, city, makemodel
FROM     cars
GROUP BY category TOP 2

Results:

========================================================================================
| category      | city                            | makemodel                          |
========================================================================================
| compact       | u.s.a./texas/houston            | asian/acura/tl                     |
| compact       | u.s.a./florida/tampa            | asian/acura/1.6el                  |
+---------------+---------------------------------+------------------------------------+
| exotic        | u.s.a./florida/tampa            | european/bentley/azure             |
| exotic        | u.s.a./california/san francisco | european/bentley/azure             |
+---------------+---------------------------------+------------------------------------+
| luxury        | u.s.a./florida/orlando          | asian/acura/3.5rl                  |
| luxury        | u.s.a./new york/rochester       | asian/acura/3.5rl                  |
+---------------+---------------------------------+------------------------------------+
| sports car    | u.s.a./california/sunnyvale     | asian/acura/integra                |
| sports car    | u.s.a./texas/austin             | asian/acura/integra                |
+---------------+---------------------------------+------------------------------------+
| sedan         | china/beijing                   | north american/eagle/vision        |
| sedan         | australia/perth                 | north american/eagle/vision        |
+---------------+---------------------------------+------------------------------------+
| suv           | china/shanghai                  | north american/ford/bronco         |
| suv           | u.s.a./california/sacramento    | north american/ford/bronco         |
+---------------+---------------------------------+------------------------------------+
| van           | u.s.a./florida/palm beach       | north american/ford/club wagon     |
| van           | u.s.a./new york/albany          | north american/ford/club wagon     |
+---------------+---------------------------------+------------------------------------+
| truck         | u.s.a./utah/provo               | asian/mazda/b-series               |
| truck         | australia/melbourn              | asian/isuzu/pickup                 |
+---------------+---------------------------------+------------------------------------+
| station wagon | u.s.a./texas/dallas             | north american/saturn/sw           |
| station wagon | u.s.a./california/san jose      | north american/saturn/sw           |
+---------------+---------------------------------+------------------------------------+
| mini-van      | u.s.a./texas/austin             | north american/chevrolet/astro van |
| mini-van      | u.s.a./california/san jose      | north american/chevrolet/astro van |
+---------------+---------------------------------+------------------------------------+
========================================================================================
10 groups in set, 15000 hits, 15000 total docs (server: 55ms, total:130ms)

Find the numbers of cars in different categories:

SELECT   category, _grouphitscount
FROM     cars
GROUP BY category top 1

Results:

+---------------+----------------+
| category      | _grouphitscount |
+---------------+-----------------+
| compact       | 4169            |
| exotic        | 1902            |
| luxury        | 2735            |
| sports car    | 1494            |
| sedan         | 990             |
| suv           | 1449            |
| van           | 168             |
| truck         | 611             |
| station wagon | 186             |
| mini-van      | 869             |
+---------------+-----------------+
10 rows in set, 15000 hits, 15000 total docs (server: 3ms, total: 38ms)

Getting Facet Information Using BROWSE BY Clause

BQL is designed to support faceted search, so we have to make it possible to get facet information along with the search results. This can be done using the Browse By clause, where you can specify one or more facets for which you want to get the facet count information. For each facet in the BROWSE BY clause, you can optionally include

whether the selection is expanded (default false)
the minimum number of hit counts (default 1)
the maximum number of hit counts (default 10)
facet ordering method ("hits" or "value") (default "hits")

Here is an example:

Query:

SELECT    color, year, tags, price
FROM      cars
WHERE     QUERY IS "cool"
AND       tags CONTAINS ALL ("cool", "hybrid") EXCEPT ("favorite")
AND       color in ("red")
ORDER BY  price desc
LIMIT     0,10
BROWSE BY color(true, 1, 10, hits), year(true, 1, 10, value), price

Results:

+-------+----------------------+----------------------------------+-------------------------+
| color | year                 | tags                             | price                   |
+-------+----------------------+----------------------------------+-------------------------+
| red   | 00000000000000002000 | cool,hybrid,moon-roof,navigation | 00000000000000014500.00 |
| red   | 00000000000000001993 | cool,hybrid,moon-roof,navigation | 00000000000000014400.00 |
| red   | 00000000000000002002 | automatic,cool,hybrid,navigation | 00000000000000014200.00 |
| red   | 00000000000000001998 | automatic,cool,hybrid,navigation | 00000000000000012100.00 |
| red   | 00000000000000002002 | automatic,cool,hybrid,reliable   | 00000000000000011500.00 |
| red   | 00000000000000002002 | automatic,cool,hybrid,reliable   | 00000000000000011400.00 |
| red   | 00000000000000001998 | automatic,cool,hybrid,reliable   | 00000000000000011400.00 |
| red   | 00000000000000001996 | automatic,cool,hybrid,reliable   | 00000000000000011200.00 |
| red   | 00000000000000001999 | automatic,cool,hybrid,reliable   | 00000000000000011100.00 |
| red   | 00000000000000002001 | cool,hybrid,moon-roof,reliable   | 00000000000000010500.00 |
+-------+----------------------+----------------------------------+-------------------------+
10 rows in set, 59 hits, 15000 total docs (server: 337ms, total: 372ms)
+-------------+
| color       |
+-------------+
| white  (73) |
| yellow (73) |
| blue   (62) |
| silver (61) |
| red    (59) |
| green  (58) |
| gold   (53) |
| black  (52) |
+-------------+
+-----------------------+
| price                 |
+-----------------------+
| [6800 TO 9900]   (27) |
| [* TO 6700]      (21) |
| [10000 TO 13100] (8)  |
| [13200 TO 17300] (3)  |
+-----------------------+
+---------------------+
| year                |
+---------------------+
| [1993 TO 1994] (16) |
| [1995 TO 1996] (13) |
| [1997 TO 1998] (10) |
| [1999 TO 2000] (9)  |
| [2001 TO 2002] (11) |
+---------------------+

Queries with Run-Time Facet Handler Initialization Parameters

On a search system for LinkedIn shares, find recent updates from member 12345678 himself (i.e. value 0 for the Network facet) and all his first degree connections (i.e. value 1 for the Network facet) in US:

SELECT _uid, Network, userid, country
FROM   shares
WHERE  country = "us"
AND    Network in (0, 1)
GIVEN FACET PARAM (Network, "member_id", int, 12345678)

Results:

+---------------------+---------+----------------------+---------+
| _uid                | Network | userid               | country |
+---------------------+---------+----------------------+---------+
| 5527797854963249152 | 0       | 00000000000012345678 | us      |
| 5527805402646839296 | 1       | 00000000000042593551 | us      |
| 5527816561408086016 | 0       | 00000000000012345678 | us      |
| 5527825082430267392 | 1       | 00000000000022593551 | us      |
| 5527829323551084544 | 0       | 00000000000012345678 | us      |
| 5527848889647902720 | 1       | 00000000000004730909 | us      |
| 5527853965330358272 | 1       | 00000000000004730909 | us      |
| 5527884781573898240 | 1       | 00000000000026325826 | us      |
| 5527487070454427648 | 0       | 00000000000012345678 | us      |
| 5527488521884930048 | 0       | 00000000000012345678 | us      |
+---------------------+---------+----------------------+---------+
10 rows in set, 14 hits, 10749644 total docs (server: 432ms, total: 472ms)

Query with a Relevance Model

Find the top 10 black or red cars using a special relevance model, which gives higher scores to the cars of year 1999 or 2000 (with a boost value of 100) or the black cars (with a boost value of 50):

SELECT year, color, price, _score
FROM cars
WHERE color in ('black', 'red')
USING RELEVANCE MODEL my_model (favorite_color:'black', favorite_years:[1999, 2000])
      DEFINED AS (String favorite_color, IntOpenHashSet favorite_years)
      BEGIN
        float boost = 0.0;
        if (favorite_years.contains(year)) 
          boost += 100;
        if (favorite_color.equals(color))
          boost += 50;
        return price + boost;
      END
ORDER BY RELEVANCE

Results:

+----------------------+-------+-------------------------+--------+
| year                 | color | price                   | _score |
+----------------------+-------+-------------------------+--------+
| 00000000000000001999 | black | 00000000000000019500.00 | 19650  |
| 00000000000000001999 | black | 00000000000000019500.00 | 19650  |
| 00000000000000002000 | red   | 00000000000000019500.00 | 19600  |
| 00000000000000001999 | red   | 00000000000000019500.00 | 19600  |
| 00000000000000002000 | red   | 00000000000000019500.00 | 19600  |
| 00000000000000001999 | black | 00000000000000019400.00 | 19550  |
| 00000000000000001996 | black | 00000000000000019500.00 | 19550  |
| 00000000000000001993 | black | 00000000000000019500.00 | 19550  |
| 00000000000000001994 | black | 00000000000000019500.00 | 19550  |
| 00000000000000001997 | black | 00000000000000019500.00 | 19550  |
+----------------------+-------+-------------------------+--------+
10 rows in set, 5301 hits, 15000 total docs (server: 9ms, total: 28ms)

Query with Sensei map reduce functions:

Sensei Map Reduce functions could be executed from BQL. Such functions as sum, avg, count, min, max could be specified in the select clause. They might be used in conjunction with group by clause. For example:

select sum(cost), avg(year) where tag='automatic' group by groupid, color
select distinctCount(year) where tag='manual'

One could also invoke an arbitrary map reduce function using the 'EXECUTE' clause

select from Sensei where color = 'pink' execute(com.senseidb.search.req.mapred.functions.CustomMapReduce, year:2001, myMap:{'limit':1000, 'groupBy':'year'})

Documentation

Developer

Project

Browse Query Language (BQL)

What is BQL?

BNF Grammar for BQL

BNF Notation

BQL Grammar

BQL Statement Syntax

DESCRIBE Statement

SELECT Statement

Select List

FROM Clause

WHERE Clause

Predicate

IN Predicate

CONTAINS ALL Predicate

Equal Predicate

Not Equal Predicate

Query Predicate

Range Predicate

BETWEEN Predicate

Time Predicate

MATCH AGAINST Predicate

LIKE Predicate

ORDER BY Clause

LIMIT Clause

GROUP BY Clause

BROWSE BY Clause

GIVEN FACET PARAM Clause

FETCHING STORED Clause

Comments in BQL Statements

Query Examples

The Simplest Query

Queries with Limited Number of Results

Queries with Basic Conditions

Queries with AND, OR, and NOT Logic in Value Selections

Having Search Results Sorted

Queries Using MATCH AGAINST Predicate

Queries Using LIKE Predicate

Queries with Full Text Search

Queries with GROUP BY

Getting Facet Information Using BROWSE BY Clause

Queries with Run-Time Facet Handler Initialization Parameters

Query with a Relevance Model

Query with Sensei map reduce functions: