Data in the child rows of a nested column - Tablestore - Alibaba Cloud Documentation Center

You can perform a nested query to query the data in the child rows of nested columns. Nested columns cannot be directly queried. To query a nested column, you must specify the path of the nested column and a subquery in a NestedQuery object. The subquery can be a query of any type.

Note

Only nested columns can be queried in nested queries.
However, you can perform queries on nested columns and other types of columns in a single request. For more information about the nested type, see Nested query.

API operation

You can call the Search or ParallelScan operation and set the query type to NestedQuery to perform a nested query.

Use Tablestore SDKs

You can use the following Tablestore SDKs to perform a nested query:

Tablestore SDK for Java: Nested query
Tablestore SDK for Go: Nested query
Tablestore SDK for Python: Nested query
Tablestore SDK for Node.js: Nested query
Tablestore SDK for .NET: Nested query
Tablestore SDK for PHP: Nested query

Parameters

Parameter	Description
path	The path of the nested column. The path is similar to the tree structure. For example, news.title indicates the title subcolumn in the nested column named news.
query	The query that you want to perform on the subcolumn in the nested column. The query can be of any query type.
scoreMode	Specifies which value is used to calculate the score when the column contains multiple values.
getTotalCount	Specifies whether to return the total number of rows that meet the query conditions. Default value: false. If you set this parameter to true, the query performance is compromised.
weight	The weight that you want to assign to the column that you want to query to calculate the BM25-based keyword relevance score. This parameter is used in full-text search scenarios. If you specify a higher weight for the column that you want to query, the BM25-based keyword relevance score is higher for the row. The value of this parameter is a positive floating point number. This parameter does not affect the number of rows that are returned. However, this parameter affects the BM25-based keyword relevance scores of the query results.
tableName	The name of the data table.
indexName	The name of the search index.
columnsToGet	Specifies whether to return all columns of each row that meets the query conditions. You can specify the returnAll and columns fields for this parameter. The default value of the returnAll field is false, which indicates that not all columns are returned. In this case, you can use the columns field to specify the columns that you want to return. If you do not specify the columns that you want to return, only the primary key columns are returned. If you set the returnAll field to true, all columns are returned.

Examples

The following examples show how to perform a nested query:

Example 1

The following sample code provides an example on how to query the rows in which the value of the col_nested.nested_1 column is tablestore. In this example, the nested column named col_nested includes two subcolumns: nested_1 and nested_2.

private static void nestedQuery(SyncClient client) {
    SearchQuery searchQuery = new SearchQuery();
    NestedQuery nestedQuery = new NestedQuery(); // Set the query parameter to NestedQuery. 
    nestedQuery.setPath("col_nested"); // Specify the path of the nested column. 
    TermQuery termQuery = new TermQuery(); // Specify a subquery to perform the nested query. 
    termQuery.setFieldName("col_nested.nested_1"); // Specify the name of the column. The name must include the path of the nested column. 
    termQuery.setTerm(ColumnValue.fromString("tablestore")); // Specify the value that you want to use to match the column value. 
    nestedQuery.setQuery(termQuery);
    nestedQuery.setScoreMode(ScoreMode.None);
    searchQuery.setQuery(nestedQuery);
    //searchQuery.setGetTotalCount(true);// Set GetTotalCount to true to return the total number of matched rows. 

    SearchRequest searchRequest = new SearchRequest("sampleTable", "sampleSearchIndex", searchQuery);
    // You can use the columnsToGet parameter to specify the columns that you want to return or specify that all columns are returned. If you do not specify 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 rows that meet the query conditions instead of the number of returned rows is displayed. 
    System.out.println("Row: " + resp.getRows());
}

Example 2

The following sample code provides an example on how to query the rows in which the value of the col_nested.nested_2.nested_2_2 column is tablestore. In this example, the nested column named col_nested includes two subcolumns: nested_1 and nested_2. The nested_2 subcolumn includes two columns: nested_2_1 and nested_2_2.

private static void nestedQuery(SyncClient client) {
    SearchQuery searchQuery = new SearchQuery();
    NestedQuery nestedQuery = new NestedQuery(); // Set the query parameter to NestedQuery. 
    nestedQuery.setPath("col_nested.nested_2"); // Specify the path of the nested column, which is the parent path of the column that you want to query. 
    TermQuery termQuery = new TermQuery(); // Specify a subquery to perform the nested query. 
    termQuery.setFieldName("col_nested.nested_2.nested_2_2"); // Specify the name of the column. The name must include the path of the nested columns. 
    termQuery.setTerm(ColumnValue.fromString("tablestore")); // Specify the value that you want to use to match the column value. 
    nestedQuery.setQuery(termQuery);
    nestedQuery.setScoreMode(ScoreMode.None);
    searchQuery.setQuery(nestedQuery);
    //searchQuery.setGetTotalCount(true);// Set GetTotalCount to true to return the total number of matched rows. 

    SearchRequest searchRequest = new SearchRequest("sampleTable", "sampleSearchIndex", searchQuery);
    // You can use the columnsToGet parameter to specify the columns that you want to return or specify that all columns are returned. If you do not specify 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());
}