Use post-query filters - Tablestore SDK for Java - Tablestore

Prerequisites

Install the Tablestore SDK for Java and initialize the client.
Use Tablestore SDK for Java 5.17.5 or later.

How it works

A post-query filter (SearchFilter) further filters the candidate rows after the main query (query) runs. Aggregations and group-bys are then calculated from the filtered results. Separating conditions that are suitable for later execution from the main query can reduce the cost of evaluating complex conditions during the main query. The actual performance benefit depends on the selectivity of the main query and the filter condition.

To use a post-query filter, call search and configure the main query and SearchFilter separately in SearchQuery. The main query supports the query types provided by search indexes. The post-query filter supports only specific query types and field types. For more information, see Limits.

SearchResponse search(SearchRequest request)

The following example first uses TermsQuery to query rows in which the value of the category field is book. The example then uses a post-query filter to retain rows in which the value of the score_long field is greater than 1 and less than 10.

String tableName = "example_table";
String indexName = "example_index";

// Configure the main query.
TermsQuery termsQuery = new TermsQuery();
termsQuery.setFieldName("category");
termsQuery.addTerm(ColumnValue.fromString("book"));

// Configure the post-query filter.
RangeQuery rangeQuery = new RangeQuery();
rangeQuery.setFieldName("score_long");
rangeQuery.setFrom(ColumnValue.fromLong(1));
rangeQuery.setIncludeLower(false);
rangeQuery.setTo(ColumnValue.fromLong(10));
rangeQuery.setIncludeUpper(false);

SearchFilter searchFilter = new SearchFilter();
searchFilter.setQuery(rangeQuery);

SearchQuery searchQuery = new SearchQuery();
searchQuery.setQuery(termsQuery);
searchQuery.setFilter(searchFilter);
searchQuery.setTrackTotalCount(SearchQuery.TRACK_TOTAL_COUNT);

SearchRequest request = new SearchRequest(tableName, indexName, searchQuery);
SearchRequest.ColumnsToGet columnsToGet =
        new SearchRequest.ColumnsToGet();
columnsToGet.setReturnAllFromIndex(true);
request.setColumnsToGet(columnsToGet);

SearchResponse response = client.search(request);
System.out.println("Total count: " + response.getTotalCount());
System.out.println("Rows: " + response.getRows());

Limits

A post-query filter must be used together with a main query. Configure the main query to reduce the number of candidate rows before the post-query filter runs. Do not set the main query to MatchAllQuery unless you need to filter all rows in the search index.
A post-query filter supports only TermQuery, TermsQuery, RangeQuery, ExistsQuery, and BoolQuery conditions composed of these query types.
In a BoolQuery used as a post-query filter, only mustQueries, mustNotQueries, and shouldQueries are supported. filterQueries is not supported.
Only Keyword, Long, and Double fields can be filtered. enableSortAndAgg must be enabled for the fields.
Post-query filter conditions do not participate in relevance scoring. Setting weight for a filter condition does not change the relevance scores of the query results.

Parameters

Search request

request is a SearchRequest object that contains the following parameters.

Name	Type	Description
tableName (required)	String	The name of the table.
indexName (required)	String	The name of the search index.
searchQuery (required)	SearchQuery	The main query, post-query filter, and common query settings.
columnsToGet (optional)	SearchRequest.ColumnsToGet	The columns to return. If this parameter is not specified, only primary key columns are returned.
timeoutInMillisecond (optional)	int	The request-level query timeout in milliseconds. The default value is `-1`, which specifies that no request-level timeout is set.
routingValues (optional)	`List<PrimaryKey>`	The primary key values of a custom routing field. You do not need to configure this parameter if custom routing is not used.

Query settings

request.searchQuery is a SearchQuery object that contains the following parameters.

Name	Type	Description
query (required)	Query	The main query condition. Search index query types are supported.
filter (required)	SearchFilter	The post-query filter that further filters candidate rows after the main query runs.
offset (optional)	Integer	The offset from which the query starts.
limit (optional)	Integer	The maximum number of rows to return. If you set this parameter to `0`, no rows are returned.
highlight (optional)	Highlight	The summary and highlighting settings for `Text` fields. For configuration details, see Summary and highlighting.
collapse (optional)	Collapse	The field collapsing settings used to deduplicate query results by a field. For configuration details, see Collapse query results.
sort (optional)	Sort	The sort order of the query results. For configuration details, see Sort and paginate results.
trackTotalCount (optional)	int	The maximum number of matching rows to count. The default value is `TRACK_TOTAL_COUNT_DISABLED`, which specifies that matching rows are not counted. Set this parameter to `TRACK_TOTAL_COUNT` to count all matching rows. A smaller value provides better query performance.
aggregationList (optional)	`List<Aggregation>`	The aggregation settings. Aggregations are calculated from the post-query filter results. For configuration details, see Aggregation.
groupByList (optional)	`List<GroupBy>`	The group-by settings. Group-bys are calculated from the post-query filter results. For configuration details, see Aggregation.
token (optional)	byte[]	The pagination token. To read the next page, set this parameter to the `nextToken` value returned by the previous request. When `token` is set, the SDK clears `sort` because the pagination token already contains the sort condition.

Post-query filter

request.searchQuery.filter is a SearchFilter object that contains the following parameter.

Name	Type	Description
query (required)	Query	The filter condition. `TermQuery`, `TermsQuery`, `RangeQuery`, `ExistsQuery`, and `BoolQuery` conditions composed of these query types are supported.

Returned columns

request.columnsToGet is a SearchRequest.ColumnsToGet object that contains the following parameters.

Name	Type	Description
columns (optional)	`List<String>`	The attribute columns to return. Configure this parameter only if `returnAll` and `returnAllFromIndex` are both `false`. If this parameter is not specified, only primary key columns are returned.
returnAll (optional)	boolean	Specifies whether to return all attribute columns in the table. Default value: `false`.
returnAllFromIndex (optional)	boolean	Specifies whether to return all indexed attribute columns. Default value: `false`. This parameter and `returnAll` cannot both be set to `true`.

Response

search returns a SearchResponse object. The following table describes the core fields.

Name	Type	Description
totalCount	long	The number of rows that match the post-query filter, obtained by using `getTotalCount()`. The value depends on the `trackTotalCount` setting.
rows	`List<Row>`	The rows returned by the current request, obtained by using `getRows()`. The number of rows does not exceed `limit`.
searchHits	`List<SearchHit>`	The search hits, obtained by using `getSearchHits()`.
aggregationResults	AggregationResults	The aggregation results calculated from the post-query filter results, obtained by using `getAggregationResults()`.
groupByResults	GroupByResults	The group-by results calculated from the post-query filter results, obtained by using `getGroupByResults()`.
nextToken	byte[]	The token for the next page, obtained by using `getNextToken()`. If the value is not `null`, use the value as `token` in the next request.
isAllSuccess	boolean	Indicates whether all index partitions were queried, obtained by using `isAllSuccess()`. If the value is `false`, partial results are returned and `totalCount` may be less than the actual number of matching rows.