You can use `NestedQuery` to query data in the child rows of a nested field. You cannot query nested types directly. Instead, you must wrap the query in a `NestedQuery`. A `NestedQuery` requires the path of the nested field and a subquery, which can be of any query type.
Prerequisites
A Tablestore client is initialized. For more information, see Initialize a Tablestore client.
A data table is created. Data is written to the 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 name. This is the tree-like path of the nested field. For example, `news.title` indicates the `title` sub-column within the nested `news` field. |
query | The query on the sub-columns of the nested field. The sub-column query can be of any query type. |
scoreMode | The value to use for score calculation when the field has multiple values. |
getTotalCount | Specifies whether to return the total number of matched rows. The default value is `false`. Returning the total number of matched rows can affect query performance. |
weight | The query weight. This is used for score-based sorting in full-text index scenarios. This parameter specifies the weight for score calculation of a column during a query. A larger value results in a higher score in the results. The value must be a positive floating-point number. This parameter does not affect the number of returned results. It only affects the scores in the returned results. |
tableName | The name of the data table. |
indexName | The name of the search index. |
columnsToGet | Specifies whether to return all columns. This includes the `returnAll` and `columns` settings. The default value of `returnAll` is `false`, which means not all columns are returned. In this case, you can use `columns` to specify the columns to return. If you do not use `columns` to specify the columns, only the primary key columns are returned. If you set `returnAll` to `true`, all columns are returned. |
InnerHits | The configuration parameters for the sub-columns of the nested field. It includes the following configuration items:
|
Examples
The following examples show how to perform nested queries.
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
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();
}
}
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.