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.

SymbolMeaning
|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>

<where_clause> ::= WHERE <search_expr>

<search_expr> ::= <boolean_expression>

<boolean_expression> ::= <boolean_term> ( OR <boolean_term> )*

<boolean_term> ::= <boolean_term> (AND <boolean_term> )*

<boolean_factor> ::= <predicate>
                   | '(' <boolean_expression> ')'

<predicate> ::= <in_predicate>
              | <contains_all_predicate>
              | <equal_predicate>
              | <not_equal_predicate>
              | <query_predicate>
              | <between_predicate>
              | <range_predicate>
              | <time_predicate>
              | <match_predicate>
              | <like_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> <value>

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

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

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

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

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

<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> ':' <quoted_string>

<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>

<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> ::= <facet_name> [TOP <max_per_group>]

<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

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

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

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

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

<column_name> ::= <identifier>

<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> | <number>

<number> ::= <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> ::= <digit><digit><digit><digit> ('-' | '/' ) <digit><digit>
                       ('-' | '/' ) <digit><digit>
                       <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.

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:

CONTAINS ALL Predicate

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

Equal Predicate

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

Not Equal Predicate

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

Query Predicate

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

Range Predicate

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

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:

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:

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:

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:

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:

  1. whether the selection is expanded (default false)

  2. the minimum number of hit counts (the first <count>)

  3. the maximum number of hit counts (the second <count>)

  4. 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:

  1. 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
        
  2. 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.

  1. 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)
        
  2. 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

  1. 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)
        
  2. 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)
        
  3. 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)
        
  4. 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

  1. 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)
        
  2. 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.

  1. 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)
        
  2. 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

  1. 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)
        
  2. 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)
        
  3. 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

  1. 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)
        
  2. 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.

  1. 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)
        
  2. 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

  1. 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)
        
  2. 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

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

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. Every parameter is specified in the clause as a 4-tuple, (facet-name, param-name, param-type, param-value).

  1. 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)