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 Initialization.
  • 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 RelationalCondition and CompositeCondition.

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

Limit

  • 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, nextStartPrimaryKey may not be empty. In this case, you need to use nextStartPrimaryKey to continue reading the data until nextStartPrimaryKey is empty.

Parameters

Parameter Description
ColumnName The name of the reference column in the filter.
ColumnValue The comparison value of the reference column in the filter.
ComparatorType The relational operator in the filter. For more information, see ComparatorType.

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

LogicOperator The logical operator in the filter. For more information, see LogicalOperator.

Logical operators include NOT, AND, and OR.

PassIfMissing Specifies whether to return a row if the row does not contain the reference column. The type of this parameter value is Boolean. The default value is true, which indicates that if the reference column does not exist in a row, the row is returned.

When PassIfMissing is set to false, if the reference column does not exist in a row, the row is not returned.

LatestVersionsOnly Specifies whether to use only the value of the latest version for comparison when the reference column contains data of multiple versions. The type of this parameter value is Boolean. The default value is true, which indicates that if the reference column has multiple versions of data, only the value of the latest version is used for comparison.

When LatestVersionsOnly is set to false, if a reference column has multiple versions of data, the values of all versions in the column are used for comparison. In this case, if only the value of one version meets the conditions, the row is returned.

Examples

  • The following code provides an example on how to construct RelationalCondition:
    public void GetRowWithRelationalCondition(OTSClient otsClient)
    {
        // Specify the primary key of the row. The primary key must be consistent with the primary key specified in TableMeta when you create a table.
        PrimaryKey primaryKey = new PrimaryKey
        {
            { "pk0", new ColumnValue(0) },
            { "pk1", new ColumnValue("abc") }
        };
    
        var rowQueryCriteria = new SingleRowQueryCriteria(TableName)
        {
            RowPrimaryKey = primaryKey
        };
    
        // Specify that the rows are returned when the value of col0 is 5.
        var filter = new RelationalCondition("col0",CompareOperator.EQUAL,new ColumnValue(5))
        {
            PassIfMissing = true
        };
    
        rowQueryCriteria.Filter = filter.ToFilter();
        rowQueryCriteria.AddColumnsToGet("col0");
        rowQueryCriteria.AddColumnsToGet("col1");
    
        GetRowRequest request = new GetRowRequest(rowQueryCriteria); 
    
        // Execute the query.
        GetRowResponse response = otsClient.GetRow(request);
        PrimaryKey primaryKeyRead = response.PrimaryKey;
        AttributeColumns attributesRead = response.Attribute;
    
        Console.WriteLine("Primary key read: ");
        foreach (KeyValuePair<string, ColumnValue> entry in primaryKeyRead)
        {
            Console.WriteLine(entry.Key + ":" + PrintColumnValue(entry.Value));
        }
    
        Console.WriteLine("Attributes read: ");
        foreach (KeyValuePair<string, ColumnValue> entry in attributesRead)
        {
            Console.WriteLine(entry.Key + ":" + PrintColumnValue(entry.Value));
        }
    
        Console.WriteLine("Get row with filter succeed.") ;
    }
  • The following code provides an example on how to construct CompositeCondition:
    public void GetRowWithCompositeCondition(OTSClient otsClient)
    {
        // Specify the primary key of the row. The primary key must be consistent with the primary key specified in TableMeta when you create a table.
        PrimaryKey primaryKey = new PrimaryKey
        {
            { "pk0", new ColumnValue(0) },
            { "pk1", new ColumnValue("abc") }
        };
    
        var rowQueryCriteria = new SingleRowQueryCriteria(TableName)
        {
            RowPrimaryKey = primaryKey
            };
    
        // Specify that the rows are returned when the value of col0 is 5, or the value of col1 is not ff.
        var filter1 = new RelationalCondition("col0",
                                              CompareOperator.EQUAL,
                                              new ColumnValue(5));
    
        var filter2 = new RelationalCondition("col1", CompareOperator.NOT_EQUAL, new ColumnValue("ff"));
    
        var filter = new CompositeCondition(LogicOperator.OR);
        filter.AddCondition(filter1);
        filter.AddCondition(filter2);
    
        rowQueryCriteria.Filter = filter.ToFilter();
        rowQueryCriteria.AddColumnsToGet("col0");
        rowQueryCriteria.AddColumnsToGet("col1");
    
        GetRowRequest request = new GetRowRequest(rowQueryCriteria); 
    
        // Execute the query.
        GetRowResponse response = otsClient.GetRow(request);
        PrimaryKey primaryKeyRead = response.PrimaryKey;
        AttributeColumns attributesRead = response.Attribute;
    
        Console.WriteLine("Primary key read: ");
        foreach (KeyValuePair<string, ColumnValue> entry in primaryKeyRead)
        {
            Console.WriteLine(entry.Key + ":" + PrintColumnValue(entry.Value));
        }
    
        Console.WriteLine("Attributes read: ");
        foreach (KeyValuePair<string, ColumnValue> entry in attributesRead)
        {
            Console.WriteLine(entry.Key + ":" + PrintColumnValue(entry.Value));
        }
    
        Console.WriteLine("Get row with filter succeed.") ;
    }