Query data in the child rows of a nested field - Tablestore

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

Important

Only nested fields can be queried in nested queries.
You can perform queries on nested fields and other types of fields in a single request. For more information about the nested field type, see Nested data type.

API operation

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

Parameters

Parameter	Description
path	The path of the nested field. The path is similar to the tree structure. For example, news.title specifies the title subfield in the nested field named news.
query	The query that you want to perform on the subfield in the nested field. The query can be of any query type.
scoreMode	The value that is used to calculate the score if a field contains multiple values.
getTotalCount	Specifies whether to return the total number of rows that meet the query conditions. The default value of this parameter is false, which specifies that the total number of rows that meet the query conditions is not returned. If you set this parameter to true, the query performance is compromised.
weight	The weight that you want to assign to the field that you want to query to calculate the BM25-based keyword relevance score. This parameter is used in full-text search scenarios. A higher weight results in a higher BM25-based keyword relevance score for the field. 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 configure the returnAll and columns parameters for this parameter. The default value of the returnAll parameter is false, which specifies that not all columns are returned. In this case, you can use the columns parameter 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 parameter to true, all columns are returned.
InnerHits	The settings of the subfields of the nested field. sort: the sorting rule for the child rows of the nested field. offset: the start position of the child rows to return if the nested field consists of multiple child rows. limit: the maximum number of child rows to return if the nested field consists of multiple child rows. Default value: 3. highlight: the highlight settings for the subfields of the nested field. For more information, see Highlight.

Methods

You can use the Tablestore console or Tablestore SDKs to perform a nested query. Before you perform a nested query, make sure that the following preparations are made:

An Alibaba Cloud account or a RAM user that has Tablestore operation permissions is created. For information about how to grant Tablestore operation permissions to a RAM user, see Use a RAM policy to grant permissions to a RAM user.
If you want to use Tablestore SDKs to perform a nested query, an AccessKey pair is created for your Alibaba Cloud account or RAM user. For more information, see Create an AccessKey pair.
Warning
The AccessKey pair of an Alibaba Cloud account has permissions on all API operations. We recommend that you use a RAM user to call API operations or perform routine O&M. We recommend that you do not hard-code the AccessKey ID and AccessKey secret into your project code. Otherwise, the AccessKey pair may be leaked and the security of all resources in your account is compromised.
A data table is created. For more information, see Operations on a data table.
A search index is created for the data table. For more information, see Create a search index.
If you want to use Tablestore SDKs to perform a nested query, an OTSClient instance is initialized. For more information, see Initialize an OTSClient instance.

Use the Tablestore console

You can use the Tablestore console to query only data of single-level nested fields when you perform a nested query.

Go to the Indexes tab.
1. Log on to the Tablestore console.
2. In the top navigation bar, select a resource group and a region.
3. On the Overview page, click the name of the instance that you want to manage or click Manage Instance in the Actions column of the instance.
4. On the Tables tab of the Instance Details tab, click the name of the data table or click Indexes in the Actions column of the data table.
On the Indexes tab, find the search index that you want to use to query data and click Manage Data in the Actions column.
In the Search dialog box, specify the query conditions.
1. By default, the system returns all attribute columns. To return specific attribute columns, turn off All Columns and specify the attribute columns that you want to return. Separate multiple attribute columns with commas (,).
  Note
  By default, the system returns all primary key columns of the data table.
2. Select the And, Or, or Not logical operator based on your business requirements.
  If you select the And logical operator, data that meets the query conditions is returned. If you select the Or operator and specify a single query condition, data that meets the query condition is returned. If you select the Or logical operator and specify multiple query conditions, data that meets one of the query conditions is returned. If you select the Not logical operator, data that does not meet the query conditions is returned.
3. Select a field of the Nested type and click Add.
4. Select a subfield and set the Query Type parameter to the query type that you want to use to query data, such as TermQuery(TermQuery). Then, enter the value that you want to query.
5. By default, the sorting feature is disabled. If you want to sort the query results based on specific fields, turn on Sort and specify the fields based on which you want to sort the query results and the sorting order.
6. By default, the aggregation feature is disabled. If you want to collect statistics on a specific field, turn on Collect Statistics, specify the field based on which you want to collect statistics, and then configure the information that is required to collect statistics.
Click OK.
Data that meets the query conditions is displayed in the specified order on the Indexes tab.

Use Tablestore SDKs

You can use Tablestore SDKs to query data of single-level and multi-level nested fields. When you perform a nested query, you can use the highlight feature to highlight the query strings in the query results. For more information, see Highlight.

You can use the following Tablestore SDKs to perform a nested query: Tablestore SDK for Java, Tablestore SDK for Go, Tablestore SDK for Python, Tablestore SDK for Node.js, Tablestore SDK for .NET, and Tablestore SDK for PHP. In this example, Tablestore SDK for Java is used.

Single-level Nested fields

The following sample code provides an example on how to query the rows in which the value of the col_nested.nested_1 field is tablestore. In this example, the Nested field named col_nested consists of the nested_1 and nested_2 subfields.

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 field. 
    TermQuery termQuery = new TermQuery(); // Specify a subquery to perform the nested query. 
    termQuery.setFieldName("col_nested.nested_1"); // Specify the name of the field. The name must include the path of the Nested field. 
    termQuery.setTerm(ColumnValue.fromString("tablestore")); // Specify the value that you want to use to match the field value. 
    nestedQuery.setQuery(termQuery);
    nestedQuery.setScoreMode(ScoreMode.None);
    searchQuery.setQuery(nestedQuery);
    //searchQuery.setGetTotalCount(true);// Set the GetTotalCount parameter to true 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 fields that you want to return or specify whether to return all fields. If you leave this parameter empty, only the primary key fields are returned. 
    //SearchRequest.ColumnsToGet columnsToGet = new SearchRequest.ColumnsToGet();
    //columnsToGet.setReturnAll(true); // Specify that all fields are returned. 
    //columnsToGet.setColumns(Arrays.asList("ColName1","ColName2")); // Specify the fields 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());
}

Multi-level Nested fields

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 field is tablestore. In this example, the Nested field named col_nested consists of the nested_1 and nested_2 subfields. The nested_2 subfield consists of the nested_2_1 and nested_2_2 fields.

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 field, which is the parent path of the field 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 field. The name must include the path of the Nested field. 
    termQuery.setTerm(ColumnValue.fromString("tablestore")); // Specify the value that you want to use to match the field value. 
    nestedQuery.setQuery(termQuery);
    nestedQuery.setScoreMode(ScoreMode.None);
    searchQuery.setQuery(nestedQuery);
    //searchQuery.setGetTotalCount(true);// Set the GetTotalCount parameter to true 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 fields that you want to return or specify whether to return all fields. If you leave this parameter empty, only the primary key fields are returned. 
    //SearchRequest.ColumnsToGet columnsToGet = new SearchRequest.ColumnsToGet();
    //columnsToGet.setReturnAll(true); // Specify that all fields are returned. 
    //columnsToGet.setColumns(Arrays.asList("ColName1","ColName2")); // Specify the fields 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 fields with the highlight feature enabled

The following sample code provides an example on how to use the NestedQuery feature to query rows whose data matches hangzhou shanghai from the Level1_Col1_Nested subfield of the Col_Nested field. Keywords in the query results are also highlighted.

/**
 * Enable the highlight feature by using the innerHits parameter for the nested query. 
 */
public static void NestedQueryQueryWithHighlighting1(SyncClient client) {
        SearchRequest searchRequest = SearchRequest.newBuilder()
                .tableName("<TABLE_NAME>")
                .indexName("<SEARCH_INDEX_NAME>")
                .returnAllColumnsFromIndex(true)
                .searchQuery(SearchQuery.newBuilder()
                        .limit(5)
                        .query(QueryBuilders.nested()
                                .path("Col_Nested")
                                .scoreMode(ScoreMode.Min)
                                .query(QueryBuilders.match("Col_Nested.Level1_Colqia1_Nested", "hangzhou shanghai"))
                                .innerHits(InnerHits.newBuilder()
                                        .highlight(Highlight.newBuilder()
                                                .addFieldHighlightParam("Col_Nested.Level1_Col1_Nested", HighlightParameter.newBuilder().build())
                                                .build())
                                        .build()))
                        .build())
                .build();
        SearchResponse resp = client.search(searchRequest);

        // Display the highlighted results. 
        printSearchHit(resp.getSearchHits(), "");
}

/**
 * Display the content that meets the query conditions. 
 * @param searchHits searchHits
 * If the output uses the @param prefix Nested structure, add the prefix to display the hierarchy information. 
 */
private static void printSearchHit(List<SearchHit> searchHits, String prefix) {
    for (SearchHit searchHit : searchHits) {
        if (searchHit.getScore() != null) {
            System.out.printf("%s Score: %s\n", prefix, searchHit.getScore());
        }

        if (searchHit.getOffset() != null) {
            System.out.printf("%s Offset: %s\n", prefix, searchHit.getOffset());
        }

        if (searchHit.getRow() != null) {
            System.out.printf("%s Row: %s\n", prefix, searchHit.getRow().toString());
        }

        // Display the highlighted fragments for each field. 
        if (searchHit.getHighlightResultItem() != null) {
            System.out.printf("%s Highlight: \n", prefix);
            StringBuilder strBuilder = new StringBuilder();
            for (Map.Entry<String, HighlightField> entry : searchHit.getHighlightResultItem().getHighlightFields().entrySet()) {
                strBuilder.append(entry.getKey()).append(":").append("[");
                strBuilder.append(StringUtils.join(",", entry.getValue().getFragments())).append("]\n");
            }
            System.out.printf("%s   %s", prefix, strBuilder);
        }

        // The highlighted results of the Nested field. 
        for (SearchInnerHit searchInnerHit : searchHit.getSearchInnerHits().values()) {
            System.out.printf("%s Path: %s\n", prefix, searchInnerHit.getPath());
            System.out.printf("%s InnerHit: \n", prefix);
            printSearchHit(searchInnerHit.getSubSearchHits(), prefix + "    ");
        }

        System.out.println();
    }
}

For example, the Col_Nested field consists of the following subfields: the Level1_Col1_Text subfield of the Text type and the Level1_Col2_Nested subfield of the Nested type. The Level1_Col2_Nested subfield of the Nested type also consists of the Level2_Col1_Text field.

The following sample code provides an example on how to add a bool query to the nested query to highlight the query content in the Level1_Col1_Text field and the Level2_Col1_Text subfield under the Level1_Col2_Nested field.

public static void NestedQueryWithHighlighting(SyncClient client) {
    SearchRequest searchRequest = SearchRequest.newBuilder()
        .tableName("<TABLE_NAME>")
        .indexName("<SEARCH_INDEX_NAME>")
        .returnAllColumnsFromIndex(true)
        .searchQuery(SearchQuery.newBuilder()
            .limit(5)
            .query(QueryBuilders.nested()
                .path("Col_Nested")
                .scoreMode(ScoreMode.Min)
                .query(QueryBuilders.bool()
                    .should(QueryBuilders.match("Col_Nested.Level1_Col1_Text", "hangzhou shanghai"))
                    .should(QueryBuilders.nested()
                        .path("Col_Nested.Level1_Col2_Nested")
                        .scoreMode(ScoreMode.Min)
                        .query(QueryBuilders.match("Col_Nested.Level1_Col2_Nested.Level2_Col1_Text", "hangzhou shanghai"))
                        .innerHits(InnerHits.newBuilder()
                            .highlight(Highlight.newBuilder()
                                .addFieldHighlightParam("Col_Nested.Level1_Col2_Nested.Level2_Col1_Text", HighlightParameter.newBuilder().build())
                                .build())
                            .build())))
                .innerHits(InnerHits.newBuilder()
                    .sort(new Sort(Arrays.asList(
                        new ScoreSort(),
                        new DocSort()
                    )))
                    .highlight(Highlight.newBuilder()
                        .addFieldHighlightParam("Col_Nested.Level1_Col1_Text", HighlightParameter.newBuilder().build())
                        .build())
                .build())))
        .build();
    SearchResponse resp = client.search(searchRequest);

    // Display the highlighted results. 
    printSearchHit(resp.getSearchHits(), "");
}

/**
 * Display the content that meets the query conditions. 
 * @param searchHits searchHits
 * If the output uses the @param prefix Nested structure, add the prefix to display the hierarchy information. 
 */
private static void printSearchHit(List<SearchHit> searchHits, String prefix) {
    for (SearchHit searchHit : searchHits) {
        if (searchHit.getScore() != null) {
            System.out.printf("%s Score: %s\n", prefix, searchHit.getScore());
        }

        if (searchHit.getOffset() != null) {
            System.out.printf("%s Offset: %s\n", prefix, searchHit.getOffset());
        }

        if (searchHit.getRow() != null) {
            System.out.printf("%s Row: %s\n", prefix, searchHit.getRow().toString());
        }

        // Display the highlighted items for each field. 
        if (searchHit.getHighlightResultItem() != null) {
            System.out.printf("%s Highlight: \n", prefix);
            StringBuilder strBuilder = new StringBuilder();
            for (Map.Entry<String, HighlightField> entry : searchHit.getHighlightResultItem().getHighlightFields().entrySet()) {
                strBuilder.append(entry.getKey()).append(":").append("[");
                strBuilder.append(StringUtils.join(",", entry.getValue().getFragments())).append("]\n");
            }
            System.out.printf("%s   %s", prefix, strBuilder);
        }

        // The highlighted results of the Nested field. 
        for (SearchInnerHit searchInnerHit : searchHit.getSearchInnerHits().values()) {
            System.out.printf("%s Path: %s\n", prefix, searchInnerHit.getPath());
            System.out.printf("%s InnerHit: \n", prefix);
            printSearchHit(searchInnerHit.getSubSearchHits(), prefix + "    ");
        }

        System.out.println();
    }
}

Billing rules for

When you use a search index to query data, you are charged for the read throughput that is consumed. For more information, see Billable items of search indexes.

FAQ

References

When you use a search index to query data, you can use the following query methods: term query, terms query, match all query, match query, match phrase query, prefix query, range query, wildcard query, fuzzy query, Boolean query, geo query, nested query, KNN vector query, and exists query. You can select query methods based on your business requirements to query data from multiple dimensions.
You can sort or paginate rows that meet the query conditions by using the sorting and paging features. For more information, see Perform sorting and paging.
You can use the collapse (distinct) feature to collapse the result set based on a specific column. This way, data of the specified type appears only once in the query results. For more information, see Collapse (distinct).
If you want to analyze data in a data table, you can use the aggregation feature of the Search operation or execute SQL statements. For example, you can obtain the minimum and maximum values, sum, and total number of rows. For more information, see Aggregation and SQL query.
If you want to obtain all rows that meet the query conditions without the need to sort the rows, you can call the ParallelScan and ComputeSplits operations to use the parallel scan feature. For more information, see Parallel scan.