Tablestore filters are used to filter the query results on the server side. The server returns only the rows or columns that match the filtering conditions. Filters can be used for GetRow, BatchGetRow, and GetRange operations.

Filter types

The following filters can be used to filter rows based on the reference column values.
  • 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 values of multiple reference columns.
Note You must include the reference columns in the query results. If the columns to be queried do not include any reference columns, the filters do not obtain any values. When a specified reference column does not exist, RelationalCondition uses the configured PassIfMissing parameter to determine whether the filtering conditions are met and what operation to perform if a reference column does not exist.

Examples

  • Construct RelationalCondition
    public void GetRowWithRelationalCondition(OTSClient otsClient)
    {
        // Specify the primary keys of the row. These primary keys must be consistent with the primary keys specified in TableMeta during table creation.
        PrimaryKey primaryKey = new PrimaryKey
        {
            { "pk0", new ColumnValue(0) },
            { "pk1", new ColumnValue("abc") }
        };
    
        var rowQueryCriteria = new SingleRowQueryCriteria(TableName)
        {
            RowPrimaryKey = primaryKey
        };
    
        // Specify to return only rows in which 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.") ;
    }
  • Construct CompositeCondition
    public void GetRowWithCompositeCondition(OTSClient otsClient)
    {
        // Specify the primary keys of the row. These primary keys must be consistent with the primary keys specified in TableMeta during table creation.
        PrimaryKey primaryKey = new PrimaryKey
        {
            { "pk0", new ColumnValue(0) },
            { "pk1", new ColumnValue("abc") }
        };
    
        var rowQueryCriteria = new SingleRowQueryCriteria(TableName)
        {
            RowPrimaryKey = primaryKey
            };
    
        // Specify to return only rows in which 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.") ;
    }