Tablestore: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.

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 relational operators of =, !=, >, >=, <, and <= and logical operators of 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 query results. If the specified columns from which data is read do not include reference columns, the filter cannot query the values of reference columns.

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

  If no data matches the filter conditions in the range of the scan, the returned rows are empty. However, NextStartPrimaryKey may not be empty. If NextStartPrimaryKey is not empty, use the parameter value to continue scanning until the return value of NextStartPrimaryKey is empty.

Parameters

Examples

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"))

    // Set the reference column to the c1 column, the value of the reference column to Zhejiang, and the FilterIfMissing parameter to false. 
    condition := tablestore.NewSingleColumnCondition("c1", tablestore.ComparatorType(tablestore.CT_EQUAL), "Zhejiang")
    condition.FilterIfMissing = false

    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()
        if len(colMap.Columns) > 0 {
            fmt.Println("length is ", len(colMap.Columns))
            fmt.Println("get row col0 result is ", getResp.Columns[0].ColumnName, getResp.Columns[0].Value)
        } else {
            fmt.Println("No data that meets the conditions.")
        }
    }
}

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 filter conditions are met when the value of the c1 column is Zhejiang and the value of the c2 column 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()
        if len(colMap.Columns) > 0 {
            fmt.Println("length is ", len(colMap.Columns))
            fmt.Println("get row col0 result is ", getResp.Columns[0].ColumnName, getResp.Columns[0].Value)
        } else {
            fmt.Println("No data that meets the conditions.")
        }
    }
}

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.