Range query - Tablestore SDK for Java - Tablestore - Alibaba Cloud Documentation Center

Prerequisites

Install the Tablestore SDK for Java and initialize a client.

Feature description

A range query matches rows whose indexed field values fall within a specified interval. You can set only a lower bound, only an upper bound, or both bounds. At least one bound is required. For a Text field, a row matches if any token generated from the field value falls within the interval.

Use greaterThan, greaterThanOrEqual, lessThan, and lessThanOrEqual to specify greater than, greater than or equal to, less than, and less than or equal to conditions. Set the query type to RangeQuery when you call search.

SearchResponse search(SearchRequest request)

The following example queries Long values in the price field that fall within the half-open interval [100, 500). The query returns up to 10 rows and the total number of matches.

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

RangeQuery rangeQuery = new RangeQuery();
rangeQuery.setFieldName("price");
rangeQuery.greaterThanOrEqual(ColumnValue.fromLong(100L));
rangeQuery.lessThan(ColumnValue.fromLong(500L));

SearchQuery searchQuery = new SearchQuery();
searchQuery.setQuery(rangeQuery);
searchQuery.setLimit(10);
searchQuery.setTrackTotalCount(SearchQuery.TRACK_TOTAL_COUNT);

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

SearchResponse response = client.search(request);
System.out.println(response.getTotalCount());
System.out.println(response.getRows());

Parameters

Search request

request is a SearchRequest object that contains the following parameters.

Name	Type	Description
tableName (required)	String	The name of the data table.
indexName (required)	String	The name of the search index.
searchQuery (required)	SearchQuery	The query condition and general query settings.
columnsToGet (optional)	SearchRequest.ColumnsToGet	The columns to return. If this parameter is not configured, only primary key columns are returned.
timeoutInMillisecond (optional)	int	The request-level query timeout in milliseconds. The default value is `-1`, which does not configure a separate query timeout.
routingValues (optional)	`List<PrimaryKey>`	The primary key values that correspond to custom routing fields. Leave this parameter unset if custom routing is not configured.

Query settings

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

Name	Type	Description
query (required)	Query	The query condition. Set this parameter to a `RangeQuery` object for a range query.
offset (optional)	Integer	The starting position of the query.
limit (optional)	Integer	The maximum number of rows to return. Set this parameter to `0` to return no rows.
highlight (optional)	Highlight	The summary and highlighting settings for `Text` fields. For configuration details, see Summary and highlighting.
collapse (optional)	Collapse	The field collapse settings, which deduplicate results by a specified field. For configuration details, see Collapse query results.
sort (optional)	Sort	The result sort order. For configuration details, see Sort and paginate results.
trackTotalCount (optional)	int	The expected maximum number of matching rows to count. The default value is `TRACK_TOTAL_COUNT_DISABLED`, which disables counting. Set this parameter to `TRACK_TOTAL_COUNT` to count all matching rows. A smaller value improves query performance.
filter (optional)	SearchFilter	A filter that is applied to the results of `query`.
aggregationList (optional)	`List<Aggregation>`	The aggregation settings. For configuration details, see Aggregation.
groupByList (optional)	`List<GroupBy>`	The grouping settings. For configuration details, see Aggregation.
token (optional)	byte[]	The pagination token. Set this parameter to the `nextToken` value from the previous response to continue reading rows. When you set `token`, the SDK clears `sort` because the token already contains the sort conditions.

Query condition

request.searchQuery.query is a RangeQuery object that contains the following parameters.

Name	Type	Description
fieldName (required)	String	The name of the indexed field to query. Range queries support `Long`, `Double`, `Boolean`, `Keyword`, `Text`, `Date`, and `IP` fields, and subfields of `JSON Object` fields. For a `Text` field, a row matches if any token falls within the interval.
from (optional)	ColumnValue	The lower bound. At least one of `from` and `to` is required. Call `greaterThan` or `greaterThanOrEqual` to set the lower bound and whether to include it.
to (optional)	ColumnValue	The upper bound. At least one of `from` and `to` is required. Call `lessThan` or `lessThanOrEqual` to set the upper bound and whether to include it.
includeLower (optional)	boolean	Specifies whether to include `from`. The default value is `false`. `greaterThan` sets this parameter to `false`, and `greaterThanOrEqual` sets it to `true`.
includeUpper (optional)	boolean	Specifies whether to include `to`. The default value is `false`. `lessThan` sets this parameter to `false`, and `lessThanOrEqual` sets it to `true`.

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. Set this parameter only if `returnAll` and `returnAllFromIndex` are both `false`. If you omit this parameter, only primary key columns are returned.
returnAll (optional)	boolean	Specifies whether to return all attribute columns from the data table. The default value is `false`.
returnAllFromIndex (optional)	boolean	Specifies whether to return all indexed attribute columns. The default value is `false`. Do not set both `returnAll` and `returnAllFromIndex` to `true`.

Return values

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

Name	Type	Description
totalCount	long	The number of matching rows. Call `getTotalCount()` to obtain the value. The returned value depends on the `trackTotalCount` setting.
rows	`List<Row>`	The rows returned by this query. Call `getRows()` to obtain the value. The number of rows does not exceed `limit`.
searchHits	`List<SearchHit>`	The query hits. Call `getSearchHits()` to obtain the value. If `highlight` is configured, this field contains the row data and summary and highlighting results.
nextToken	byte[]	The next-page token. Call `getNextToken()` to obtain the value. If the value is not `null`, set it as `token` in the next request to continue reading rows.
isAllSuccess	boolean	Indicates whether all index partitions were queried successfully. Call `isAllSuccess()` to obtain the value. If the value is `false`, the response contains partial results and `totalCount` may be less than the actual number of matching rows.

Scenario examples

Query dates in a custom format

If date_string is a String field in the data table and is mapped to a Date field in the search index with the yyyy-MM-dd HH:mm:ss format, use strings in the same format as query bounds. The following example queries the interval [2021-01-01 00:00:00, 2023-01-01 00:00:00).

RangeQuery rangeQuery = new RangeQuery();
rangeQuery.setFieldName("date_string");
rangeQuery.greaterThanOrEqual(ColumnValue.fromString("2021-01-01 00:00:00"));
rangeQuery.lessThan(ColumnValue.fromString("2023-01-01 00:00:00"));

Query epoch-second timestamps

If date_epoch is an Integer field in the data table and is mapped to a Date field in the search index with the epoch_second format, use epoch-second timestamps as query bounds. The following example queries values greater than 1609459200.

RangeQuery rangeQuery = new RangeQuery();
rangeQuery.setFieldName("date_epoch");
rangeQuery.greaterThan(ColumnValue.fromLong(1609459200L));