Perform nested queries by using Tablestore SDK for Python - Tablestore

You can perform a nested query to query the data in the child rows of nested columns. Nested fields cannot be directly queried. To query a nested field, you must specify the path of the nested field and a subquery in a NestedQuery object. The subquery can be a query of any type.

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
path	The path of the nested column. The path is similar to the tree structure. For example, news.title indicates the title subcolumn in the news column of the nested type.
query	The query on the subcolumn in the nested column. The value of this parameter can be of any query type.
score_mode	Specifies which value is used to calculate the score when a column contains multiple values.
table_name	The name of the data table.
index_name	The name of the search index.

Examples

The following examples show how to query the rows in which the value of the col_nested.col_long column is greater than or equal to 100 and smaller than or equal to 300.

Perform a nested query by using Tablestore SDK for Python V5.2.1 or later

If you use Tablestore SDK for Python V5.2.1 or later to perform a nested query, a SearchResponse object is returned by default. The following code provides a sample request:

nested_query = RangeQuery('col_nested.col_long', range_from=100, range_to=300, include_lower=True, include_upper=True)
query = NestedQuery('col_nested', nested_query)
search_response = client.search(
    '<TABLE_NAME>', '<SEARCH_INDEX_NAME>', 
    SearchQuery(query, 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:

nested_query = RangeQuery('col_nested.col_long', range_from=100, range_to=300, include_lower=True, include_upper=True)
query = NestedQuery('col_nested', nested_query)
rows, next_token, total_count, is_all_succeed, agg_result, group_by_results = client.search(
    '<TABLE_NAME>', '<SEARCH_INDEX_NAME>', 
    SearchQuery(query, limit=100, get_total_count=True), 
    ColumnsToGet(return_type=ColumnReturnType.ALL)
).v1_response()

Perform a nested query by using Tablestore SDK for Python of a version 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 nested query, results of the TUPLE type are returned by default. The following code provides a sample request:

nested_query = RangeQuery('col_nested.col_long', range_from=100, range_to=300, include_lower=True, include_upper=True)
query = NestedQuery('col_nested', nested_query)
rows, next_token, total_count, is_all_succeed = client.search(
    '<TABLE_NAME>', '<SEARCH_INDEX_NAME>', 
    SearchQuery(query, 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.