Parameters
Parameter | Description |
mustQueries | A list of queries. A row of data must match all subqueries to be considered a match. This is equivalent to the AND operator. |
mustNotQueries | A list of queries. A row of data must not match any of the subqueries to be considered a match. This is equivalent to the NOT operator. |
filterQueries | A list of queries. A row of data must match all sub-filters to be considered a match. A filter is similar to a query, but it does not calculate a relevance score based on the number of matched filter queries. |
shouldQueries | A list of queries. These queries can be matched or not. This is equivalent to the OR operator. A row of data must match at least the minimum number of shouldQueries subqueries to be considered a match. The more shouldQueries subqueries that are matched, the higher the overall relevance score. |
minimumShouldMatch | The minimum number of shouldQueries subqueries that must be matched. The default value is 1 if only shouldQueries exists at the same level. The default value is 0 if other queries, such as mustQueries, mustNotQueries, and filterQueries, exist at the same level. |
getTotalCount | Specifies whether to return the total number of matched rows. The default value is false. Returning the total number of matched rows can affect query performance. |
tableName | The name of the data table. |
indexName | The name of the search index. |
columnsToGet | Specifies whether to return all columns. This includes the returnAll and columns settings. The default value of returnAll is false, which means not all columns are returned. In this case, you can use columns to specify the columns to return. If you do not specify columns to return, only the primary key columns are returned. If you set returnAll to true, all columns are returned. |
Usage
You can perform a Boolean query using the console, command line interface, or an SDK. Before you begin, complete the following steps.
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 a Tablestore client.
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 Manage data tables.
Use the 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.
You can query the data in the Query Data dialog box.
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, click Add, and then set the query type for the index field and enter a value.
If required, you can repeat this step to add query configurations 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 command line interface
Run the search command in the command line interface to query data from a search index. For more information, see Search index.
Run the search command to query data from the table using the search_index search index. This command returns all indexed columns.
search -n search_index --return_all_indexed
Enter the query conditions as prompted. The following is an example:
{
"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 an SDK
You can perform a Boolean query using the Java SDK, Go SDK, Python SDK, Node.js SDK, .NET SDK, or PHP SDK. This topic uses the Java SDK as an example.
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
useVCUmode (formerly reserved mode),usesearch CNYindex queries consumeVCUcompute resources. When you useCUmode (formerly pay-as-you-go mode),usesearch CNYindex queries consumeread throughput. For more search information, seesearch CNYindex billing.
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 (remove duplicates).
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.