Tablestore:Configure filter

Prerequisites

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

Usage notes

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

The available filters are SingleColumnValueFilter, SingleColumnValueRegexFilter, and CompositeColumnValueFilter.

• SingleColumnValueFilter determines whether to filter a row based only on the values of a reference column.
• SingleColumnValueRegexFilter uses regular expressions to match column values of the String type and extract matching substrings. Then, this filter converts the data type of the extracted substrings to String, Integer, or Double and filters the values after conversion.
• 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 result. 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 does not match the filter conditions, the rows in the response are empty. However, next_start_primary_key may not be empty. In this case, you need to use next_start_primary_key to continue reading the data until next_start_primary_key is empty.

Parameters

Filters are applicable to the column_filter parameter of the GetRow, BatchGetRow, and GetRange operations.

    'column_filter' => <ColumnFilter>

• The following code provides an example on how to create SingleColumnValueFilter:

  SingleColumnValueFilter supports comparison between the value of a column that can be a primary key column and a constant. SingleColumnValueFilter does not support the comparison between two columns or two constants.

      [
          'column_name' => '<string>',
          'value' => <ColumnValue>,
          'comparator' => <ComparatorType>
          'pass_if_missing' => true || false
          'latest_version_only' => true || false
      ]
          

• The following code provides an example on how to create CompositeColumnValueFilter:

  CompositeColumnValueFilter has a tree structure. It contains the logical_operator inner node and the SingleColumnValueFilter leaf node.

      [
          'logical_operator' => <LogicalOperator>
          'sub_filters' => [
              <ColumnFilter>,
              <ColumnFilter>,
              <ColumnFilter>,
              // other conditions
          ]
      ]
          

Examples

• The following code provides an example on how to construct SingleColumnValueFilter:

      // Specify that the row is returned when the value of Col0 is 0.
      $column_filter = [
          'column_name' => 'Col0',
          'value' => 0,
          'comparator' => ComparatorTypeConst::CONST_EQUAL 
          'pass_if_missing' => false                         / If Col0 does not exist, the row that contains this column is not returned.
          'latest_version_only' => true                      // Specify that only the latest version is used for comparison.
      ];
                      

• The following code provides an example on how to construct CompositeColumnValueFilter:

      // Set condition composite1 to (Col0 == 0) AND (Col1 > 100).
      $composite1 = [
          'logical_operator' => LogicalOperatorConst::CONST_AND,
          'sub_filters' => [
              [
                  'column_name' => 'Col0',
                  'value' => 0,
                  'comparator' => ComparatorTypeConst::CONST_EQUAL
              ],
              [
                  'column_name' => 'Col1',
                  'value' => 100,
                  'comparator' => ComparatorTypeConst::CONST_GREATER_THAN
              ]
          ]
      ];
      // Set condition composite2 to ( (Col0 == 0) AND (Col1 > 100) ) OR (Col2 <= 10).
      $composite2 = [
          'logical_operator' => LogicalOperatorConst::CONST_OR,
          'sub_filters' => [
              $composite1,
              [
                  'column_name' => 'Col2',
                  'value' => 10,
                  'comparator' => ComparatorTypeConst::CONST_LESS_EQUAL
              ]
          ]
      ];