search index range query-Tablestore(Tablestore)-阿里云帮助中心

A range query retrieves data from a table based on a specified range. For a Text type field, a match is found if any token in the field's value meets the range condition.

Interface

You can perform a range query using the Search or ParallelScan operation. The query type is RangeQuery.

Parameters

Parameter	Description
fieldName	The field to match.
from	The start position value. When you set the range condition, use `greaterThan` to specify greater than (>) or `greaterThanOrEqual` to specify greater than or equal to (>=).
to	The end position value. When you set the range condition, use `lessThan` to specify less than (<) or `lessThanOrEqual` to specify less than or equal to (<=).
includeLower	Specifies whether to include the `from` value in the results. The type is Boolean.
includeUpper	Specifies whether to include the `to` value in the results. The type is Boolean.
query	Set the query type to RangeQuery.
sort	Sorts the results in a specified order. For more information, see Sorting and pagination.
getTotalCount	Specifies whether to return the total number of matched rows. The default value is false, which indicates that the total number of matched rows is not returned. Returning the total number of matched rows affects query performance.
tableName	The name of the data table.
indexName	The name of the search index.
columnsToGet	Specifies whether to return all columns. This includes the `returnAll` and `columns` settings. `returnAll` is false by default. This means not all columns are returned. You can use `columns` to specify which columns to return. If you do not use `columns` to specify columns, only primary key columns are returned. If you set `returnAll` to true, all columns are returned.

Usage

You can perform a range query using the console, the command line interface, or a software development kit (SDK). Before you perform a range query, make the following preparations.

You have an Alibaba Cloud account or a RAM user that has permissions to perform operations on Tablestore. For information about how to grant Tablestore operation permissions to a RAM user, see Use a RAM policy to grant permissions to a RAM user.
If you want to use Tablestore SDKs or the Tablestore CLI to perform a query, an AccessKey pair is created for your Alibaba Cloud account or RAM user. For more information, see Create an AccessKey pair.
A data table is created. For more information, see Operations on tables.
A search index is created for the data table. For more information, see Create a search index.
If you want to use Tablestore SDKs to perform a query, an OTSClient instance is initialized. For more information, see Initialize a Tablestore client.
If you want to use the Tablestore CLI to perform a query, the Tablestore CLI is downloaded and started, and information about the instance that you want to access and the data table are configured. For more information, see Download the Tablestore CLI, Start the Tablestore CLI and configure access information, and Manage data tables.

Use the console

Go to the Indexes tab.
1. Log on to the Tablestore console.
2. In the top navigation bar, select a resource group and a region.
3. On the Overview page, click the name of the instance that you want to manage or click Manage Instance in the Actions column of the instance.
4. On the Tables tab of the Instance Details tab, click the name of the data table or click Indexes in the Actions column of the data table.
On the Indexes tab, find the search index that you want to use to query data and click Manage Data in the Actions column.
In the Query Data dialog box, you can query data.
1. By default, the system returns all attribute columns. To return specific attribute columns, turn off All Columns and specify the attribute columns that you want to return. Separate multiple attribute columns with commas (,).
  Note
  By default, the system returns all primary key columns of the data table.
2. Select the And, Or, or Not logical operator based on your business requirements.
  If you select the And logical operator, data that meets the query conditions is returned. If you select the Or operator and specify a single query condition, data that meets the query condition is returned. If you select the Or logical operator and specify multiple query conditions, data that meets one of the query conditions is returned. If you select the Not logical operator, data that does not meet the query conditions is returned.
3. Select an index field and click Add.
4. Set the query type for the index field to Range Query (RangeQuery) and specify a query range.
  
  The query range is a left-closed right-open interval.
5. By default, the sorting feature is disabled. If you want to sort the query results based on specific fields, turn on Sort and specify the fields based on which you want to sort the query results and the sorting order.
6. By default, the aggregation feature is disabled. If you want to collect statistics on a specific field, turn on Collect Statistics, specify the field based on which you want to collect statistics, and then configure the information that is required to collect statistics.
Click OK.
Data that meets the query conditions is displayed in the specified order on the Indexes tab.

Use the command line interface

You can run the search command in the command line interface to query data using a search index. For more information, see Search index.

Run the search command to query data from the table using the `search_index` search index. This command returns all indexed columns.
```
search -n search_index --return_all_indexed
```

Enter the query conditions as prompted by the system. The following is an example:

{
    "Offset": -1,
    "Limit": 10,
    "Collapse": null,
    "Sort": null,
    "GetTotalCount": true,
    "Token": null,
    "Query": {
        "Name": "RangeQuery",
        "Query": {
            "FieldName": "col_long",
            "From": 1,
            "To": 100,
            "IncludeLower": true,
            "IncludeUpper": false
        }
    }
}

Use an SDK

You can use a Java SDK, Go SDK, Python SDK, Node.js SDK, .NET SDK, or PHP SDK to perform a range query. This topic uses the Java SDK as an example.

Billing rules

useVCUmode (formerly reserved mode),usesearch CNYindex queries consumeVCUcompute resources. When you useCUmode (formerly pay-as-you-go mode),usesearch CNYindex queries consumeread throughput. For more search information, seesearch CNYindex billing.

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, fuzzy query, Boolean query, geo query, nested query, KNN vector query, and exists query. You can select query methods based on your business requirements to query data from multiple dimensions.
You can sort or paginate rows that meet the query conditions by using the sorting and paging features. For more information, see Perform 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 (remove duplicates).
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.