A Boolean query retrieves rows from a data table by combining multiple subqueries. Tablestore returns only rows that match the combined conditions. A subquery can be of any type, including another Boolean query.
Prerequisites
-
An OTSClient instance is initialized. For more information, see Initialize an OTSClient instance.
-
A data table is created. Data is written to the table. For more information, see Create a data table and Write data.
-
A search index is created for the data table. For more information, see Create a search index.
Parameters
|
Parameter |
Description |
|
table_name |
The name of the data table. |
|
index_name |
The name of the search index. |
|
must_queries |
The AND operator. Tablestore returns only rows that match all subquery conditions. |
|
must_not_queries |
The NOT operator. Tablestore returns only rows that match none of the subquery conditions. When multiple subqueries are specified, this parameter is equivalent to NOT(A OR B): a row is excluded if it matches any one of the subqueries. |
|
filter_queries |
A filter applied in AND logic. Tablestore returns only rows that match all filter conditions. Unlike |
|
should_queries |
The OR operator. Tablestore returns rows that match at least the minimum number of subquery conditions. A row's relevance score increases with each additional Use |
|
minimum_should_match |
The minimum number of |
Examples
The following examples show how to use each Boolean query clause. All examples call $otsClient->search($request) with QueryTypeConst::BOOL_QUERY as the query type.
AND
Use must_queries to require all conditions. The following example returns rows where the keyword field equals "keyword" AND the long field is in the range [100, 101).
$request = array(
'table_name' => 'php_sdk_test',
'index_name' => 'php_sdk_test_search_index',
'search_query' => array(
'offset' => 0,
'limit' => 2,
'get_total_count' => true,
'query' => array(
'query_type' => QueryTypeConst::BOOL_QUERY,
'query' => array(
'must_queries' => array(
array(
'query_type' => QueryTypeConst::TERM_QUERY,
'query' => array(
'field_name' => 'keyword',
'term' => 'keyword'
)
),
array(
'query_type' => QueryTypeConst::RANGE_QUERY,
'query' => array(
'field_name' => 'long',
'range_from' => 100,
'include_lower' => true,
'range_to' => 101,
'include_upper' => false
)
)
),
)
),
'sort' => array(
array(
'field_sort' => array(
'field_name' => 'keyword',
'order' => SortOrderConst::SORT_ORDER_ASC
)
),
)
),
'columns_to_get' => array(
'return_type' => ColumnReturnTypeConst::RETURN_SPECIFIED,
'return_names' => array('keyword', 'long')
)
);
$response = $otsClient->search($request);OR
Use should_queries with minimum_should_match to match any condition. The following example returns rows where the keyword field equals "keyword" OR the long field is in the range [100, 101).
$request = array(
'table_name' => 'php_sdk_test',
'index_name' => 'php_sdk_test_search_index',
'search_query' => array(
'offset' => 0,
'limit' => 2,
'get_total_count' => true,
'query' => array(
'query_type' => QueryTypeConst::BOOL_QUERY,
'query' => array(
'should_queries' => array(
array(
'query_type' => QueryTypeConst::TERM_QUERY,
'query' => array(
'field_name' => 'keyword',
'term' => 'keyword'
)
),
array(
'query_type' => QueryTypeConst::RANGE_QUERY,
'query' => array(
'field_name' => 'long',
'range_from' => 100,
'include_lower' => true,
'range_to' => 101,
'include_upper' => false
)
)
),
'minimum_should_match' => 1
)
),
'sort' => array(
array(
'field_sort' => array(
'field_name' => 'keyword',
'order' => SortOrderConst::SORT_ORDER_ASC
)
),
)
),
'columns_to_get' => array(
'return_type' => ColumnReturnTypeConst::RETURN_SPECIFIED,
'return_names' => array('keyword', 'long')
)
);
$response = $otsClient->search($request);NOT
Use must_not_queries to exclude rows that match any of the conditions. The following example excludes rows where the keyword field equals "keyword" OR the long field is in the range [100, 101) — that is, NOT(condition A OR condition B).
$request = array(
'table_name' => 'php_sdk_test',
'index_name' => 'php_sdk_test_search_index',
'search_query' => array(
'offset' => 0,
'limit' => 2,
'get_total_count' => true,
'query' => array(
'query_type' => QueryTypeConst::BOOL_QUERY,
'query' => array(
'must_not_queries' => array(
array(
'query_type' => QueryTypeConst::TERM_QUERY,
'query' => array(
'field_name' => 'keyword',
'term' => 'keyword'
)
),
array(
'query_type' => QueryTypeConst::RANGE_QUERY,
'query' => array(
'field_name' => 'long',
'range_from' => 100,
'include_lower' => true,
'range_to' => 101,
'include_upper' => false
)
)
),
)
),
'sort' => array(
array(
'field_sort' => array(
'field_name' => 'keyword',
'order' => SortOrderConst::SORT_ORDER_ASC
)
),
)
),
'columns_to_get' => array(
'return_type' => ColumnReturnTypeConst::RETURN_SPECIFIED,
'return_names' => array('keyword', 'long')
)
);
$response = $otsClient->search($request);filter_queries
Use filter_queries when you need AND logic without relevance scoring. The following example returns rows that match both conditions without scoring rows by how many conditions they meet.
$request = array(
'table_name' => 'php_sdk_test',
'index_name' => 'php_sdk_test_search_index',
'search_query' => array(
'offset' => 0,
'limit' => 2,
'get_total_count' => true,
'query' => array(
'query_type' => QueryTypeConst::BOOL_QUERY,
'query' => array(
'filter_queries' => array(
array(
'query_type' => QueryTypeConst::TERM_QUERY,
'query' => array(
'field_name' => 'keyword',
'term' => 'keyword'
)
),
array(
'query_type' => QueryTypeConst::RANGE_QUERY,
'query' => array(
'field_name' => 'long',
'range_from' => 100,
'include_lower' => true,
'range_to' => 101,
'include_upper' => false
)
)
),
)
),
'sort' => array(
array(
'field_sort' => array(
'field_name' => 'keyword',
'order' => SortOrderConst::SORT_ORDER_ASC
)
),
)
),
'columns_to_get' => array(
'return_type' => ColumnReturnTypeConst::RETURN_SPECIFIED,
'return_names' => array('keyword', 'long')
)
);
$response = $otsClient->search($request);Combined queries
Nest Boolean queries to build complex logic. The following example matches rows that satisfy: (col2 < 4 OR col3 < 5) OR (col2 = 4 AND (col3 = 5 OR col3 = 6)).
$request = array(
'table_name' => 'php_sdk_test',
'index_name' => 'php_sdk_test_index',
'search_query' => [
'offset' => 0,
'limit' => 10,
'get_total_count' => false,
'query' => [
'query_type' => QueryTypeConst::BOOL_QUERY,
'query' => [
// Final combination: (col2 < 4 or col3 < 5) or (col2 = 4 and (col3 = 5 or col3 = 6))
'should_queries' => [
[
'query_type' => QueryTypeConst::BOOL_QUERY,
'query' => [
// Combination 1: col2 < 4 or col3 < 5
'should_queries' => [
[
'query_type' => QueryTypeConst::RANGE_QUERY,
// Condition 1: col2 < 4
'query' => [
'field_name' => 'col2',
'range_to' => 4
]
],
[
'query_type' => QueryTypeConst::RANGE_QUERY,
// Condition 2: col3 < 5
'query' => [
'field_name' => 'col3',
'range_to' => 5
]
]
]
]
],
[
'query_type' => QueryTypeConst::BOOL_QUERY,
// Combination 2: (col2 = 4 and (col3 = 5 or col3 = 6))
'query' => [
'must_queries' => [
[
'query_type' => QueryTypeConst::TERM_QUERY,
// Condition 3: col2 = 4
'query' => [
'field_name' => 'col2',
'term' => 4
]
],
[
'query_type' => QueryTypeConst::BOOL_QUERY,
// Combination 3: (col3 = 5 or col3 = 6)
'query' => [
'should_queries' => [
[
'query_type' => QueryTypeConst::TERM_QUERY,
// Condition 4: col3 = 5
'query' => [
'field_name' => 'col3',
'term' => 5
]
],
[
'query_type' => QueryTypeConst::TERM_QUERY,
// Condition 5: col3 = 6
'query' => [
'field_name' => 'col3',
'term' => 6
]
]
],
'minimum_should_match' => 1
]
]
]
]
]
],
'minimum_should_match' => 1
]
]
],
// You can configure the columns_to_get parameter to specify the columns to return or specify that all columns are returned. If you do not configure this parameter, only the primary key columns are returned.
'columns_to_get' => [
//'return_type' => ColumnReturnTypeConst::RETURN_ALL // Specify that all columns are returned.
// Specify that the specified columns are returned.
'return_type' => ColumnReturnTypeConst::RETURN_SPECIFIED,
'return_names' => array('col1', 'col2')
]
);
$response = $otsClient->search($request);FAQ
References
When you use a search index to query data, you can use the following query methods: term query, terms query, match all query, match query, match phrase query, prefix query, range query, wildcard query, Boolean query, geo query, nested query, and exists query. You can use different query methods to query data from multiple dimensions based on your business requirements.
If you want to sort or paginate the rows that meet the query conditions, you can use the sorting and paging feature. For more information, see Sorting and paging.
If you want to collapse the result set based on a specific column, you can use the collapse (distinct) feature. This way, data of the specified type appears only once in the query results. For more information, see Collapse (distinct).
If you want to analyze data in a data table, such as obtaining the extreme values, sum, and total number of rows, you can perform aggregation operations or execute SQL statements. For more information, see Aggregation and SQL query.
If you want to quickly obtain all rows that meet the query conditions without the need to sort the rows, you can call the ParallelScan and ComputeSplits operations to use the parallel scan feature. For more information, see Parallel scan.