Tablestore:Post-query filter

Feature architecture

The post-query filter is based on a multilayer query architecture:

• SearchRequest: The top-level container for a query request. It includes the table name, index name, and specific query configurations.

• SearchQuery: The core query configuration. It includes the main query condition (Query) and an optional query filter (SearchFilter).

• Query: The main query condition. It supports all query types and data types of Search Index and is used for initial data retrieval.

• SearchFilter: The secondary filter. It contains filter conditions for fine-grained filtering on the main query results.

Limits

• Post-query filters must be used with Search Index query conditions. The supported query types are TermQuery, TermsQuery, RangeQuery, ExistsQuery, and BoolQuery, which combines these query types.

• When using a BoolQuery, only `mustQueries`, `mustNotQueries`, and `shouldQueries` clauses are supported. The `filterQueries` clause is not supported.

• Filtering is supported only for Keyword (non-tokenized string), Long, and Double field types. The `enableSortAndAgg` property must be enabled for these fields.

• Post-query filters do not support setting weights.

Prerequisites

• Initialize the Tablestore client.

• Create a search index on the data table.

Method description

public SearchResponse search(SearchRequest searchRequest) throws TableStoreException, ClientException

Sample code

The following example shows how to use the post-query filter feature. The main query condition first matches data where the `col_keyword` field is `value`. Then, the filter condition selects records where the `col_long` field value is between 1 and 10.

• Imperative API call

  private static void queryUsingSetter(SyncClient client) {
      // [Required] Replace with your table name.
      String tableName = "<TABLE_NAME>";
      // [Required] Replace with your search index name.
      String indexName = "<SEARCH_INDEX_NAME>";
  
      // Build the main query: an exact match using terms query.
      TermsQuery termsQuery = new TermsQuery();
      termsQuery.setFieldName("col_keyword");
      termsQuery.addTerm(ColumnValue.fromString("value"));
  
      // Build the filter condition: the value of the col_long field is in the range (1, 10).
      RangeQuery rangeQuery = new RangeQuery();
      rangeQuery.setFieldName("col_long");
      rangeQuery.setFrom(ColumnValue.fromLong(1));
      rangeQuery.setTo(ColumnValue.fromLong(10));
  
      // Assemble the SearchFilter.
      SearchFilter searchFilter = new SearchFilter();
      searchFilter.setQuery(rangeQuery);
  
      // Combine into a complete SearchQuery.
      SearchQuery searchQuery = new SearchQuery();
      searchQuery.setQuery(termsQuery);
      searchQuery.setFilter(searchFilter);
  
      // Construct the request.
      SearchRequest searchRequest = new SearchRequest(tableName, indexName, searchQuery);
      
      // By default, only primary key columns are returned. Set the columns to return as needed.
      SearchRequest.ColumnsToGet columnsToGet = new SearchRequest.ColumnsToGet();
      // Set to return all columns from the search index.
      columnsToGet.setReturnAllFromIndex(true); 
      searchRequest.setColumnsToGet(columnsToGet);
      
      try {
          SearchResponse resp = client.search(searchRequest);
          System.out.println("Rows: " + resp.getRows());     
      } catch (Exception e) {
          System.err.println("Search failed: " + e.getMessage());
      }
  }

  • You can configure the query to return specific columns or all columns.

    SearchRequest.ColumnsToGet columnsToGet = new SearchRequest.ColumnsToGet();
    // Return specific columns.
    columnsToGet.setColumns(Arrays.asList("col_long", "col_keyword")); // Specify columns.
    // Or: return all columns.
    // columnsToGet.setReturnAll(true);  
    searchRequest.setColumnsToGet(columnsToGet);

  • To count the total number of matched rows, you can enable the `totalCount` feature and retrieve the count from the response.

     // Enable totalCount statistics in searchQuery.
     searchQuery.setTrackTotalCount(SearchQuery.TRACK_TOTAL_COUNT); 
     // Print the total number of rows from the result.
     System.out.println("Total Count (matched): " + resp.getTotalCount());

• Builder pattern API call

  private static void queryUsingBuilder(SyncClient client) {
      // [Required] Replace with your table name.
      String tableName = "<TABLE_NAME>";
      // [Required] Replace with your search index name.
      String indexName = "<SEARCH_INDEX_NAME>";
      
      try {
          // Build SearchQuery: main query and filter condition.
          SearchQuery searchQuery = SearchQuery.newBuilder()
                  .query(QueryBuilders.terms("col_keyword").terms("value")) // Exact match for the keyword.
                  .filter(SearchFilter.newBuilder()
                          .query(QueryBuilders.range("col_long")
                                  .greaterThan(1)   // Range (1, 10).
                                  .lessThan(10))
                          .build())
                  // .trackTotalCount(SearchQuery.TRACK_TOTAL_COUNT)  // Enable total match count statistics as needed.
                  .build();
  
          // Construct the request.
          SearchRequest searchRequest = new SearchRequest(tableName, indexName, searchQuery);
      
          // By default, only primary key columns are returned. Set the columns to return as needed.
          SearchRequest.ColumnsToGet columnsToGet = new SearchRequest.ColumnsToGet();
          // Set to return all columns from the search index.
          columnsToGet.setReturnAllFromIndex(true); 
          searchRequest.setColumnsToGet(columnsToGet);
  
          SearchResponse resp = client.search(searchRequest);
          System.out.println("Rows: " + resp.getRows());
  
      } catch (Exception e) {
          System.err.println("Search request failed: " + e.getMessage());
      }
  }