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.

Prerequisites

  • The OTSClient is initialized. For more information, see Initialization.
  • A data table is created. Data is written to the table.
  • A search index is created for the data table. For more information, see Create search indexes.

Parameters

ParameterDescription
must_queriesThe list of subqueries that the query results must match. This parameter is equivalent to the AND operator.
must_not_queriesThe list of subqueries that the query results must not match. This parameter is equivalent to the NOT operator.
filter_queriesThe 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.
should_queriesThe 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 should_queries are returned.

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

minimum_should_matchThe minimum number of subquery conditions specified by should_queries that the rows must meet. If no other subquery conditions except the subquery conditions that are specified by should_queries are specified, the default value of the minimum_should_match parameter is 1. If other subquery conditions, such as subquery conditions specified by must_queries, must_not_queries, and filter_queries, are specified, the default value of the minimum_should_match parameter is 0.
table_nameThe name of the data table.
index_nameThe name of the search index.

Examples

  • Perform a Boolean query by using Tablestore SDK for Python V5.2.1 or later
    By default, if you use Tablestore SDK for Python V5.2.1 or later to perform a Boolean query, SearchResponse objects are returned. The following code provides a sample request:
    # k > 'key100' and (l > 110 and l < 200) and not (k = 'key121')
    # and should_queries(k > 'key120' or l < 300, minimum_should_match=2)
    bool_query = BoolQuery(
        must_queries=[
            RangeQuery('k', range_from='key100', include_lower=False),
            // Set the query type to BoolQuery. 
            BoolQuery(
                // Set subqueries that the rows must match. 
                must_queries=[
                    RangeQuery('l', range_from=110, include_lower=False),
                    RangeQuery('l', range_to=200, include_upper=False)
                ],
            )
        ],
        // Set subqueries that the rows must not match. 
        must_not_queries=[
            TermQuery('k', 'key121')
        ],
        should_queries=[
            RangeQuery('k', range_from='key120', include_lower=False),
            RangeQuery('l', range_to=300, include_upper=130)
        ],
        minimum_should_match=2
    )
    
    // Construct a Boolean query by specifying query parameters, including sort, limit, and get_total_count. 
    search_response = client.search(
        table_name, index_name, 
        SearchQuery(
            bool_query, 
            sort=Sort(sorters=[FieldSort('l', SortOrder.ASC)]), 
            limit=100, 
            get_total_count=True), 
        ColumnsToGet(return_type=ColumnReturnType.ALL)
    )
    You can use the following sample request to return results of the Tuple type:
    # k > 'key100' and (l > 110 and l < 200) and not (k = 'key121')
    # and should_queries(k > 'key120' or l < 300, minimum_should_match=2)
    bool_query = BoolQuery(
        must_queries=[
            RangeQuery('k', range_from='key100', include_lower=False),
            // Set the query type to BoolQuery. 
            BoolQuery(
                // Set subqueries that the rows must match. 
                must_queries=[
                    RangeQuery('l', range_from=110, include_lower=False),
                    RangeQuery('l', range_to=200, include_upper=False)
                ],
            )
        ],
        // Set subqueries that the rows must not match. 
        must_not_queries=[
            TermQuery('k', 'key121')
        ],
        should_queries=[
            RangeQuery('k', range_from='key120', include_lower=False),
            RangeQuery('l', range_to=300, include_upper=130)
        ],
        minimum_should_match=2
    )
    
    // Construct a Boolean query by specifying query parameters, including sort, limit, and get_total_count. 
    rows, next_token, total_count, is_all_succeed, agg_results, group_by_results = client.search(
        table_name, index_name, 
        SearchQuery(
            bool_query, 
            sort=Sort(sorters=[FieldSort('l', SortOrder.ASC)]), 
            limit=100, 
            get_total_count=True), 
        ColumnsToGet(return_type=ColumnReturnType.ALL)
    ).v1_response()
  • Perform a Boolean query by using Tablestore SDK for Python of a version earlier than V5.2.1

    By default, if you use Tablestore SDK for Python of a version earlier than V5.2.1 to perform a Boolean query, results of the Tuple type are returned. The following code provides a sample request:

    # k > 'key100' and (l > 110 and l < 200) and not (k = 'key121')
    # and should_queries(k > 'key120' or l < 300, minimum_should_match=2)
    bool_query = BoolQuery(
        must_queries=[
            RangeQuery('k', range_from='key100', include_lower=False),
            // Set the query type to BoolQuery. 
            BoolQuery(
                // Set subqueries that the rows must match. 
                must_queries=[
                    RangeQuery('l', range_from=110, include_lower=False),
                    RangeQuery('l', range_to=200, include_upper=False)
                ],
            )
        ],
        // Set subqueries that the rows must not match. 
        must_not_queries=[
            TermQuery('k', 'key121')
        ],
        should_queries=[
            RangeQuery('k', range_from='key120', include_lower=False),
            RangeQuery('l', range_to=300, include_upper=130)
        ],
        minimum_should_match=2
    )
    
    // Construct a Boolean query by specifying query parameters, including sort, limit, and get_total_count. 
    rows, next_token, total_count, is_all_succeed = client.search(
        table_name, index_name, 
        SearchQuery(
            bool_query, 
            sort=Sort(sorters=[FieldSort('l', SortOrder.ASC)]), 
            limit=100, 
            get_total_count=True), 
        ColumnsToGet(return_type=ColumnReturnType.ALL)
    )