Parameters
Parameter | Description |
mustQueries | The list of subqueries. Only rows that meet all subquery conditions are returned. This parameter is equivalent to the AND operator. |
mustNotQueries | The list of subqueries. Only rows that do not meet any subquery conditions are returned. This parameter is equivalent to the NOT operator. |
filterQueries | The list of subqueries. Only rows that meet all subfilters are returned. A filter is similar to a query except that no relevance score is calculated in a filter based on the number of subfilters that a row meets. |
shouldQueries | The list of subqueries that the query results can or cannot match. This parameter is equivalent to the OR operator. Only rows that meet the minimum number of subquery conditions specified by the shouldQueries parameter are returned. A higher overall relevance score indicates that more subquery conditions specified by the shouldQueries parameter are met. |
minimumShouldMatch | The minimum number of subquery conditions specified by the shouldQueries parameter that the returned rows must meet. If no other subquery conditions except the subquery conditions that are specified by the shouldQueries parameter are specified, the default value of the minimumShouldMatch parameter is 1. If other subquery conditions, such as subquery conditions specified by the mustQueries, mustNotQueries, and filterQueries parameters are specified, the default value of the minimumShouldMatch parameter is 0. |
getTotalCount | Specifies whether to return the total number of rows that meet the query conditions. The default value of this parameter is false, which specifies that the total number of rows that meet the query conditions is not returned. If you set this parameter to true, the query performance is compromised. |
tableName | The name of the data table. |
indexName | The name of the search index. |
columnsToGet | Specifies whether to return all columns of each row that meets the query conditions. You can configure the returnAll and columns fields for this parameter. The default value of the returnAll field is false, which specifies that not all columns are returned. In this case, you can use the columns field to specify the columns that you want to return. If you do not specify the columns that you want to return, only the primary key columns are returned. If you set the returnAll field to true, all columns are returned. |
Methods
You can use the Tablestore console, Tablestore CLI, or Tablestore SDKs to perform a Boolean query. Before you perform a Boolean query, make sure that the following preparations are made:
You have an Alibaba Cloud account or a RAM user that has permissions to perform operations on Tablestore. For information about how to grant Tablestore operation permissions to a RAM user, see Use a RAM policy to grant permissions to a RAM user.
If you want to use Tablestore SDKs or the Tablestore CLI to perform a query, an AccessKey pair is created for your Alibaba Cloud account or RAM user. For more information, see Create an AccessKey pair.
A data table is created. For more information, see Operations on tables.
A search index is created for the data table. For more information, see Create a search index.
If you want to use Tablestore SDKs to perform a query, an OTSClient instance is initialized. For more information, see Initialize an OTSClient instance.
If you want to use the Tablestore CLI to perform a query, the Tablestore CLI is downloaded and started, and information about the instance that you want to access and the data table are configured. For more information, see Download the Tablestore CLI, Start the Tablestore CLI and configure access information, and Operations on data tables.
Use the Tablestore console
Go to the Indexes tab.
Log on to the Tablestore console.
In the top navigation bar, select a resource group and a region.
On the Overview page, click the name of the instance that you want to manage or click Manage Instance in the Actions column of the instance.
On the Tables tab of the Instance Details tab, click the name of the data table or click Indexes in the Actions column of the data table.
On the Indexes tab, find the search index that you want to use to query data and click Manage Data in the Actions column.
In the Search dialog box, specify query conditions.
By default, the system returns all attribute columns. To return specific attribute columns, turn off All Columns and specify the attribute columns that you want to return. Separate multiple attribute columns with commas (,).
Note By default, the system returns all primary key columns of the data table.
Select the And, Or, or Not logical operator based on your business requirements.
If you select the And logical operator, data that meets the query conditions is returned. If you select the Or operator and specify a single query condition, data that meets the query condition is returned. If you select the Or logical operator and specify multiple query conditions, data that meets one of the query conditions is returned. If you select the Not logical operator, data that does not meet the query conditions is returned.
Select an index field and click Add. Then, configure the Query Type and Value parameters.
You can repeat this step to add query conditions for multiple index fields.
By default, the sorting feature is disabled. If you want to sort the query results based on specific fields, turn on Sort and specify the fields based on which you want to sort the query results and the sorting order.
By default, the aggregation feature is disabled. If you want to collect statistics on a specific field, turn on Collect Statistics, specify the field based on which you want to collect statistics, and then configure the information that is required to collect statistics.
Click OK.
Data that meets the query conditions is displayed in the specified order on the Indexes tab.
Use the Tablestore CLI
You can use the Tablestore CLI to run the search command to query data by using search indexes. For more information, see Search index.
Run the search command to use the search_index search index to query data and return all indexed columns of each row that meets the query conditions.
search -n search_index --return_all_indexed
Enter the query conditions as prompted:
{
"Offset": -1,
"Limit": 10,
"Collapse": null,
"Sort": null,
"GetTotalCount": true,
"Token": null,
"Query": {
"Name": "BoolQuery",
"Query": {
"MinimumShouldMatch": null,
"MustQueries": null,
"MustNotQueries": null,
"FilterQueries": null,
"ShouldQueries": [{
"Name": "RangeQuery",
"Query": {
"FieldName": "gid",
"From": null,
"To": 10,
"IncludeLower": false,
"IncludeUpper": false
}
}, {
"Name": "TermQuery",
"Query": {
"FieldName": "gid",
"Term": 77
}
}]
}
},
"Aggregations": [{
"Name": "avg",
"Aggregation": {
"AggName": "agg1",
"Field": "gid",
"MissingValue": null
}
}]
}
Use Tablestore SDKs
You can use the following Tablestore SDKs to perform a Boolean query: Tablestore SDK for Java, Tablestore SDK for Go, Tablestore SDK for Python, Tablestore SDK for Node.js, Tablestore SDK for .NET, and Tablestore SDK for PHP. In this example, Tablestore SDK for Java is used.
The following examples show how to perform Boolean queries.
Query rows that meet all subquery conditions
When you query rows that meet all subquery conditions, you can determine whether to calculate the relevance scores for the rows based on your business requirements.
Calculate the relevance scores for rows
The following sample code provides an example on how to perform a Boolean query to query the rows that meet all subquery conditions and calculate the relevance scores for the rows.
/**
* Perform a Boolean query to query the rows that meet all subquery conditions.
* @param client
*/
public static void andQuery(SyncClient client){
/**
* Condition 1: Perform a range query to query the rows in which the value of the Col_Long column is greater than 3.
*/
RangeQuery rangeQuery = new RangeQuery();
rangeQuery.setFieldName("Col_Long");
rangeQuery.greaterThan(ColumnValue.fromLong(3));
/**
* Condition 2: Perform a match query to query the rows in which the value of the Col_Keyword column is hangzhou.
*/
MatchQuery matchQuery = new MatchQuery();
matchQuery.setFieldName("Col_Keyword");
matchQuery.setText("hangzhou");
SearchQuery searchQuery = new SearchQuery();
{
/**
* Construct a Boolean query whose query results meet both Condition 1 and Condition 2.
*/
BoolQuery boolQuery = new BoolQuery();
boolQuery.setMustQueries(Arrays.asList(rangeQuery, matchQuery));
searchQuery.setQuery(boolQuery);
//searchQuery.setGetTotalCount(true);// Set the GetTotalCount parameter to true to return the total number of rows that meet the query conditions.
SearchRequest searchRequest = new SearchRequest("<TABLE_NAME>", "<SEARCH_INDEX_NAME>", searchQuery);
// You can configure the columnsToGet 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.
//SearchRequest.ColumnsToGet columnsToGet = new SearchRequest.ColumnsToGet();
//columnsToGet.setReturnAll(true); // Set the ReturnAll parameter to true to return all columns.
//columnsToGet.setColumns(Arrays.asList("ColName1","ColName2")); // Specify the columns that you want to return.
//searchRequest.setColumnsToGet(columnsToGet);
SearchResponse resp = client.search(searchRequest);
//System.out.println("TotalCount: " + resp.getTotalCount()); // Specify that the total number of rows that meet the query conditions instead of the number of returned rows is displayed.
System.out.println("Row: " + resp.getRows());
}
}
Do not calculate the relevance scores for rows
The following sample code provides an example on how to perform a Boolean query to query the rows that meet all subquery conditions without calculating the relevance scores for the rows.
/**
* Perform a Boolean query to query the rows that meet all subquery conditions without calculating the relevance scores for the rows.
* @param client
*/
public static void filtersQuery(SyncClient client){
/**
* Condition 1: Perform a range query to query the rows in which the value of the Col_Long column is greater than 3.
*/
RangeQuery rangeQuery = new RangeQuery();
rangeQuery.setFieldName("Col_Long");
rangeQuery.greaterThan(ColumnValue.fromLong(3));
/**
* Condition 2: Perform a match query to query the rows in which the value of the Col_Keyword column is hangzhou.
*/
MatchQuery matchQuery = new MatchQuery();
matchQuery.setFieldName("Col_Keyword");
matchQuery.setText("hangzhou");
SearchQuery searchQuery = new SearchQuery();
{
/**
* Construct a Boolean query whose query results meet both Condition 1 and Condition 2.
*/
BoolQuery boolQuery = new BoolQuery();
boolQuery.setFilterQueries(Arrays.asList(rangeQuery, matchQuery));
searchQuery.setQuery(boolQuery);
//searchQuery.setGetTotalCount(true);// Set the GetTotalCount parameter to true to return the total number of rows that meet the query conditions.
SearchRequest searchRequest = new SearchRequest("sampleTable", "sampleSearchIndex", searchQuery);
// You can configure the columnsToGet 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.
//SearchRequest.ColumnsToGet columnsToGet = new SearchRequest.ColumnsToGet();
//columnsToGet.setReturnAll(true); // Set the ReturnAll parameter to true to return all columns.
//columnsToGet.setColumns(Arrays.asList("ColName1","ColName2")); // Specify the columns that you want to return.
//searchRequest.setColumnsToGet(columnsToGet);
SearchResponse resp = client.search(searchRequest);
//System.out.println("TotalCount: " + resp.getTotalCount()); // Specify that the total number of rows that meet the query conditions instead of the number of returned rows is displayed.
System.out.println("Row: " + resp.getRows());
}
}
Query rows that meet at least one of the subquery conditions
The following sample code provides an example on how to perform a Boolean query to query the rows that meet at least one of the specified subquery conditions:
/**
* Perform a Boolean query to query the rows that meet at least one of the specified subquery conditions.
* @param client
*/
public static void orQuery(SyncClient client) {
/**
* Condition 1: Perform a range query to query the rows in which the value of the Col_Long column is greater than 3.
*/
RangeQuery rangeQuery = new RangeQuery();
rangeQuery.setFieldName("Col_Long");
rangeQuery.greaterThan(ColumnValue.fromLong(3));
/**
* Condition 2: Perform a match query to query the rows in which the value of the Col_Keyword column is hangzhou.
*/
MatchQuery matchQuery = new MatchQuery();
matchQuery.setFieldName("Col_Keyword");
matchQuery.setText("hangzhou");
SearchQuery searchQuery = new SearchQuery();
{
/**
* Construct a Boolean query whose query results meet at least one of Condition 1 and Condition 2.
*/
BoolQuery boolQuery = new BoolQuery();
boolQuery.setShouldQueries(Arrays.asList(rangeQuery, matchQuery));
boolQuery.setMinimumShouldMatch(1); // Specify that the results meet at least one of the subquery conditions.
searchQuery.setQuery(boolQuery);
//searchQuery.setGetTotalCount(true);// Set the GetTotalCount parameter to true to return the total number of rows that meet the query conditions.
SearchRequest searchRequest = new SearchRequest("<TABLE_NAME>", "<SEARCH_INDEX_NAME>", searchQuery);
// You can configure the columnsToGet 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.
//SearchRequest.ColumnsToGet columnsToGet = new SearchRequest.ColumnsToGet();
//columnsToGet.setReturnAll(true); // Set the ReturnAll parameter to true to return all columns.
//columnsToGet.setColumns(Arrays.asList("ColName1","ColName2")); // Specify the columns that you want to return.
//searchRequest.setColumnsToGet(columnsToGet);
SearchResponse resp = client.search(searchRequest);
//System.out.println("TotalCount: " + resp.getTotalCount()); // Specify that the total number of rows that meet the query conditions instead of the number of returned rows is displayed.
System.out.println("Row: " + resp.getRows());
}
}
Query rows that do not meet any subquery conditions
The following sample code provides an example on how to perform a Boolean query to query the rows that do not meet any subquery conditions:
/**
* Perform a Boolean query to query the rows that do not meet any subquery conditions.
* @param client
*/
public static void notQuery(SyncClient client) {
/**
* Condition 1: Perform a match query to query the rows in which the value of the Col_Keyword column is hangzhou.
*/
MatchQuery matchQuery = new MatchQuery();
matchQuery.setFieldName("Col_Keyword");
matchQuery.setText("hangzhou");
SearchQuery searchQuery = new SearchQuery();
{
/**
* Construct a Boolean query whose query results do not meet Condition 1.
*/
BoolQuery boolQuery = new BoolQuery();
boolQuery.setMustNotQueries(Arrays.asList(matchQuery));
searchQuery.setQuery(boolQuery);
//searchQuery.setGetTotalCount(true);// Set the GetTotalCount parameter to true to return the total number of rows that meet the query conditions.
SearchRequest searchRequest = new SearchRequest("<TABLE_NAME>", "<SEARCH_INDEX_NAME>", searchQuery);
// You can configure the columnsToGet 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.
//SearchRequest.ColumnsToGet columnsToGet = new SearchRequest.ColumnsToGet();
//columnsToGet.setReturnAll(true); // Set the ReturnAll parameter to true to return all columns.
//columnsToGet.setColumns(Arrays.asList("ColName1","ColName2")); // Specify the columns that you want to return.
//searchRequest.setColumnsToGet(columnsToGet);
SearchResponse resp = client.search(searchRequest);
//System.out.println("TotalCount: " + resp.getTotalCount()); // Specify that the total number of rows that meet the query conditions instead of the number of returned rows is displayed.
System.out.println("Row: " + resp.getRows());
}
}
Query rows that meet a combination of subquery conditions
The following sample code provides an example on how to perform a Boolean query to query the rows that meet a combination of subquery conditions, which is (col2<4 or col3<5) or (col2 = 4 and (col3 = 5 or col3 =6)). In the preceding combination of subquery conditions, the Boolean subqueries are connected by the AND or OR operator.
/**
* (col2<4 or col3<5) or (col2 = 4 and (col3 = 5 or col3 =6))
* The Boolean subqueries are connected by the AND or OR operator.
* @param client
*/
private static void boolQuery2(SyncClient client){
// Condition 1: The value of the col2 column is less than 4.
RangeQuery rangeQuery1 = new RangeQuery();
rangeQuery1.setFieldName("col2");
rangeQuery1.lessThan(ColumnValue.fromLong(4));
// Condition 2: The value of the col3 column is less than 5.
RangeQuery rangeQuery2 = new RangeQuery();
rangeQuery2.setFieldName("col3");
rangeQuery2.lessThan(ColumnValue.fromLong(5));
// Condition 3: The value of the col2 column is equal to 4.
TermQuery termQuery = new TermQuery();
termQuery.setFieldName("col2");
termQuery.setTerm(ColumnValue.fromLong(4));
// Condition 4: The value of the col3 column is equal to 5 or the value of the col3 column is equal to 6.
TermsQuery termsQuery = new TermsQuery();
termsQuery.setFieldName("col3");
termsQuery.addTerm(ColumnValue.fromLong(5));
termsQuery.addTerm(ColumnValue.fromLong(6));
SearchQuery searchQuery = new SearchQuery();
List<Query> queryList1 = new ArrayList<>();
queryList1.add(rangeQuery1);
queryList1.add(rangeQuery2);
// Composite condition 1: The value of the col2 column is less than 4 or the value of the col3 column is less than 5.
BoolQuery boolQuery1 = new BoolQuery();
boolQuery1.setShouldQueries(queryList1);
// Composite condition 2: The value of the col2 column is equal to 4 and the value of the col3 column is equal to 5 or 6.
List<Query> queryList2 = new ArrayList<>();
queryList2.add(termQuery);
queryList2.add(termsQuery);
BoolQuery boolQuery2 = new BoolQuery();
boolQuery2.setMustQueries(queryList2);
// Composite conditions: The value of the col2 column is less than 4 or the value of the col3 column is less than 5, or the value of the col2 column is equal to 4 and the value of the col3 column is equal to 5 or 6.
List<Query> queryList3 = new ArrayList<>();
queryList3.add(boolQuery1);
queryList3.add(boolQuery2);
BoolQuery boolQuery = new BoolQuery();
boolQuery.setShouldQueries(queryList3);
searchQuery.setQuery(boolQuery);
//searchQuery.setGetTotalCount(true);// Set the GetTotalCount parameter to true to return the total number of rows that meet the query conditions.
SearchRequest searchRequest = new SearchRequest("<TABLE_NAME>", "<SEARCH_INDEX_NAME>", searchQuery);
// You can configure the columnsToGet 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.
//SearchRequest.ColumnsToGet columnsToGet = new SearchRequest.ColumnsToGet();
//columnsToGet.setReturnAll(true); // Set the ReturnAll parameter to true to return all columns.
//columnsToGet.setColumns(Arrays.asList("ColName1","ColName2")); // Specify the columns that you want to return.
//searchRequest.setColumnsToGet(columnsToGet);
SearchResponse response = client.search(searchRequest);
//System.out.println("TotalCount: " + resp.getTotalCount()); // Specify that the total number of rows that meet the query conditions instead of the number of returned rows is displayed.
System.out.println(response.getRows());
}
Billing rules
When you use a search index to query data, you are charged for the read throughput that is consumed. For more information, see Billable items of search indexes.
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, fuzzy query, Boolean query, geo query, nested query, KNN vector query, and exists query. You can select query methods based on your business requirements to query data from multiple dimensions.
You can sort or paginate rows that meet the query conditions by using the sorting and paging features. For more information, see Perform sorting and paging.
You can use the collapse (distinct) feature to collapse the result set based on a specific column. 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, you can use the aggregation feature of the Search operation or execute SQL statements. For example, you can obtain the minimum and maximum values, sum, and total number of rows. For more information, see Aggregation and SQL query.
If you want to 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.