Query data in child rows of a nested type 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 Index Management tab.
1. Log on to the Table Store console.
2. In the top navigation bar, select a resource group and a region.
3. On the Overview page, click the instance name or click Instance Management in the Actions column.
4. On the Instance Details tab, in the Data Table List tab, click the data table name or click Index Management in the Actions column.
On the Index Management tab, find the target Search Index and click Search in the Actions column.
In the Search dialog box, query data.
1. By default, all columns are returned. To return specific columns, turn off Retrieve All Columns and enter the column names, separated by commas.
  Note
  By default, Table Store returns the primary key columns of the data table.
2. Select a logical operator: And, Or, or Not.
  If you select And, the query returns data that meets all specified conditions. If you select Or, the query returns data that meets at least one of the specified conditions. If you select Not, the query returns data that does not meet the specified conditions.
3. Select a nested type field and click Add.
4. Select a subfield, set the query type to your desired type, such as TermQuery, and enter a value to query.
5. By default, sorting is disabled. To sort the results by a specific field, turn on Enable Sorting, add the sort field, and configure the sort order.
6. By default, aggregation is disabled. To perform statistical aggregation on a specific field, turn on Enable Aggregation, add the field for aggregation, and configure the aggregation settings.
Click OK.
The query results are displayed on the Index Management tab.

Use Tablestore SDKs

You can use an SDK to query data in single-level and multi-level nested type fields. When you perform a nested query, you can use the highlight feature to highlight keywords in the results. For more information about highlighting, 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.

Example of a single-level nested query

The following example shows how to query for data where `col_nested.nested_1` is `tablestore`. In this example, `col_nested` is a nested field whose child rows contain the `nested_1` and `nested_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"); // Set the path of the nested column.
    TermQuery termQuery = new TermQuery(); // Construct the subquery for NestedQuery.
    termQuery.setFieldName("col_nested.nested_1"); // Set the column name. Note that it includes the path of the nested column.
    termQuery.setTerm(ColumnValue.fromString("tablestore")); // Set the value to query.
    nestedQuery.setQuery(termQuery);
    nestedQuery.setScoreMode(ScoreMode.None);
    searchQuery.setQuery(nestedQuery);
    //searchQuery.setGetTotalCount(true); // Set this to return the total number of matched rows.

    SearchRequest searchRequest = new SearchRequest("<TABLE_NAME>", "<SEARCH_INDEX_NAME>", searchQuery);
    // Use the columnsToGet parameter to specify the columns to return or to return all columns. If you do not set this parameter, only the primary key columns are returned by default.
    //SearchRequest.ColumnsToGet columnsToGet = new SearchRequest.ColumnsToGet();
    //columnsToGet.setReturnAll(true); // Set this to return all columns.
    //columnsToGet.setColumns(Arrays.asList("ColName1","ColName2")); // Set this 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());
}

Example of a multi-level nested query

The following example shows how to query for data where `col_nested.nested_2.nested_2_2` is `tablestore`. In this example, `col_nested` is a nested field whose child rows contain the `nested_1` and `nested_2` columns. The `nested_2` column is also a nested field whose child rows contain 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"); // Set the path of the nested column, which is the parent path of the field to query.
    TermQuery termQuery = new TermQuery(); // Construct the subquery for NestedQuery.
    termQuery.setFieldName("col_nested.nested_2.nested_2_2"); // Set the column name, which is the full path of the field to query.
    termQuery.setTerm(ColumnValue.fromString("tablestore")); // Set the value to query.
    nestedQuery.setQuery(termQuery);
    nestedQuery.setScoreMode(ScoreMode.None);
    searchQuery.setQuery(nestedQuery);
    //searchQuery.setGetTotalCount(true); // Set this to return the total number of matched rows.

    SearchRequest searchRequest = new SearchRequest("<TABLE_NAME>", "<SEARCH_INDEX_NAME>", searchQuery);
    // Use the columnsToGet parameter to specify the columns to return or to return all columns. If you do not set this parameter, only the primary key columns are returned by default.
    //SearchRequest.ColumnsToGet columnsToGet = new SearchRequest.ColumnsToGet();
    //columnsToGet.setReturnAll(true); // Set this to return all columns.
    //columnsToGet.setColumns(Arrays.asList("ColName1","ColName2")); // Set this 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());
}

Example of a composite nested query

Query requirements

Assume that a data table has two columns: `col_string` (String) and `col_nested` (String). The `col_nested` column stores data in JSON format. The following table shows sample data from this data table.

Note

A row number column is added to each row for demonstration purposes.

Row 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}]

Assume that you have the following query requirements for the data in the `col_nested` column:

A single child row meets multiple query conditions

For example, you can query for rows where a single child row in the `col_nested` column meets both of the following conditions: the value of the `col_keyword` column is `tablestore` and the value of the `col_long` column is not empty.
Different child rows meet multiple query conditions

For example, you can query for rows where one child row in the `col_nested` column has a `col_keyword` value of `tablestore`, and another child row has a `col_long` value that is not empty.

To meet these query requirements, you can perform the following steps:

Create a search index for the data table and set the `col_nested` column as a nested type in the search index.

The `col_nested` column contains two sub-fields: `col_keyword` (Keyword) and `col_long` (Long).
Use the appropriate query method based on your requirements.
- To perform a query where a single child row satisfies multiple query conditions, configure multiple BoolQueries within a NestedQuery.
- To query for different child rows that meet multiple conditions, you can set multiple `NestedQuery` conditions under a `BoolQuery`.

The following examples show how to query data. You can refer to the example that matches your query requirements.

Example of a query where a single child row meets multiple conditions

The following example shows how to query for rows where a single child row in the `col_nested` column meets both of these conditions: `col_nested.col_keyword is "tablestore" and `col_nested.col_long is not empty.

Based on the sample data, only the data in row 2 meets the query conditions.

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

    // Condition 2: The col_long column in the child row of col_nested must not be empty.
    ExistsQuery existsQuery = new ExistsQuery();
    existsQuery.setFieldName("col_nested.col_long");

    // Use the AND condition of BoolQuery to query for child rows that meet both conditions.
    List<Query> mustQueries = new ArrayList<>();
    mustQueries.add(termQuery);
    mustQueries.add(existsQuery);
    BoolQuery boolQuery = new BoolQuery();
    boolQuery.setMustQueries(mustQueries);

    // Set a BoolQuery within the NestedQuery to require a child row to meet multiple query conditions at the same time.
    NestedQuery nestedQuery = new NestedQuery();    // Set the query type to NestedQuery.
    nestedQuery.setPath("col_nested");   // Set the path of the nested column, which is the parent path of the field 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);
    // Use the columnsToGet parameter to specify the columns to return or to return all columns. If you do not set this parameter, only the primary key columns are returned by default.
    //SearchRequest.ColumnsToGet columnsToGet = new SearchRequest.ColumnsToGet();
    //columnsToGet.setReturnAll(true); // Set this to return all columns.
    //columnsToGet.setColumns(Arrays.asList("ColName1","ColName2")); // Set this 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());
}

Example of a query where different child rows meet multiple conditions

The following example queries for rows where nested objects in the `col_nested` field meet two conditions: col_nested.col_keyword is "tablestore" and col_nested.col_long is not empty.

Based on the sample data, the data in row 1 and row 2 meet the query conditions.

public static void nestedQuery(SyncClient client) {
        // Condition 1: The value of the col_keyword column in the child row of col_nested must be "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);

        // Condition 2: The col_long column in the child row of col_nested must not be 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 condition of BoolQuery to query for rows that meet the preceding conditions.
        List<Query> mustQueries = new ArrayList<>();
        mustQueries.add(nestedTermQuery);
        mustQueries.add(nestedExistsQuery);

        // The BoolQuery includes multiple NestedQuery conditions. The query is successful if different child rows meet the respective 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);
        // Use the columnsToGet parameter to specify the columns to return or to return all columns. If you do not set this parameter, only the primary key columns are returned by default.
        //SearchRequest.ColumnsToGet columnsToGet = new SearchRequest.ColumnsToGet();
        //columnsToGet.setReturnAll(true); // Set this to return all columns.
        //columnsToGet.setColumns(Arrays.asList("ColName1","ColName2")); // Set this 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());
    }

Example of using summary and highlighting in a nested query

The following example shows how to use `NestedQuery` to query for data where the value of the `Level1_Col1_Nested` sub-field in the `Col_Nested` nested field matches `hangzhou shanghai. The search query is highlighted in the returned results.

/**
 * Use summary and highlighting in a NestedQuery. Set parameters using innerHits.
 */
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);

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

/**
 * Print the content of searchHit.
 * @param searchHits The search hits.
 * @param prefix The prefix to add when printing nested structures to show hierarchical 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());
        }

        // Print 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);
        }

        // Highlighted results for the nested type.
        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();
    }
}

Assume that the multi-level nested field `Col_Nested` includes two sub-fields: `Level1_Col1_Text` (Text) and `Level1_Col2_Nested` (Nested). The `Level1_Col2_Nested` nested field includes the `Level2_Col1_Text` sub-field.

The following example shows how to add a `BoolQuery` to a `NestedQuery` to use the summary and highlighting features on both the `Level1_Col1_Text` sub-field in the `Col_Nested` field and the `Level2_Col1_Text` sub-field under `Level1_Col2_Nested`.

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())
            .build();
    SearchResponse resp = client.search(searchRequest);
    // Print the highlighted results.
    printSearchHit(resp.getSearchHits(), "");
}

/**
 * Print the content of searchHit.
 * @param searchHits The search hits.
 * @param prefix The prefix to add when printing nested structures to show hierarchical 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());
        }

        // Print 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);
        }

        // Highlighted results for the nested type.
        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

Querying data by using a Search Index consumes read throughput. For more information, see Search Index metering and billing.

FAQ

References

Search Index supports various query types for multi-dimensional data queries, including term query, terms query, match all query, match query, phrase match query, range query, prefix query, suffix query, wildcard query, token-based wildcard query, boolean query, geo query, nested query, vector search, and exists query.
When you query data, you can sort and paginate the result set or perform collapsing (deduplication).
For data analysis, such as finding the maximum or minimum value, calculating a sum, or counting rows, you can use the statistical aggregation or SQL query features.
To quickly export data regardless of the result set order, you can use the Parallel Scan feature.