Nested query - Tablestore SDK for Java - Tablestore - Alibaba Cloud Documentation Center

Prerequisites

Install the Tablestore SDK for Java and initialize a client.

Feature description

A nested query queries child rows in a Nested field. Each child row in a Nested field independently preserves the relationships between its fields. You cannot directly query the subfields of a Nested field. Instead, wrap the subquery in a NestedQuery object.

NestedQuery.path specifies the path of the nested field to query. Field names in the subquery must use full paths. The subquery can be any Query type. To query a multi-level nested field, set path directly to the full path of the target nested field or nest NestedQuery objects to query each level.

Whether multiple conditions must be met by the same child row depends on how NestedQuery and BoolQuery are combined:

To require the same child row to meet multiple conditions, set a BoolQuery that contains the child conditions as the subquery of one NestedQuery.
To allow different child rows to meet the conditions separately, create one NestedQuery for each condition and combine the nested queries in an outer BoolQuery.

Call search to perform a nested query. In the query condition, specify the nested field path, subquery, and score mode.

SearchResponse search(SearchRequest request)

The following example queries child rows in the items nested field whose items.keyword field is equal to tablestore. The query returns up to 10 rows and the total number of matches.

String tableName = "example_table";
String indexName = "example_index";

TermQuery termQuery = new TermQuery();
termQuery.setFieldName("items.keyword");
termQuery.setTerm(ColumnValue.fromString("tablestore"));

NestedQuery nestedQuery = new NestedQuery();
nestedQuery.setPath("items");
nestedQuery.setQuery(termQuery);
nestedQuery.setScoreMode(ScoreMode.None);

SearchQuery searchQuery = new SearchQuery();
searchQuery.setQuery(nestedQuery);
searchQuery.setLimit(10);
searchQuery.setTrackTotalCount(SearchQuery.TRACK_TOTAL_COUNT);

SearchRequest request = new SearchRequest(tableName, indexName, searchQuery);
SearchResponse response = client.search(request);
System.out.println(response.getTotalCount());
System.out.println(response.getRows());

Parameters

Search request

request is a SearchRequest object that contains the following parameters.

Name	Type	Description
tableName (required)	String	The name of the data table.
indexName (required)	String	The name of the search index.
searchQuery (required)	SearchQuery	The query condition and general query settings.
columnsToGet (optional)	SearchRequest.ColumnsToGet	The columns to return. If this parameter is not configured, only primary key columns are returned.
timeoutInMillisecond (optional)	int	The request-level query timeout in milliseconds. The default value is `-1`, which does not configure a separate query timeout.
routingValues (optional)	`List<PrimaryKey>`	The primary key values that correspond to custom routing fields. Leave this parameter unset if custom routing is not configured.

Query settings

request.searchQuery is a SearchQuery object that contains the following parameters.

Name	Type	Description
query (required)	Query	The query condition. Set this parameter to a `NestedQuery` object for a nested query.
offset (optional)	Integer	The starting position of the query.
limit (optional)	Integer	The maximum number of rows to return. Set this parameter to `0` to return no rows.
collapse (optional)	Collapse	The field collapse settings, which deduplicate results by a specified field. For configuration details, see Collapse query results.
sort (optional)	Sort	The result sort order. For configuration details, see Sort and paginate results.
trackTotalCount (optional)	int	The expected maximum number of matching rows to count. The default value is `TRACK_TOTAL_COUNT_DISABLED`, which disables counting. Set this parameter to `TRACK_TOTAL_COUNT` to count all matching rows. A smaller value improves query performance.
filter (optional)	SearchFilter	A filter that is applied to the results of `query`.
aggregationList (optional)	`List<Aggregation>`	The aggregation settings. For configuration details, see Aggregation.
groupByList (optional)	`List<GroupBy>`	The grouping settings. For configuration details, see Aggregation.
token (optional)	byte[]	The pagination token. Set this parameter to the `nextToken` value from the previous response to continue reading rows. When you set `token`, the SDK clears `sort` because the token already contains the sort conditions.

Nested query condition

request.searchQuery.query is a NestedQuery object that contains the following parameters.

Name	Type	Description
path (required)	String	The path of the nested field to query. For a multi-level nested field, set this parameter to the full path of the target nested field, such as `items.details`.
query (required)	Query	The query condition to execute on child rows under `path`. The condition can be any `Query` type. Specify a subfield by using its full path, such as `items.keyword`.
scoreMode (required)	ScoreMode	The parent-row scoring mode when multiple child rows match. `None` disables relevance scoring for child rows. `Avg`, `Max`, `Min`, and `Total` use the average, maximum, minimum, and sum of child-row scores.
innerHits (optional)	InnerHits	The settings for returning, sorting, paginating, and highlighting matching child rows. If you omit this parameter, details about matching child rows are not returned.
weight (optional)	float	The query weight. The default value is `1.0` and the value must be a positive floating-point number. A larger value increases the scores of matching rows but does not change which rows match.

Child-row return settings

request.searchQuery.query.innerHits is an InnerHits object that contains the following parameters.

Name	Type	Description
sort (optional)	Sort	The sort order of matching child rows. You can use `ScoreSort` and `DocSort`. `FieldSort` is not supported.
offset (optional)	Integer	The starting position from which to return matching child rows.
limit (optional)	Integer	The maximum number of matching child rows to return. The default value is `3`.
highlight (optional)	Highlight	The highlight settings for matching child rows. For information about fields and parameters that support highlighting, see Summary and highlighting.

Returned columns

request.columnsToGet is a SearchRequest.ColumnsToGet object that contains the following parameters.

Name	Type	Description
columns (optional)	`List<String>`	The attribute columns to return. Set this parameter only if `returnAll` and `returnAllFromIndex` are both `false`. If you omit this parameter, only primary key columns are returned.
returnAll (optional)	boolean	Specifies whether to return all attribute columns from the data table. The default value is `false`.
returnAllFromIndex (optional)	boolean	Specifies whether to return all indexed attribute columns. The default value is `false`. Do not set both `returnAll` and `returnAllFromIndex` to `true`.

Return values

Search response

search returns a SearchResponse object. The following table describes the core fields.

Name	Type	Description
totalCount	long	The number of matching rows. Call `getTotalCount()` to obtain the value. The returned value depends on the `trackTotalCount` setting.
rows	`List<Row>`	The rows returned by this query. Call `getRows()` to obtain the value. The number of rows does not exceed `limit`.
searchHits	`List<SearchHit>`	The query hits. Call `getSearchHits()` to obtain the value. If `innerHits` is configured, read matching child rows from this field.
nextToken	byte[]	The next-page token. Call `getNextToken()` to obtain the value. If the value is not `null`, set it as `token` in the next request to continue reading rows.
isAllSuccess	boolean	Indicates whether all index partitions were queried successfully. Call `isAllSuccess()` to obtain the value. If the value is `false`, the response contains partial results and `totalCount` may be less than the actual number of matching rows.

Search hit

response.searchHits[] is a SearchHit object that contains the following core fields.

Name	Type	Description
row	Row	The matching row or child row. Call `getRow()` to obtain the value.
score	Double	The relevance score. Call `getScore()` to obtain the value.
offset	Integer	The position of a nested child row in the original array. Call `getOffset()` to obtain the value. This field can be empty in a parent-row hit.
highlightResultItem	HighlightResultItem	The highlight result. Call `getHighlightResultItem()` to obtain the value.
searchInnerHits	`Map<String, SearchInnerHit>`	The matching child rows grouped by nested field path. Call `getSearchInnerHits()` to obtain the map, or call `getSearchInnerHitByPath(path)` to obtain the result for a specific path.

Nested hit

response.searchHits[].searchInnerHits contains SearchInnerHit values with the following fields.

Name	Type	Description
path	String	The nested field path. Call `getPath()` to obtain the value.
subSearchHits	`List<SearchHit>`	The matching child rows. Call `getSubSearchHits()` to obtain the value. In a multi-level nested query, `searchInnerHits` in a child-row hit can contain matching rows from the next level.

Scenario examples

Query a multi-level nested field

To query a multi-level nested field, set path to the full path of the target nested field and specify the full subfield path in the subquery. The following example queries rows in which items.details.name is equal to beta.

TermQuery termQuery = new TermQuery();
termQuery.setFieldName("items.details.name");
termQuery.setTerm(ColumnValue.fromString("beta"));

NestedQuery nestedQuery = new NestedQuery();
nestedQuery.setPath("items.details");
nestedQuery.setQuery(termQuery);
nestedQuery.setScoreMode(ScoreMode.None);

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

Require the same child row to meet multiple conditions

Set a BoolQuery that contains multiple child conditions as the subquery of one NestedQuery. The following example requires the same child row in items to have an items.keyword value of tablestore and an items.number field.

TermQuery termQuery = new TermQuery();
termQuery.setFieldName("items.keyword");
termQuery.setTerm(ColumnValue.fromString("tablestore"));

ExistsQuery existsQuery = new ExistsQuery();
existsQuery.setFieldName("items.number");

BoolQuery childQuery = new BoolQuery();
childQuery.setMustQueries(Arrays.asList(termQuery, existsQuery));

NestedQuery nestedQuery = new NestedQuery();
nestedQuery.setPath("items");
nestedQuery.setQuery(childQuery);
nestedQuery.setScoreMode(ScoreMode.None);

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

Allow different child rows to meet multiple conditions

Create one NestedQuery for each condition and combine the nested queries in an outer BoolQuery. The following example allows the items.keyword value of tablestore and the existence of items.number to be matched by different child rows.

TermQuery termQuery = new TermQuery();
termQuery.setFieldName("items.keyword");
termQuery.setTerm(ColumnValue.fromString("tablestore"));
NestedQuery termNestedQuery = new NestedQuery();
termNestedQuery.setPath("items");
termNestedQuery.setQuery(termQuery);
termNestedQuery.setScoreMode(ScoreMode.None);

ExistsQuery existsQuery = new ExistsQuery();
existsQuery.setFieldName("items.number");
NestedQuery existsNestedQuery = new NestedQuery();
existsNestedQuery.setPath("items");
existsNestedQuery.setQuery(existsQuery);
existsNestedQuery.setScoreMode(ScoreMode.None);

BoolQuery boolQuery = new BoolQuery();
boolQuery.setMustQueries(
        Arrays.asList(termNestedQuery, existsNestedQuery));

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

Return and highlight matching child rows

Use InnerHits to configure the number, sort order, and highlight settings of matching child rows. The following example queries child rows whose items.description field contains hangzhou and returns highlighted results.

MatchQuery matchQuery = new MatchQuery();
matchQuery.setFieldName("items.description");
matchQuery.setText("hangzhou");

HighlightParameter parameter = new HighlightParameter();
parameter.setPreTag("<em>");
parameter.setPostTag("</em>");
Highlight highlight = new Highlight();
highlight.addFieldHighlightParam("items.description", parameter);

InnerHits innerHits = new InnerHits();
innerHits.setLimit(3);
innerHits.setSort(new Sort(Arrays.asList(
        new ScoreSort(), new DocSort(SortOrder.ASC))));
innerHits.setHighlight(highlight);

NestedQuery nestedQuery = new NestedQuery();
nestedQuery.setPath("items");
nestedQuery.setQuery(matchQuery);
nestedQuery.setScoreMode(ScoreMode.None);
nestedQuery.setInnerHits(innerHits);

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

In a multi-level nested query, configure innerHits in each NestedQuery level from which matching child rows must be returned or highlighted.