Tablestore:Filters

Sample code

This sample code shows how to run a range query for rows with a primary key in the range [`row1`, `row3`) and then filter the results to return only rows where the col1 attribute column is equal to val1.

func SingleValueFilterSample(client *tablestore.TableStoreClient) {
    // Construct the query criteria.
    rangeRowQueryCriteria := &tablestore.RangeRowQueryCriteria{}
    rangeRowQueryCriteria.TableName = "test_table"
    // Set the start primary key.
    startPK := new(tablestore.PrimaryKey)
    startPK.AddPrimaryKeyColumn("id", "row1")
    rangeRowQueryCriteria.StartPrimaryKey = startPK
    // Set the end primary key. The row with this key is not included in the result.
    endPK := new(tablestore.PrimaryKey)
    endPK.AddPrimaryKeyColumn("id", "row3")
    rangeRowQueryCriteria.EndPrimaryKey = endPK
    // Set the number of versions to query.
    rangeRowQueryCriteria.MaxVersion = 1

    // Construct a filter where col1 equals "val1".
    singleColumnCondition := tablestore.NewSingleColumnCondition("col1", tablestore.CT_EQUAL, "val1")
    rangeRowQueryCriteria.Filter = singleColumnCondition

    // Call the GetRange operation to query data.
    getRangeRequest := new(tablestore.GetRangeRequest)
    getRangeRequest.RangeRowQueryCriteria = rangeRowQueryCriteria
    getRangeResponse, err := client.GetRange(getRangeRequest)

    // Process the response.
    if err != nil {
        fmt.Println("Range get failed with error: ", err)
    } else {
        fmt.Printf("* RequestId: %s \n", getRangeResponse.RequestId)
        fmt.Printf("* Read CU Cost: %d \n", getRangeResponse.ConsumedCapacityUnit.Read)
        fmt.Printf("* Write CU Cost: %d \n", getRangeResponse.ConsumedCapacityUnit.Write)
        fmt.Println("* Rows Data:")
        for _, row := range getRangeResponse.Rows {
            fmt.Printf("PrimaryKey: %v; Columns: ", row.PrimaryKey.PrimaryKeys)
            for _, column := range row.Columns {
                fmt.Printf("%v ", column)
            }
            fmt.Printf("\n")
        }
    }
}

• To filter out a row if it does not contain the specified attribute column, set the FilterIfMissing parameter to true.

  singleColumnCondition.FilterIfMissing = true

• To evaluate only the latest data version of the attribute column, set the LatestVersionOnly parameter to true.

  singleColumnCondition.LatestVersionOnly = true

Sample code

The following sample code shows how to perform a range query for rows with a primary key in the range [`row1`, `row3`). It then applies a regular expression filter to return only rows where the value of the col1 attribute column matches the expression 1([a-z]+)5 and the captured substring is aaa.

func SingleRegexFilterSample(client *tablestore.TableStoreClient) {
    // Construct the query criteria.
    rangeRowQueryCriteria := &tablestore.RangeRowQueryCriteria{}
    rangeRowQueryCriteria.TableName = "test_table"
    // Set the start primary key.
    startPK := new(tablestore.PrimaryKey)
    startPK.AddPrimaryKeyColumn("id", "row1")
    rangeRowQueryCriteria.StartPrimaryKey = startPK
    // Set the end primary key. The row with this key is not included in the result.
    endPK := new(tablestore.PrimaryKey)
    endPK.AddPrimaryKeyColumn("id", "row3")
    rangeRowQueryCriteria.EndPrimaryKey = endPK
    // Set the number of versions to query.
    rangeRowQueryCriteria.MaxVersion = 1

    // Construct the filter: cast<String>(reg(col1)) == "aaa"
    valueTransferRule := tablestore.NewValueTransferRule("1([a-z]+)5", tablestore.Variant_STRING)
    singleColumnValueRegexFilter := tablestore.NewSingleColumnValueRegexFilter("col1", tablestore.CT_EQUAL, valueTransferRule, "aaa")
    rangeRowQueryCriteria.Filter = singleColumnValueRegexFilter

    // Call the GetRange operation to query data.
    getRangeRequest := new(tablestore.GetRangeRequest)
    getRangeRequest.RangeRowQueryCriteria = rangeRowQueryCriteria
    getRangeResponse, err := client.GetRange(getRangeRequest)

    // Process the response.
    if err != nil {
        fmt.Println("Range get failed with error: ", err)
    } else {
        fmt.Printf("* RequestId: %s \n", getRangeResponse.RequestId)
        fmt.Printf("* Read CU Cost: %d \n", getRangeResponse.ConsumedCapacityUnit.Read)
        fmt.Printf("* Write CU Cost: %d \n", getRangeResponse.ConsumedCapacityUnit.Write)
        fmt.Println("* Rows Data:")
        for _, row := range getRangeResponse.Rows {
            fmt.Printf("PrimaryKey: %v; Columns: ", row.PrimaryKey.PrimaryKeys)
            for _, column := range row.Columns {
                fmt.Printf("%v ", column)
            }
            fmt.Printf("\n")
        }
    }
}

Sample code

This sample code shows how to run a range query for rows with a primary key in the range [`row1`, `row3`) and use a composite filter to refine the results.

func CompositeFilterSample(client *tablestore.TableStoreClient) {
    // Construct the query criteria.
    rangeRowQueryCriteria := &tablestore.RangeRowQueryCriteria{}
    rangeRowQueryCriteria.TableName = "test_table"
    // Set the start primary key.
    startPK := new(tablestore.PrimaryKey)
    startPK.AddPrimaryKeyColumn("id", "row1")
    rangeRowQueryCriteria.StartPrimaryKey = startPK
    // Set the end primary key. The row with this key is not included in the result.
    endPK := new(tablestore.PrimaryKey)
    endPK.AddPrimaryKeyColumn("id", "row3")
    rangeRowQueryCriteria.EndPrimaryKey = endPK
    // Set the number of versions to query.
    rangeRowQueryCriteria.MaxVersion = 1

    // Construct the first single-column value filter: col1 == "val1".
    singleColumnCondition1 := tablestore.NewSingleColumnCondition("col1", tablestore.CT_EQUAL, "val1")
    // Construct the single-column regex filter: cast<String>(reg(col2)) == "aaa".
    valueTransferRule := tablestore.NewValueTransferRule("1([a-z]+)5", tablestore.Variant_STRING)
    singleColumnValueRegexFilter2 := tablestore.NewSingleColumnValueRegexFilter("col2", tablestore.CT_EQUAL, valueTransferRule, "aaa")
    // Construct the first composite filter: col1 == "val1" OR cast<String>(reg(col2)) == "aaa".
    compositeCondition1 := tablestore.NewCompositeColumnCondition(tablestore.LO_OR)
    compositeCondition1.AddFilter(singleColumnCondition1)
    compositeCondition1.AddFilter(singleColumnValueRegexFilter2)
    // Construct the second single-column value filter: col3 == "val3".
    singleColumnCondition3 := tablestore.NewSingleColumnCondition("col3", tablestore.CT_EQUAL, "val3")
    // Construct the final composite filter: (col1 == "val1" OR cast<String>(reg(col2)) == "aaa") AND col3 == "val3".
    compositeCondition2 := tablestore.NewCompositeColumnCondition(tablestore.LO_AND)
    compositeCondition2.AddFilter(compositeCondition1)
    compositeCondition2.AddFilter(singleColumnCondition3)

    // Add the filter to the query.
    rangeRowQueryCriteria.Filter = compositeCondition2

    // Call the GetRange operation to query data.
    getRangeRequest := new(tablestore.GetRangeRequest)
    getRangeRequest.RangeRowQueryCriteria = rangeRowQueryCriteria
    getRangeResponse, err := client.GetRange(getRangeRequest)

    // Process the response.
    if err != nil {
        fmt.Println("Range get failed with error: ", err)
    } else {
        fmt.Printf("* RequestId: %s \n", getRangeResponse.RequestId)
        fmt.Printf("* Read CU Cost: %d \n", getRangeResponse.ConsumedCapacityUnit.Read)
        fmt.Printf("* Write CU Cost: %d \n", getRangeResponse.ConsumedCapacityUnit.Write)
        fmt.Println("* Rows Data:")
        for _, row := range getRangeResponse.Rows {
            fmt.Printf("PrimaryKey: %v; Columns: ", row.PrimaryKey.PrimaryKeys)
            for _, column := range row.Columns {
                fmt.Printf("%v ", column)
            }
            fmt.Printf("\n")
        }
    }
}