Use virtual columns

Use the Tablestore SDK for Go to map a source field to another index field type without changing the table schema or rewriting data.

Prerequisites

Before you begin, complete the following preparations:

Install the Tablestore Go SDK and initialize a client.
The maximum number of versions of the data table is 1.
The data table TTL is -1, or updates by using UpdateRow are disabled.

Limits

The source field and virtual column must comply with the following type conversion rules.

Table field type	Virtual column type
String	Keyword (including arrays), Text (including arrays), Long (including arrays), Double (including arrays), Date (including arrays), and Geo-point (including arrays)
Long	Keyword, Text, and Date
Double	Keyword and Text

A virtual column can be used only for queries and cannot be returned by ColumnsToGet. To obtain a value, return the source field mapped to the virtual column.

Description

A virtual column maps a source field in a data table to another search index field type. After you create a search index that contains a virtual column, query data by using the virtual column name.

Create a virtual column

fields := []*tablestore.FieldSchema{
    {
        FieldName: proto.String("price"),
        FieldType: tablestore.FieldType_LONG,
        Index:     proto.Bool(true),
    },
    {
        FieldName:        proto.String("price_text"),
        FieldType:        tablestore.FieldType_KEYWORD,
        Index:            proto.Bool(true),
        IsVirtualField:   proto.Bool(true),
        SourceFieldNames: []string{"price"},
    },
}

_, err := client.CreateSearchIndex(&tablestore.CreateSearchIndexRequest{
    TableName: "example_table",
    IndexName: "example_index",
    IndexSchema: &tablestore.IndexSchema{
        FieldSchemas: fields,
    },
})
if err != nil {
    log.Fatal(err)
}

After the create request succeeds, wait until index data is synchronized before querying the index.

Query data by using a virtual column

tableName := "example_table"
indexName := "example_index"

query := &search.TermQuery{FieldName: "price_text", Term: "1000"}
searchQuery := search.NewSearchQuery().
    SetQuery(query).
    SetLimit(10).
    SetGetTotalCount(true)

response, err := client.Search(&tablestore.SearchRequest{
    TableName:   tableName,
    IndexName:   indexName,
    SearchQuery: searchQuery,
    ColumnsToGet: &tablestore.ColumnsToGet{
        ReturnAllFromIndex: true,
    },
})
if err != nil {
    log.Fatal(err)
}

fmt.Println(response.TotalCount)
fmt.Println(response.Rows)

Parameters

tablestore.FieldSchema contains the following parameters for defining a virtual column. For other index field parameters, see Create a search index.

Name	Type	Description
FieldName (required)	*string	The virtual column name, which must be unique in the index schema.
FieldType (required)	tablestore.FieldType	The virtual column type, which must comply with the supported conversion rules.
Index (required)	*bool	Set this parameter to true to index the virtual column.
IsVirtualField (required)	*bool	Set this parameter to true to define a virtual column.
SourceFieldNames (required)	[]string	The source field names. Only one source field is supported.
IsArray (optional)	*bool	Specifies whether the virtual column is an array. Default value: false.
DateFormats (optional)	[]string	The date formats supported by a Date virtual column. This parameter is required for a Date virtual column.