Use filters

Use Tablestore SDK for Python to filter rows by attribute column values on the server and reduce the amount of data returned to the client.

Prerequisites

Install the Tablestore SDK for Python and initialize a client.

Function description

Filters are applied after the server reads data. Only rows that meet the conditions are returned to the client. Filtering does not reduce the number of rows read by the server or the read CUs consumed. Use filters after you narrow the primary key range.

The following filter conditions are supported:

SingleColumnCondition compares the value of one attribute column.
SingleColumnRegexCondition extracts a substring from a string attribute column and compares the extracted value.
CompositeColumnCondition combines up to 32 conditions with AND, OR, or NOT.

The following example returns only rows whose latest value in the status attribute column is online during a range read.

column_filter = SingleColumnCondition(
    "status",
    "online",
    ComparatorType.EQUAL,
    pass_if_missing=False,
)
start_primary_key = [("partition", "device"), ("id", INF_MIN)]
end_primary_key = [("partition", "device"), ("id", INF_MAX)]

consumed, next_start_primary_key, rows, next_token = client.get_range(
    "example_table",
    Direction.FORWARD,
    start_primary_key,
    end_primary_key,
    column_filter=column_filter,
)

Parameters

Single-column value condition

Call SingleColumnCondition(column_name, column_value, comparator, pass_if_missing=True, latest_version_only=True) to create a single-column value condition.

Name	Type	Description
column_name (required)	`str`	The name of the attribute column to evaluate.
column_value (required)	`str`, `int`, `bytes`, `float`, or `bool`	The target value. Its type must match the attribute column value type.
comparator (required)	`ComparatorType`	The comparison operator. Valid values are `EQUAL`, `NOT_EQUAL`, `GREATER_THAN`, `GREATER_EQUAL`, `LESS_THAN`, and `LESS_EQUAL`.
pass_if_missing (optional)	`bool`	Specifies whether a row passes the filter if the target attribute column is missing. Default value: `True`. If this parameter is `False`, rows without the column are not returned.
latest_version_only (optional)	`bool`	Specifies whether to evaluate only the latest version. Default value: `True`. If this parameter is `False`, a row passes if any version meets the condition.

Regular expression condition

Call SingleColumnRegexCondition(column_name, comparator, column_value=None, regex_rule=None, latest_version_only=True) to create a regular expression condition. Only string attribute columns support regular expression filtering.

Name	Type	Description
column_name (required)	`str`	The name of the string attribute column to evaluate.
comparator (required)	`ComparatorType`	The comparison operator. Valid values are `EQUAL`, `NOT_EQUAL`, `GREATER_THAN`, `GREATER_EQUAL`, `LESS_THAN`, `LESS_EQUAL`, `EXIST`, and `NOT_EXIST`.
column_value (optional)	`str`, `int`, or `float`	The target value. Omit this parameter if `comparator` is `EXIST` or `NOT_EXIST`. Otherwise, this parameter is required. Its type must match `regex_rule.cast_type`.
regex_rule (optional)	`RegexRule`	The substring extraction and type conversion rule.
latest_version_only (optional)	`bool`	Specifies whether to evaluate only the latest version. Default value: `True`. If this parameter is `False`, a row passes if any version meets the condition.

If the target attribute column is missing, a row never passes a regular expression filter. pass_if_missing cannot be configured separately for this condition.

Regular expression rule

The regex_rule parameter is of the RegexRule type and contains the following parameters.

Name	Type	Description
regex_input (required)	`str`	The regular expression, up to 256 bytes. Perl-compatible expressions and single-byte characters are supported. Chinese characters are not supported. If the expression contains capturing groups, the first group is extracted. Otherwise, the entire match is extracted.
cast_type (required)	`CastType`	The type to which the extracted value is converted. Valid values are `VT_STRING`, `VT_INTEGER`, and `VT_DOUBLE`. Rows whose extracted values cannot be converted do not pass the filter.

Composite condition

Call CompositeColumnCondition(combinator) to create a composite condition, and then call add_sub_condition to add conditions.

Name	Type	Description
combinator (required)	`LogicalOperator`	The logical operator. Valid values are `AND`, `OR`, and `NOT`.
sub_conditions (required)	`List[ColumnCondition]`	The conditions to combine. Single-column value conditions, regular expression conditions, and nested composite conditions are supported, with a maximum of 32 conditions.

Examples

Filter with a regular expression

The following example extracts the number from values such as user_004 in the account attribute column, converts the number to an integer, and returns only rows whose extracted value is greater than 3.

regex_rule = RegexRule(r"user_(\d+)", CastType.VT_INTEGER)
column_filter = SingleColumnRegexCondition(
    "account",
    ComparatorType.GREATER_THAN,
    3,
    regex_rule,
)

consumed, next_start_primary_key, rows, next_token = client.get_range(
    "example_table",
    Direction.FORWARD,
    start_primary_key,
    end_primary_key,
    column_filter=column_filter,
)

Combine filters

The following example constructs the filter (category == "sensor" OR category == "gateway") AND score > 3.

category_filter = CompositeColumnCondition(LogicalOperator.OR)
category_filter.add_sub_condition(
    SingleColumnCondition("category", "sensor", ComparatorType.EQUAL)
)
category_filter.add_sub_condition(
    SingleColumnCondition("category", "gateway", ComparatorType.EQUAL)
)

column_filter = CompositeColumnCondition(LogicalOperator.AND)
column_filter.add_sub_condition(category_filter)
column_filter.add_sub_condition(
    SingleColumnCondition("score", 3, ComparatorType.GREATER_THAN)
)

Evaluate historical versions

The following example sets latest_version_only to False. A row is returned if any version of the status attribute column is online. Rows without the status column are not returned.

column_filter = SingleColumnCondition(
    "status",
    "online",
    ComparatorType.EQUAL,
    pass_if_missing=False,
    latest_version_only=False,
)