Tablestore:Use a filter

Prerequisites

• An OTSClient instance is initialized. For more information, see Initialize an OTSClient instance.

• A data table is created and data is written to the data table. For more information, see Create a data table and Write data.

Methods

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.

Filters include SingleColumnValueFilter and CompositeColumnValueFilter.

• SingleColumnValueFilter: determines whether to return a row based only on the value of a reference column.

• CompositeColumnValueFilter: determines whether to return a row based on a combination of filter conditions for values of multiple reference columns.

Usage notes

• Filter conditions support the following relational operators: =, !=, >, >=, <, and <=. Filter conditions also support the following logical operators: NOT, AND, and OR. You can specify up to 10 subconditions for a filter condition.

• The reference columns that are used by a filter must be included in the returned columns. Otherwise, the filter cannot query the values of the reference columns.

• When you use filters for the GetRow, BatchGetRow, and GetRange operations, the native semantics or limits of these operations are not affected.

  When you use the GetRange operation, up to 5,000 rows or 4 MB of data can be scanned at a time.

  If the scanned data does not match the filter conditions, the rows in the response are empty. However, the next_start_primary_key parameter may not be empty. If the next_start_primary_key parameter is not empty, you can use the parameter value to continue scanning until the NextStartPrimaryKey parameter is empty.

Parameters

You can configure the column_filter parameter to use filters for the GetRow, BatchGetRow, and GetRange operations.

    'column_filter' => <ColumnFilter>

• SingleColumnValueFilter

  A SingleColumnValueFilter allows you to compare a column with a constant. The comparison between two columns or two constants is not supported.

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

• CompositeColumnValueFilter

  A CompositeColumnValueFilter is of 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

$request = [
    'table_name' => 'MyTable',
    'primary_key' => [ // Specify the primary key. 
        ['PK0', 123],
        ['PK1', 'abc']
    ],
    'max_versions' => 1,                     // Set this parameter to 1 to read data of the latest version. 
    'columns_to_get' => ['Col0']             // Specify the column that you want to read. 
    
    // Specify a filter to return the row if the value of the Col0 column is 0. 
    'column_filter' => [
        'column_name' => 'col0',
        'value' => 0,
        'comparator' => ComparatorTypeConst::CONST_EQUAL,
        'pass_if_missing' => false                         / If the Col0 column does not exist in the row, the row is not returned. 
        'latest_version_only' => true                      // Specify that only the latest version is used for comparison. 
    ]
];
$response = $otsClient->getRow ($request); 

// Set the composite1 condition 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 the composite2 condition 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
            ]
        ]
    ];  
                    

References

• If an application needs to use different attribute columns as query conditions to query data, you can specify these attribute columns as the primary key columns of a secondary index to accelerate data queries. For more information, see Secondary Index.

• If your business requires multi-dimensional queries and data analysis, you can create a search index and specify the required attribute columns as the fields of the search index. Then, you can query and analyze data by using the search index. For example, you can use a search index to perform queries based on non-primary key columns, Boolean queries, and fuzzy queries. You can also use a search index to obtain the maximum and minimum values, collect statistics about the number of rows, and group query results. For more information, see Search index.

• You can use the SQL query feature to query and analyze data in a table. For more information, see Query data.