Tablestore:Array and nested data types

Array formats

The following table describes the array formats for primitive data types in search indexes.

Usage notes

For Array-type fields in a search index, the corresponding field in the data table must be of the String type, and the field in the search index must be of the corresponding primitive type, such as Long or Double.

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, set the field type to Double and add the isArray=true property.

Examples

Assume that a data table named array_search_table contains the following sample data.

The data table includes the primary key column pk (String type) and two attribute columns: col_keyword_array (String type) and col_long_array (String type).

1. Create a search index.

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

   For Schema Generation Method, select Auto-generate. Then, in the field configuration table, verify that the Array toggle is enabled for the corresponding fields.

2. Query Array-type data by using the search index.

   The following Java code queries array data where the col_keyword_array column contains an element that exactly matches "Cloud server" and the col_long_array column contains an element equal to 2024.

   Note

   To query Array-type data in a search index by using SQL statements, see Execute SQL statements to query data using the search index.

   private static void query(SyncClient client) {
       // Condition 1: The col_keyword_array field contains an element that is an exact match for "ECS".
       TermQuery keywordTermQuery = new TermQuery(); 
       keywordTermQuery.setFieldName("col_keyword_array"); 
       keywordTermQuery.setTerm(ColumnValue.fromString("ECS")); 
       // Condition 2: The col_long_array field contains an element equal to 2024.
       TermQuery longTermQuery = new TermQuery(); 
       longTermQuery.setFieldName("col_long_array"); 
       longTermQuery.setTerm(ColumnValue.fromLong(2024l)); 
       SearchQuery searchQuery = new SearchQuery();
       // Require both conditions to be true.
       BoolQuery boolQuery = new BoolQuery();
       boolQuery.setMustQueries(Arrays.asList(keywordTermQuery, longTermQuery));
       searchQuery.setQuery(boolQuery);
       //searchQuery.setGetTotalCount(true); // Set to return the total number of matched rows.
       SearchRequest searchRequest = new SearchRequest("<TABLE_NAME>", "<SEARCH_INDEX_NAME>", searchQuery);
       // You can configure the columnsToGet parameter to specify the columns to return or return all columns. If this parameter is not configured, only primary key columns are returned by default.
       //SearchRequest.ColumnsToGet columnsToGet = new SearchRequest.ColumnsToGet();
       //columnsToGet.setReturnAll(true); // Set to return all columns.
       //columnsToGet.setColumns(Arrays.asList("ColName1","ColName2")); // Set to return specified columns.
       //searchRequest.setColumnsToGet(columnsToGet);
       SearchResponse resp = client.search(searchRequest);
       //System.out.println("TotalCount: " + resp.getTotalCount()); // Print the total number of matched rows, not the number of returned rows.
       System.out.println("Row: " + resp.getRows());
   }

Nested formats

Nested fields are classified into single-level and multi-level 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 FieldSchema objects for the fields in a 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")));
// Set the child row FieldSchema objects to the subFieldSchemas of the nested field.
FieldSchema nestedFieldSchema = new FieldSchema("tags", FieldType.NESTED)
    .setSubFieldSchemas(subFieldSchemas);

// Define subfield schemas for the 'address' nested field. The query path for these fields is user.address.
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));
// Define subfield schemas for the 'user' nested field. Each child row contains four primitive type fields and one nested field 'address'.
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));
// Add the 'user' nested field schema to the list of main field schemas.
List<FieldSchema> fieldSchemas = new ArrayList<>();
fieldSchemas.add(new FieldSchema("user",FieldType.NESTED).setSubFieldSchemas(subFieldSchemas));