Filter specific data rows by using Tablestore SDK for .NET - Tablestore

Tablestore filters query results on the server and then returns the filtered results. Only the rows that meet the filter conditions are returned.

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.

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.

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.

Note

For more information about filters, see Configure a filter.

Limits

Filter conditions support relational operators (=, !=, >, >=, <, and <=) and logical operators (NOT, AND, and OR). A filter condition can include up to 10 subconditions.
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 call the GetRange operation, up to 5,000 rows or 4 MB of data can be scanned at the same time.
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 used by the filter.
ColumnValue	The value of the reference column used by the filter.
ComparatorType	The relational operators used by the filter. For more information about relational operators, see ComparatorType. The following relational operators are supported: EQUAL (=), NOT_EQUAL (!=), GREATER_THAN (>), GREATER_EQUAL (>=), LESS_THAN (<), and LESS_EQUAL (<=).
LogicOperator	The logical operators used by the filter. For information about logical operators, see LogicalOperator. The following logical operators are supported: NOT, AND, and OR.
PassIfMissing	Specifies whether to return a row when a reference column does not exist in the row. The data type of the parameter value is Boolean. The default value is true, which indicates that if a 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 latest versions of data in a reference column for comparison when the reference column contains data of multiple versions. The value of this parameter is of the Bool type. The default value is true. If the default value is used, the latest versions of data are used for comparison when a reference column contains data of multiple versions. If the value of LatestVersionsOnly is set to false, all versions of data in a reference column are used for comparison. If one version of data in the reference column meets the filter conditions, the row is returned.

Examples

Use RelationalCondition to filter data

The following sample code shows how to use RelationalCondition to read data from a data table and filter the data based on the value of the col0 column.

public void GetRowWithRelationalCondition(OTSClient otsClient)
{
    // Specify the primary key of the row. The primary key must be the same as the primary key that is specified in TableMeta when the table is created. 
    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); 

    // Perform 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.");
}

Use CompositeCondition to filter data

The following sample code shows how to use CompositeCondition to read data that meets condition col0==0 OR col1=="ff" .

public void GetRowWithCompositeCondition(OTSClient otsClient)
{
    // Specify the primary key of the row. The primary key must be the same as the primary key that is specified in TableMeta when the table is created. 
    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); 

    // Perform 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.");
}

References

If an application needs to use different attribute columns as query conditions to query data, you can specify the attribute columns as the primary key columns of a secondary index. This way, you can use the secondary index to query data based on the attribute columns. This accelerates 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.