Perform a nested query by using Tablestore SDK for Java - 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.

Prerequisites

An OTSClient instance is initialized. For more information, see Initialize an OTSClient instance.
A data table is created and data is written to the data table. For more information, see Create a data table and Write data.
A search index is created for the data table. For more information, see Create a search index.

Parameters

Parameter	Description
path	The path of the nested field. The path is similar to the tree structure. For example, news.title indicates 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 when 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. If you specify a higher weight for the field that you want to query, the BM25-based keyword relevance score for the field is higher. 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 when the nested field consists of multiple child rows. limit: the maximum number of child rows to return when 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.

Examples

The following examples show how to perform nested queries.

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("sampleTable", "sampleSearchIndex", 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(termQuery);

        // 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("sampleTable", "sampleSearchIndex", 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 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 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", 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 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();
    }
}

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, geo query, KNN vector query, Boolean query, nested query, and exists query. After you create a search index, you can use the query methods provided by the search index to query data from multiple dimensions based on your business requirements.
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 table, you can call the Search operation to use the aggregation feature or use the SQL query feature. For example, you can query the maximum and minimum values, the sum of the values, and the 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.