You can perform a nested query to query the data in the child rows of nested columns. Nested columns cannot be directly queried. To query a nested column, you must specify the path of the nested column and a subquery in a NestedQuery object. The subquery can be a query of any type.
Prerequisites
The OTSClient is initialized. For more information, see Initialize an OTSClient instance.
A data table is created. Data is written to the table.
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 column. The path is similar to the tree structure. For example, news.title indicates the title subcolumn in the nested column named news. |
query | The query that you want to perform on the subcolumn in the nested column. The query can be of any query type. |
scoreMode | Specifies which value is used to calculate the score when the column contains multiple values. |
getTotalCount | Specifies whether to return the total number of rows that meet the query conditions. Default value: false. If you set this parameter to true, the query performance is compromised. |
weight | The weight that you want to assign to the column 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 column that you want to query, the BM25-based keyword relevance score is higher for the row. 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 specify the returnAll and columns fields for this parameter. The default value of the returnAll field is false, which indicates that not all columns are returned. In this case, you can use the columns field 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 field to true, all columns are returned. |
Examples
The following examples show how to perform nested queries.
Single-level nested columns
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 parameter 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 GetTotalCount to true to return the total number of matched rows.
SearchRequest searchRequest = new SearchRequest("<TABLE_NAME>", "<SEARCH_INDEX_NAME>", searchQuery);
// You can use the columnsToGet parameter to specify the columns that you want to return or specify that all columns are returned. If you do not specify this parameter, only the primary key columns are returned.
//SearchRequest.ColumnsToGet columnsToGet = new SearchRequest.ColumnsToGet();
//columnsToGet.setReturnAll(true); // Set the ReturnAll parameter to true to return all columns.
//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());
}Multi-level nested columns
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 parameter 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 GetTotalCount to true to return the total number of matched rows.
SearchRequest searchRequest = new SearchRequest("<TABLE_NAME>", "<SEARCH_INDEX_NAME>", searchQuery);
// You can use the columnsToGet parameter to specify the columns that you want to return or specify that all columns are returned. If you do not specify this parameter, only the primary key columns are returned.
//SearchRequest.ColumnsToGet columnsToGet = new SearchRequest.ColumnsToGet();
//columnsToGet.setReturnAll(true); // Set the ReturnAll parameter to true to return all columns.
//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());
}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, Boolean query, KNN vector query, nested query, and exists query. 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 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.