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.
Use the console
Go to the Index Management tab.
Log on to the Table Store console.
In the top navigation bar, select a resource group and a region.
On the Overview page, click the instance name or click Instance Management in the Actions column.
On the Instance Details tab, in the Data Table List tab, click the data table name or click Index Management in the Actions column.
On the Index Management tab, find the target Search Index and click Search in the Actions column.
-
You can query the data in the Query Data dialog box.
By default, all columns are returned. To return specific columns, turn off Retrieve All Columns and enter the column names, separated by commas.
Note By default, Table Store returns the primary key columns of the data table.
Select a logical operator: And, Or, or Not.
If you select And, the query returns data that meets all specified conditions. If you select Or, the query returns data that meets at least one of the specified conditions. If you select Not, the query returns data that does not meet the specified conditions.
-
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, sorting is disabled. To sort the results by a specific field, turn on Enable Sorting, add the sort field, and configure the sort order.
By default, aggregation is disabled. To perform statistical aggregation on a specific field, turn on Enable Aggregation, add the field for aggregation, and configure the aggregation settings.
Click OK.
The query results are displayed on the Index Management 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());
}
References
Search Index supports various query types for multi-dimensional data queries, including term query, terms query, match all query, match query, phrase match query, range query, prefix query, suffix query, wildcard query, token-based wildcard query, boolean query, geo query, nested query, vector search, and exists query.
When you query data, you can sort and paginate the result set or perform collapsing (deduplication).
For data analysis, such as finding the maximum or minimum value, calculating a sum, or counting rows, you can use the statistical aggregation or SQL query features.
To quickly export data regardless of the result set order, you can use the Parallel Scan feature.