Tablestore:Array and nested 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, if the price field is a Double Array type, the price field in the data table must be of the String type. In the search index, the corresponding field must be of the Double type, and you must add the isArray=true property.

Examples

Assume that a data table is named array_search_table. The following table shows sample data.

The data table includes the primary key column pk and two attribute columns: col_keyword_array and col_long_array. All columns are of the String type.

1. Create a search index.

   Create a search index named array_query_table_index. The index includes two columns: col_keyword_array of the String Array type and col_long_array of the Long Array type.

   

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

   The following Java code shows how to query array data. The query conditions specify that the col_keyword_array column contains an element that is an exact match of "Cloud server" and the col_long_array column contains an element that is equal to 2024.

   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 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": "Zhang San",
        "age": 20,
        "phone": "1390000****",
        "address": [
            {
                "province": "Zhejiang Province",
                "city": "Hangzhou City",
                "street": "No. 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));