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.

Query 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 column is tablestore. In this example, the nested column named col_nested includes the nested_1 and nested_2 subcolumns.

private static void nestedQuery(SyncClient client) {
    SearchQuery searchQuery = new SearchQuery();
    NestedQuery nestedQuery = new NestedQuery(); // Set the query type 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 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 columns 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());
}

Query 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 column is tablestore. In this example, the nested column named col_nested includes the nested_1 and nested_2 subcolumns. The nested_2 subcolumn includes the nested_2_1 and nested_2_2 columns.

private static void nestedQuery(SyncClient client) {
    SearchQuery searchQuery = new SearchQuery();
    NestedQuery nestedQuery = new NestedQuery(); // Set the query type 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 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 columns 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());
}

Combine nested query with Boolean query

Query requirements

In this example, the data table consists of the col_string column of the String type and the col_nested column of the String type. The col_nested column stores data in the JSON format. The following table describes the sample rows in the data table.

Note

To help you better understand the demonstration, a serial number is added to each row.

Serial number	col_string	col_nested
1	a	[{"col_keyword": "tablestore"},{"col_keyword": "searchindex","col_long": 1}]
2	b	[{"col_keyword": "tablestore","col_long": 1}]
3	c	[{"col_keyword": "searchindex"},{"col_long": 1}]

For example, you have the following query requirements for the col_nested column:

Same child row meets multiple query conditions
For example, you want to query the rows in which the value of the col_keyword column is "tablestore" and the value of the col_long column is not empty. The col_keyword and col_long columns belong to the same child row of the col_nested column.
Different child rows meet multiple query conditions
For example, you want to query the rows in which the value of the col_keyword column is "tablestore" and the value of the col_long column is not empty. The col_keyword and col_long columns belong to the same child row or different child rows of the col_nested column.

To query the rows that meet the preceding requirements, perform the following steps:

Create a search index for the data table and set the type of the col_nested column in the search index to Nested.
The col_nested column consists of the following subfields: the col_keyword subfield of the Keyword type and the col_long subfield of the Long type.
Select a suitable query method based on the query requirements.
- If you want to meet the query requirement that the same child row meets multiple query conditions, you can specify multiple Boolean queries in a nested query.
- If you want to meet the requirement that different child rows meet multiple query conditions, you can specify multiple nested queries in a Boolean query.

The following sample code provides examples on how to query data based on your query requirements. Refer to the corresponding sample code based on your query requirements.

Same child row meets multiple query conditions

The following sample code provides an example on how to query the rows in which the value of the col_nested.col_keyword column is "tablestore" and the value of the col_nested.col_long column is not empty. The col_nested.col_keyword and col_nested.col_long columns belong to the same child row.

Based on the sample rows in the data table, only the row whose serial number is 2 meets the query conditions.

public static void nestedQuery(SyncClient client) {
    // Query condition 1: The value of the col_keyword column in the child row of the col_nested column is "tablestore".
    TermQuery termQuery = new TermQuery();
    termQuery.setFieldName("col_nested.col_keyword");
    termQuery.setTerm(ColumnValue.fromString("tablestore"));

    // Query condition 2: The value of the col_long column in the child row of the col_nested column is not empty.
    ExistsQuery existsQuery = new ExistsQuery();
    existsQuery.setFieldName("col_nested.col_long");

    // Use the And operator of Boolean query to query the rows whose child rows meet the preceding two query conditions at the same time.
    List<Query> mustQueries = new ArrayList<>();
    mustQueries.add(termQuery);
    mustQueries.add(existsQuery);
    BoolQuery boolQuery = new BoolQuery();
    boolQuery.setMustQueries(mustQueries);

    // Specify multiple Boolean queries in a nested query to query the rows whose child rows meet the preceding two query conditions at the same time.
    NestedQuery nestedQuery = new NestedQuery(); // Set the query type to NestedQuery. 
    nestedQuery.setPath("col_nested"); // Specify the path of the nested column, which is the parent path of the column that you want to query. 
    nestedQuery.setQuery(boolQuery);
    nestedQuery.setScoreMode(ScoreMode.None);

    SearchQuery searchQuery = new SearchQuery();
    searchQuery.setQuery(nestedQuery);

    SearchRequest searchRequest = new SearchRequest("<TABLE_NAME>", "<SEARCH_INDEX_NAME>", searchQuery);
    // You can configure the columnsToGet parameter to specify the columns 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());
}

Different child rows meet multiple query conditions

Based on the sample rows in the data table, the rows whose serial number is 1 and 2 meet the query conditions.

public static void nestedQuery(SyncClient client) {
        // Query condition 1: The value of the col_keyword column in the child row of the col_nested column is "tablestore".
        TermQuery termQuery = new TermQuery();
        termQuery.setFieldName("col_nested.col_keyword");
        termQuery.setTerm(ColumnValue.fromString("tablestore"));
        NestedQuery nestedTermQuery = new NestedQuery();
        nestedTermQuery.setPath("col_nested");
        nestedTermQuery.setScoreMode(ScoreMode.None);
        nestedTermQuery.setQuery(termQuery);

        // Query condition 2: The value of the col_long column in the child row of the col_nested column is not empty.
        ExistsQuery existsQuery = new ExistsQuery();
        existsQuery.setFieldName("col_nested.col_long");
        NestedQuery nestedExistsQuery = new NestedQuery();
        nestedExistsQuery.setPath("col_nested");
        nestedExistsQuery.setScoreMode(ScoreMode.None);
        nestedExistsQuery.setQuery(existsQuery);

        // Use the And operator of Boolean query to query the rows that meet the preceding two query conditions.
        List<Query> mustQueries = new ArrayList<>();
        mustQueries.add(nestedTermQuery);
        mustQueries.add(nestedExistsQuery);

        // Specify multiple nested queries in a Boolean query to query the rows whose child rows meet the query conditions.
        BoolQuery boolQuery = new BoolQuery();
        boolQuery.setMustQueries(mustQueries);

        SearchQuery searchQuery = new SearchQuery();
        searchQuery.setQuery(boolQuery);

        SearchRequest searchRequest = new SearchRequest("<TABLE_NAME>", "<SEARCH_INDEX_NAME>", searchQuery);
        // You can configure the columnsToGet parameter to specify the columns 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());
    }

Use the highlight feature in nested queries

The following sample code provides an example on how to use nested query to query the rows in which the value of the Level1_Col1_Nested subcolumn of the nested column named Col_Nested matches hangzhou shanghai and highlight the query strings in the query results.

/**
 * Enable the highlight feature by using the innerHits parameter for the nested query. 
 */
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.match("Col_Nested.Level1_Col1_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 text segments of the column in each row. 
        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 column. 
        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 Boolean query to the nested query to highlight the query strings in the Level1_Col1_Text field and the Level2_Col1_Text subfield of 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", HighlightParame
                                                            .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())
            .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 text segments of the field in each row. 
        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 column. 
        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

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.