Tablestore provides filter to filter the results on the server and return the rows that match the filter conditions. After results are filtered, only rows of data that matches the filter condition are returned.

Prerequisites

  • The OTSClient instance is initialized. For more information, see Initialize SDK for Java.
  • A data table is created. Data is written to the table.

Usage notes

When you query data by calling the GetRow, BatchGetRow, or GetRange operation, you can use the filter to return only the rows that meet the filter conditions.

Filters include SingleColumnValueFilter and CompositeColumnValueFilter.

  • SingleColumnValueFilter: determines whether to filter a row based only on the values of a reference column.
  • CompositeColumnValueFilter: determines whether to filter a row by combining the filter conditions of values of multiple reference columns.

Limits

  • The filter conditions support relational operators such as =, !=, >, >=, <, and <=, and logical operators such as NOT, AND, and OR. You can specify a maximum of 10 conditions.
  • Reference columns in filters must be included in the read data. If the specified columns from which data is read do not include reference columns, the filter cannot query the values of reference columns.
  • You can use filters by calling the GetRow, BatchGetRow, and GetRange operations, which does not change the native semantics or limited items of these operations.

    When you use GetRange, the number of rows scanned cannot exceed 5,000, or the data scanned cannot exceed 4 MB in size.

    If the scanned 5,000 rows or 4 MB of data does not match the filter conditions, the rows in the response are empty. However, NextStartPrimaryKey may not be empty. In this case, you must use NextStartPrimaryKey to continue reading the data until NextStartPrimaryKey is empty.

Parameters

Parameter Description
ColumnName The name of the reference column used by the filter.
ColumnValue The value of the reference column used by the filter.
CompareOperator The relational operator used by the filter.

The relational operators include EQUAL (=), NOT_EQUAL (! =), GREATER_THAN (>), GREATER_EQUAL (>=), LESS_THAN (<), and LESS_EQUAL (<=).

LogicOperator The logical operator used by the filter.

The logical operators include NOT, AND, and OR.

PassIfMissing Determine whether to return a row when a reference column in the row does not exist. The data type of the parameter value is bool. The default value is true, which indicates that if a reference column does not exist in a row, the row is returned.

If you set the value of PassIfMissing to false, a row is not returned if a reference column does not exist in the row.

LatestVersionsOnly Determine whether to use only the latest versions of data in the reference columns for comparison when each reference column contains data of multiple versions. The data type of the parameter value is bool. The default value is true, which indicates that the latest versions of data are used for comparison when each reference column contains data of multiple versions.

If you set the value of LatestVersionsOnly to false, all versions of data in a reference column are used for comparison. The row is returned if one version of data in the reference column meets the condition.

Examples

  • The following code provides an example on how to construct SingleColumnValueFilter:
            // Specify that the row is returned when the value of the Col0 column is 0.
            SingleColumnValueFilter singleColumnValueFilter = new SingleColumnValueFilter("Col0",
                    SingleColumnValueFilter.CompareOperator.EQUAL, ColumnValue.fromLong(0));
            // If the Col0 column does not exist, the row that contains this column is not returned.
            singleColumnValueFilter.setPassIfMissing(false);
            // Specify that only the latest version is used for comparison.
            singleColumnValueFilter.setLatestVersionsOnly(true);
                        
  • The following code provides an example on how to construct CompositeColumnValueFilter:
            // Set condition composite1 to (Col0 == 0) AND (Col1 > 100).
            CompositeColumnValueFilter composite1 = new CompositeColumnValueFilter(CompositeColumnValueFilter.LogicOperator.AND);
            SingleColumnValueFilter single1 = new SingleColumnValueFilter("Col0",
                    SingleColumnValueFilter.CompareOperator.EQUAL, ColumnValue.fromLong(0));
            SingleColumnValueFilter single2 = new SingleColumnValueFilter("Col1",
                    SingleColumnValueFilter.CompareOperator.GREATER_THAN, ColumnValue.fromLong(100));
            composite1.addFilter(single1);
            composite1.addFilter(single2);
    
            // Set condition composite2 to ( (Col0 == 0) AND (Col1 > 100) ) OR (Col2 <= 10).
            CompositeColumnValueFilter composite2 = new CompositeColumnValueFilter(CompositeColumnValueFilter.LogicOperator.OR);
            SingleColumnValueFilter single3 = new SingleColumnValueFilter("Col2",
                    SingleColumnValueFilter.CompareOperator.LESS_EQUAL, ColumnValue.fromLong(10));
            composite2.addFilter(composite1);
            composite2.addFilter(single3);