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.

Limitations

• Post-query filter must be used in combination with a search index query. Supported query types include TermQuery, TermsQuery, RangeQuery, ExistsQuery, and BoolQuery.

• In a BoolQuery, only the mustQueries, mustNotQueries, and shouldQueries clauses are supported. The filterQueries clause is not supported.

• Only Keyword, Long, and Double fields with the enableSortAndAgg property enabled support filtering.

• Weight settings are not supported in post-query filters.

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 demonstrates the post-query filter feature. The main query matches data where the col_keyword field equals value. The filter then selects records where the col_long field value falls 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());
      }
  }

  • 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, 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());
      }
  }