This topic describes how to use Boolean query to query the rows based on a combination of subqueries. Tablestore returns the rows that match the subqueries. Each subquery can be of any type, including BoolQuery.

API operations

You can call the Search or ParallelScan operation and set the query type to BoolQuery to implement Boolean queries.

Use Tablestore SDKs

You can use the following Tablestore SDKs to implement Boolean queries:

Parameters

Parameter Description
mustQueries The list of subqueries that the query results must match. This parameter is equivalent to the AND operator.
mustNotQueries The list of subqueries that the query results must not match. This parameter is equivalent to the NOT operator.
filterQueries The list of subqueries. Only rows that match all subfilters are returned. filter is similar to query except that filter does not calculate the relevance score based on the number of subfilters that the row matches.
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 shouldQueries are returned.

A higher overall relevance score indicates that more subquery conditions specified by shouldQueries are met.

minimumShouldMatch The minimum number of subquery conditions specified by shouldQueries that the rows must meet. If no other subquery conditions except the subquery conditions that are specified by shouldQueries are specified, the default value of the minimumShouldMatch parameter is 1. If other subquery conditions, such as subquery conditions specified by mustQueries, mustNotQueries, and filterQueries are specified, the default value of the minimumShouldMatch parameter is 0.
getTotalCount Specifies whether to return the total number of rows that match the query conditions. By default, getTotalCount is false, which indicates that the total number of rows that match the query conditions is not returned.

If this parameter is set to true, 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 matched row. You can configure returnAll and columns for this parameter.

By default, returnAll is false, which indicates that not all columns are returned. In this case, you can use columns to specify the columns to return. If you do not specify the columns to return, only the primary key columns are returned.

If returnAll is set to true, all columns are returned.

Examples

  • Example 1:

    Perform a Boolean query to query the rows that match the AND-based conditions. 

    /**
 * Perform a Boolean query to query the rows that match the AND-based conditions. 
 * @param client
 */
public static void andQuery(SyncClient client){
    /**
     * Condition 1: Perform a range query to query the rows where the Col_Long column value 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 where the Col_Keyword column value matches hangzhou. 
     */
    MatchQuery matchQuery = new MatchQuery();
    matchQuery.setFieldName("Col_Keyword");
    matchQuery.setText("hangzhou");

    SearchQuery searchQuery = new SearchQuery();
    {
        /**
         * Construct a Boolean query where the 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 GetTotalCount to true to return the total number of matched rows. 

        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 ReturnAll to true to return all columns. 
        //columnsToGet.setColumns(Arrays.asList("ColName1","ColName2")); // Set Columns to return specified columns. 
        //searchRequest.setColumnsToGet(columnsToGet);

        SearchResponse resp = client.search(searchRequest);
        //System.out.println("TotalCount: " + resp.getTotalCount()); // Specify that all rows that match the specified conditions are displayed. The number of rows to return is not displayed. 
        System.out.println("Row: " + resp.getRows());
    }
}
  • Example 2

    Perform a Boolean query to query the rows that match the OR-based conditions. 

    /**
 * Perform a Boolean query to query the rows that match the OR-based conditions. 
 * @param client
 */
public static void orQuery(SyncClient client) {

    /**
     * Condition 1: Perform a range query to query the rows where the Col_Long column value 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 where the Col_Keyword column value matches hangzhou. 
     */
    MatchQuery matchQuery = new MatchQuery();
    matchQuery.setFieldName("Col_Keyword");
    matchQuery.setText("hangzhou");

    SearchQuery searchQuery = new SearchQuery();
    {
    /**
     * Construct a Boolean query where the 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 conditions. 
    searchQuery.setQuery(boolQuery);
    //searchQuery.setGetTotalCount(true);// Specify that the total number of matched rows is returned. 

    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 ReturnAll to true to return all columns. 
    //columnsToGet.setColumns(Arrays.asList("ColName1","ColName2")); // Set Columns to return specified columns. 
    //searchRequest.setColumnsToGet(columnsToGet);

    SearchResponse resp = client.search(searchRequest);
    //System.out.println("TotalCount: " + resp.getTotalCount()); // Specify that all rows that match the specified conditions are displayed. The number of rows to return is not displayed.
    System.out.println("Row: " + resp.getRows());
    }
}
  • Example 3

    Perform a Boolean query to query the rows that match the NOT-based conditions. 

    /**
 * Perform a Boolean query to query the rows that match the NOT-based conditions. 
 * @param client
 */
public static void notQuery(SyncClient client) {

    /**
     * Condition 1: Perform a match query to query the rows where the Col_Keyword column value matches hangzhou. 
     */
    MatchQuery matchQuery = new MatchQuery();
    matchQuery.setFieldName("Col_Keyword");
    matchQuery.setText("hangzhou");

    SearchQuery searchQuery = new SearchQuery();
    {
        /**
         * Construct a Boolean query where the query results do not meet Condition 1. 
         */
        BoolQuery boolQuery = new BoolQuery();
        boolQuery.setMustNotQueries(Arrays.asList(matchQuery));
        searchQuery.setQuery(boolQuery);
        //searchQuery.setGetTotalCount(true);// Specify that the total number of matched rows is returned. 

        SearchRequest searchRequest = new SearchRequest("sampleTable", "sampleSearchIndex", searchQuery);
        // You can set the columnsToGet parameter to specify the columns to return or specify that all columns are returned. If you do not set this parameter, only the primary key columns are returned. 
        //SearchRequest.ColumnsToGet columnsToGet = new SearchRequest.ColumnsToGet();
        //columnsToGet.setReturnAll(true); // Set ReturnAll to true to return all columns. 
        //columnsToGet.setColumns(Arrays.asList("ColName1","ColName2")); // Set Columns to return specified columns. 
        //searchRequest.setColumnsToGet(columnsToGet);

        SearchResponse resp = client.search(searchRequest);
        //System.out.println("TotalCount: " + resp.getTotalCount()); // Specify that all rows that match the specified conditions are displayed. The number of rows to return is not displayed. 
        System.out.println("Row: " + resp.getRows());
    }
}
  • Example 4

    The following sample code provides an example on how to perform a Boolean query that includes multiple subqueries of the BoolQuery type. In (col2 < 4 or col3 < 5) or (col2 = 4 and (col3 = 5 or col3 = 6)), each subquery of the BoolQuery type is connected by AND or OR. 

    /**
 * (col2<4 or col3<5) or (col2 = 4 and (col3 = 5 or col3 =6))
 * In the preceding example, each subquery of the BoolQuery type is connected by AND or OR. 
 * @param client
*/
private static void boolQuery2(SyncClient client){
        // Condition 1: col2 < 4 
        RangeQuery rangeQuery1 = new RangeQuery();
        rangeQuery1.setFieldName("col2");
        rangeQuery1.lessThan(ColumnValue.fromLong(4));

        // Condition 2: col3 < 5 
        RangeQuery rangeQuery2 = new RangeQuery();
        rangeQuery2.setFieldName("col3");
        rangeQuery2.lessThan(ColumnValue.fromLong(5));

        // Condition 3: col2 = 4 
        TermQuery termQuery = new TermQuery();
        termQuery.setFieldName("col2");
        termQuery.setTerm(ColumnValue.fromLong(4));

        // Condition 4: col3 = 5 or col3 = 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);

        // Combination 1: col2 < 4 or col3 < 5 
        BoolQuery boolQuery1 = new BoolQuery();
        boolQuery1.setShouldQueries(queryList1);

        // Combination 2: col2 = 4 and (col3 = 5 or col3 = 6) 
        List<Query> queryList2 = new ArrayList<>();
        queryList2.add(termQuery);
        queryList2.add(termsQuery);

        BoolQuery boolQuery2 = new BoolQuery();
        boolQuery2.setMustQueries(queryList2);

        // Final combination: (col2 < 4 or col3 < 5) or (col2 = 4 and (col3 = 5 or col3 = 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);// Specify that the total number of matched rows is returned. 

        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 ReturnAll to true to return all columns. 
        //columnsToGet.setColumns(Arrays.asList("ColName1","ColName2")); // Set Columns to return specified columns. 
        //searchRequest.setColumnsToGet(columnsToGet);

        SearchResponse response = client.search(searchRequest);
        //System.out.println("TotalCount: " + resp.getTotalCount()); // Specify that all rows that match the specified conditions are displayed. The number of rows to return is not displayed. 
        System.out.println(response.getRows());

    }