Tablestore filters query results on the server side before returning the results. Only rows of data that match the filter conditions 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 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.
Note For more information about filters, see Configure a filter in Function Introduction.

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 data. 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 of data does not match the filter conditions, the rows in the response are empty. However, NextStartPrimaryKey may not be empty. In this case, you must use NextStartPrimaryKey to continue reading the data until NextStartPrimaryKey is empty.

Parameters

ParameterDescription
ColumnNameThe name of the reference column in the filter.
ColumnValueThe comparison value of the reference column in the filter.
ComparatorTypeThe 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 (<=). In Tablestore, tablestore.CT_EQUAL indicates EQUAL (=), tablestore.CT_NOT_EQUAL indicates NOT_EQUAL (!=), tablestore.CT_GREATER_THAN indicates GREATER_THAN (>), tablestore.CT_GREATER_EQUAL indicates GREATER_EQUAL (>=), tablestore.CT_LESS_THAN indicates LESS_THAN (<), and tablestore.CT_LESS_EQUAL indicates LESS_EQUAL (<=).

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

Logical operators include NOT, AND, and OR. In Tablestore, tablestore. LO_NOT indicates NOT, tablestore.LO_AND indicates AND, and tablestore.LO_OR indicates OR.

FilterIfMissingSpecifies 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 FilterIfMissing is set to false, if the reference column does not exist in a row, the row is not returned.

LatestVersionOnlySpecifies 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 value of all versions in the column is 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 create SingleColumnValueFilter:
    func GetRowWithFilter(client *tablestore.TableStoreClient, tableName string) {
        fmt.Println("begin to get row")
        pk := new(tablestore.PrimaryKey)
        pk.AddPrimaryKeyColumn("pk1", "pk1value1")
        pk.AddPrimaryKeyColumn("pk2", int64(2))
        pk.AddPrimaryKeyColumn("pk3", []byte("pk3"))
    
        // Specify that the rows are returned when the value of c1 is Zhejiang, and FilterIfMissing is true.
        condition := tablestore.NewSingleColumnCondition("c1", tablestore.ComparatorType(tablestore.CT_EQUAL), "Zhejiang")
        condition.FilterIfMissing = true
    
        criteria := &tablestore.SingleRowQueryCriteria{
            TableName:     tableName,
            PrimaryKey:    pk,
            MaxVersion:    1,
            Filter:        condition,
        }
    
        getResp, err := client.GetRow(&tablestore.GetRowRequest{SingleRowQueryCriteria: criteria})
        if err != nil {
            fmt.Println("getrow failed with error:", err)
        } else {
            colMap := getResp.GetColumnMap()
            fmt.Println("length is ", len(colMap.Columns))
            fmt.Println("get row col0 result is ", getResp.Columns[0].ColumnName, getResp.Columns[0].Value)
        }
    }
  • The following code provides an example on how to create CompositeColumnValueFilter:
    func GetRowWithCompositeColumnValueFilter(client *tablestore.TableStoreClient, tableName string) {
        fmt.Println("begin to get row")
        pk := new(tablestore.PrimaryKey)
        pk.AddPrimaryKeyColumn("pk1", "pk1value1")
        pk.AddPrimaryKeyColumn("pk2", int64(2))
        pk.AddPrimaryKeyColumn("pk3", []byte("pk3"))
    
        // Specify that the rows are returned when the value of c1 is Zhejiang, and the value of c2 is Hangzhou.
        filter := tablestore.NewCompositeColumnCondition(tablestore.LO_AND)
        filter1 := tablestore.NewSingleColumnCondition("c1", tablestore.CT_EQUAL, "Zhejiang")
        filter2 := tablestore.NewSingleColumnCondition("c2", tablestore.CT_EQUAL, "Hangzhou")
        filter.AddFilter(filter2)
        filter.AddFilter(filter1)
    
        criteria := &tablestore.SingleRowQueryCriteria{
            TableName:  tableName,
            PrimaryKey: pk,
            MaxVersion: 1,
            Filter:     filter,
        }
    
        getResp, err := client.GetRow(&tablestore.GetRowRequest{SingleRowQueryCriteria: criteria})
        if err != nil {
            fmt.Println("getrow failed with error:", err)
        } else {
            colMap := getResp.GetColumnMap()
            fmt.Println("length is ", len(colMap.Columns))
            fmt.Println("get row col0 result is ", getResp.Columns[0].ColumnName, getResp.Columns[0].Value)
        }
    }