Perform a Boolean query by using Tablestore SDK for Python - Tablestore

A Boolean query retrieves data in the data table based on a combination of subqueries. Tablestore returns the rows that match the subqueries. A subquery can be of any type, including Boolean query.

Prerequisites

A TableStoreClient instance is initialized. For more information, see Initialize an OTSClient instance.
A data table is created and data is written to the data table. For more information, see Create a data table and Write data.
A search index is created for the data table. For more information, see Create a search index.

Parameters

Parameter	Description
must_queries	The list of subqueries. Only rows that meet all subquery conditions are returned. This parameter is equivalent to the AND operator.
must_not_queries	The list of subqueries. Only rows that do not meet any subquery conditions are returned. This parameter is equivalent to the NOT operator.
filter_queries	The list of subqueries. Only rows that match all subfilters are returned. A filter is similar to a query except that a filter does not calculate the relevance score based on the number of subfilters that the row matches.
should_queries	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 should_queries are returned. A higher overall relevance score indicates that more subquery conditions specified by the should_queries parameter are met.
minimum_should_match	The minimum number of subquery conditions specified by the should_queries parameter that the returned rows must meet. If only the should_queries parameter is used to specify subquery conditions, the default value of the minimum_should_match parameter is 1. If one or more of the must_queries, must_not_queries, and filter_queries parameters are also used to specify subquery conditions, the default value of the minimum_should_match parameter is 0.
table_name	The name of the data table.
index_name	The name of the search index.

Examples

The following sample code provides examples on how to perform Boolean queries.

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, a SearchResponse object is returned. The following code provides a sample request:

# Col_Keyword > 'key100' and (Col_Long > 110 and Col_Long < 200) and not (Col_Keyword = 'key121') and
# should_queries(Col_Keyword > 'key120' or Col_Long < 300, minimum_should_match = 2)
bool_query = BoolQuery(
    must_queries=[
        RangeQuery('Col_Keyword', range_from='key100', include_lower=False),
        # Use BoolQuery. 
        BoolQuery(
            # Set subqueries to obtain data that meets all filtering conditions. 
            must_queries=[
                RangeQuery('Col_Long', range_from=110, include_lower=False),
                RangeQuery('Col_Long', range_to=200, include_upper=False)
            ],
        )
    ],
    # Set subqueries to exclude data that meets all filtering conditions. 
    must_not_queries=[
        TermQuery('Col_Keyword', 'key121')
    ],
    should_queries=[
        RangeQuery('Col_Keyword', range_from='key120', include_lower=False),
        RangeQuery('Col_Long', 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>', '<SEARCH_INDEX_NAME>',
    SearchQuery(
        bool_query,
        sort=Sort(sorters=[FieldSort('Col_Long', SortOrder.ASC)]),
        limit=100,
        get_total_count=True),
    ColumnsToGet(return_type=ColumnReturnType.ALL)
)
print('request_id : %s' % search_response.request_id)
print('is_all_succeed : %s' % search_response.is_all_succeed)
print('total_count : %s' % search_response.total_count)
print('rows : %s' % search_response.rows)

You can use the following sample request to return results of the Tuple type:

# Col_Keyword > 'key100' and (Col_Long > 110 and Col_Long < 200) and not (Col_Keyword = 'key121') and 
# should_queries(Col_Keyword > 'key120' or Col_Long < 300, minimum_should_match = 2) 
bool_query = BoolQuery(
    must_queries=[
        RangeQuery('Col_Keyword', range_from='key100', include_lower=False),
        # Use BoolQuery. 
        BoolQuery(
            # Set subqueries to obtain data that meets all filtering conditions. 
            must_queries=[
                RangeQuery('Col_Long', range_from=110, include_lower=False),
                RangeQuery('Col_Long', range_to=200, include_upper=False)
            ],
        )
    ],
    # Set subqueries to exclude data that meets all filtering conditions. 
    must_not_queries=[
        TermQuery('Col_Keyword', 'key121')
    ],
    should_queries=[
        RangeQuery('Col_Keyword', range_from='key120', include_lower=False),
        RangeQuery('Col_Long', 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>', '<SEARCH_INDEX_NAME>',
    SearchQuery(
        bool_query,
        sort=Sort(sorters=[FieldSort('Col_Long', SortOrder.ASC)]),
        limit=100,
        get_total_count=True),
    ColumnsToGet(return_type=ColumnReturnType.ALL)
).v1_response()

Tablestore SDK for Python earlier than 5.2.1

If you use a version of Tablestore SDK for Python that is earlier than 5.2.1 to perform a Boolean query, results of the Tuple type are returned by default. The following sample code provides a sample request:

# Col_Keyword > 'key100' and (Col_Long > 110 and Col_Long < 200) and not (Col_Keyword = 'key121') and 
# should_queries(Col_Keyword > 'key120' or Col_Long < 300, minimum_should_match = 2) 
bool_query = BoolQuery(
    must_queries=[
        RangeQuery('Col_Keyword', range_from='key100', include_lower=False),
        # Use BoolQuery. 
        BoolQuery(
            # Set subqueries to obtain data that meets all filtering conditions. 
            must_queries=[
                RangeQuery('Col_Long', range_from=110, include_lower=False),
                RangeQuery('Col_Long', range_to=200, include_upper=False)
            ],
        )
    ],
    # Set subqueries to exclude data that meets all filtering conditions. 
    must_not_queries=[
        TermQuery('Col_Keyword', 'key121')
    ],
    should_queries=[
        RangeQuery('Col_Keyword', range_from='key120', include_lower=False),
        RangeQuery('Col_Long', 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>', '<SEARCH_INDEX_NAME>',
    SearchQuery(
        bool_query,
        sort=Sort(sorters=[FieldSort('Col_Long', SortOrder.ASC)]),
        limit=100,
        get_total_count=True),
    ColumnsToGet(return_type=ColumnReturnType.ALL)
)

FAQ

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, geo query, Boolean query, KNN vector query, nested query, and exists query. You can use the query methods provided by the search index to query data from multiple dimensions based on your business requirements.
You can sort or paginate rows that meet the query conditions by using the sorting and paging features. For more information, see 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.