Tablestore:Array and Nested data types

Array formats

The following table describes the combinations of the Array data type and primitive data types in search indexes.

Usage notes

If the data type of a field in a search index is a combination of the Array data type and a primitive data type, such as Long or Double, the field in the data table for which the search index is created must be of the String type and the field in the search index must be of the corresponding primitive data type.

For example, the price field is of the Double Array type. The value of the price field in the data table must be of the String type, the value of the price field in the search index must be of the Double type, and isArray=true must be specified.

Examples

The following table describes the sample data in the data table named array_search_table.

The data table consists of the pk primary key column of the String type, the col_keyword_array attribute column of the String type, and the col_long_array attribute column of the String type.

1. Create a search index.

   In this example, a search index named array_query_table_index is created. The search index contains the following fields of the Array data type: the col_keyword_array field that stores elements of the Keyword data type and the col_long_array field that stores elements of the Long data type.

   

2. Use the search index to query data of the Array data type.

   The following sample Java code provides an example on how to use a search index to query data of the Array data type. In this example, the rows in which the value of the col_keyword_array column contains elements that exactly match "Cloud server" and the value of the col_long_array column contains elements that are equal to 2024 meet the query conditions.

   Note

   You can execute SQL statements to query data of the Array data type in a search index. For more information, see Execute SQL statements to query data by using the search index.

   private static void query(SyncClient client) {
       // Condition 1: The value of the col_keyword_array column contains elements that exactly match "Cloud server".
       TermQuery keywordTermQuery = new TermQuery(); // Set the query type to TermQuery. 
       keywordTermQuery.setFieldName("col_keyword_array"); // Specify the name of the column that you want to match. 
       keywordTermQuery.setTerm(ColumnValue.fromString("Cloud server")); // Specify the value that you want to use to match the field. 
       
       // Condition 2: The value of the col_long_array column contains elements that are equal to 2024.
       TermQuery longTermQuery = new TermQuery(); // Set the query type to TermQuery. 
       longTermQuery.setFieldName("col_long_array"); // Specify the name of the column that you want to match. 
       longTermQuery.setTerm(ColumnValue.fromLong(2024l)); // Specify the value that you want to use to match the field. 
       
       SearchQuery searchQuery = new SearchQuery();
       // Construct a Boolean query in which the query results meet Condition 1 and Condition 2 at the same time. 
       BoolQuery boolQuery = new BoolQuery();
       boolQuery.setMustQueries(Arrays.asList(keywordTermQuery, longTermQuery));
       searchQuery.setQuery(boolQuery);
       //searchQuery.setGetTotalCount(true); // Specify that the total number of matched rows is returned. 
       
       SearchRequest searchRequest = new SearchRequest("<TABLE_NAME>", "<SEARCH_INDEX_NAME>", searchQuery);
       // You can configure the columnsToGet parameter to specify the columns that you want to return or specify that all columns are returned. If you do not configure this parameter, only the primary key columns are returned. 
       //SearchRequest.ColumnsToGet columnsToGet = new SearchRequest.ColumnsToGet();
       //columnsToGet.setReturnAll(true); // Specify that all columns are returned. 
       //columnsToGet.setColumns(Arrays.asList("ColName1","ColName2")); // Specify the columns that you want to return. 
       //searchRequest.setColumnsToGet(columnsToGet);
       
       SearchResponse resp = client.search(searchRequest);
       //System.out.println("TotalCount: " + resp.getTotalCount()); // Specify that the total number of matched rows instead of the number of returned rows is displayed. 
       System.out.println("Row: " + resp.getRows());
   }

Nested formats

Nested fields are classified into single-level and multi-level nested fields. The following table describes the two types.

[
    {
        "tagName": "tag1",
        "score": 0.8
    },
    {
        "tagName": "tag2",
        "score": 0.2
    }
]

[
    {
        name:"John",
        "age": 20,
        "phone": "1390000****",
        "address": [
            {
                "province": "Zhejiang",
                "city": "Hangzhou",
                "street": "1201 Xingfu Community, Sunshine Avenue"
            }
        ]
    }
]

Examples

// Create schemas for the fields in the child row. 
List<FieldSchema> subFieldSchemas = new ArrayList<FieldSchema>();
subFieldSchemas.add(new FieldSchema("tagName", FieldType.KEYWORD)
    .setIndex(true).setEnableSortAndAgg(true));
subFieldSchemas.add(new FieldSchema("score", FieldType.DOUBLE)
    .setIndex(true).setEnableSortAndAgg(true));
subFieldSchemas.add(new FieldSchema("time", FieldType.DATE)
    .setDateFormats(Arrays.asList("epoch_millis")));

// Use the schemas created for the child rows as the value of subfieldSchemas for the Nested field. 
FieldSchema nestedFieldSchema = new FieldSchema("tags", FieldType.NESTED)
    .setSubFieldSchemas(subFieldSchemas);

// Create schemas for the three fields in the child rows of the address Nested field. The path specified by user.address can be used to query data of fields in a child row. 
List<FieldSchema> addressSubFiledSchemas = new ArrayList<>();
addressSubFiledSchemas.add(new FieldSchema("province",FieldType.KEYWORD));
addressSubFiledSchemas.add(new FieldSchema("city",FieldType.KEYWORD));
addressSubFiledSchemas.add(new FieldSchema("street",FieldType.KEYWORD));

// Create a schema for each child row of the Nested field user. Each child row contains three fields of different primitive data types and one Nested field named address. The path specified by the Nested field user can be used to query data of fields in a child row. 
List<FieldSchema> subFieldSchemas = new ArrayList<>();
subFieldSchemas.add(new FieldSchema("name",FieldType.KEYWORD));
subFieldSchemas.add(new FieldSchema("age",FieldType.LONG));
subFieldSchemas.add(new FieldSchema("birth",FieldType.DATE).setDateFormats(Arrays.asList("yyyy-MM-dd HH:mm:ss.SSS")));
subFieldSchemas.add(new FieldSchema("phone",FieldType.KEYWORD));
subFieldSchemas.add(new FieldSchema("address",FieldType.NESTED).setSubFieldSchemas(addressSubFiledSchemas));

// Use the schemas created for the child rows of the Nested field user as the value of subfieldSchemas for the Nested field. 
List<FieldSchema> fieldSchemas = new ArrayList<>();
fieldSchemas.add(new FieldSchema("user",FieldType.NESTED).setSubFieldSchemas(subFieldSchemas));