Use NestedQuery to query data in sub-rows of a nested field. Nested types cannot be queried directly — wrap the query in a NestedQuery and specify the nested field path and a subquery. The subquery can be any query 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 data tables and Write data.
-
A search index is created for the data table. For more information, see Create a search index.
Parameters
|
Parameter |
Description |
|
tableName |
The name of the data table. |
|
indexName |
The name of the search index. |
|
path |
The path of the nested field. For example, |
|
query |
The query to run against the sub-fields of the nested field. Any query type is supported. |
|
scoreMode |
The score mode to use when a field contains multiple values. |
|
InnerHits |
Configuration for the sub-fields of the nested field. Includes the following options:
|
Examples
Example of a single-level nested query
Query rows where Col_Nested.Sub_Col_Keyword equals "happy". Col_Nested is a nested field whose sub-rows contain the Sub_Col_Keyword sub-field.
/**
* Sample nested data for Col_Nested: '[{Sub_Col_Keyword: "happy"},{Sub_Col_Keyword: "sunny"}]'
*/
client.search({
tableName: "<TABLE_NAME>",
indexName: "<SEARCH_INDEX_NAME>",
searchQuery: {
offset: 0,
limit: 10, // To get only the row count without retrieving specific data, set limit to 0. This prevents any rows from being returned.
query: { // Set the query type to TableStore.QueryType.NESTED_QUERY.
queryType: TableStore.QueryType.NESTED_QUERY,
query: {
path: "Col_Nested",
query: {
queryType: TableStore.QueryType.TERM_QUERY,
query: {
fieldName: "Col_Nested.Sub_Col_Keyword",
term: "happy"
}
},
}
},
getTotalCount: true // TotalCount in the result indicates the total number of rows in the table. The default value is false, which means the total count is not returned.
},
columnToGet: { // The columns to return. You can set this to RETURN_SPECIFIED (return custom columns), RETURN_ALL (return all columns), RETURN_ALL_FROM_INDEX (return all columns from the search index), or RETURN_NONE (return no columns).
returnType: TableStore.ColumnReturnType.RETURN_ALL
}
}, function (err, data) {
if (err) {
console.log('error:', err);
return;
}
console.log('success:', JSON.stringify(data, null, 2));
});Example of using summary and highlight in a nested query
Use NestedQuery to find rows where the Sub_Col_Text sub-field in the Col_Nested nested field matches tablestore, and highlight the matched terms in the results.
client.search({
tableName: "<TABLE_NAME>",
indexName: "<SEARCH_INDEX_NAME>",
searchQuery: {
offset: 0,
limit: 10, // To get only the row count without retrieving specific data, set limit to 0. This prevents any rows from being returned.
query: { // Set the query type to TableStore.QueryType.NESTED_QUERY.
queryType: TableStore.QueryType.NESTED_QUERY,
query: {
path: "Col_Nested",
query: {
queryType: TableStore.QueryType.MATCH_QUERY,
query: {
fieldName: "Col_Nested.Sub_Col_Text",
text: "tablestore"
}
},
innerHits: {
sort: {
sorters: [
{
scoreSort: {
order: TableStore.SortOrder.SORT_ORDER_DESC
}
},
{
docSort: {
order: TableStore.SortOrder.SORT_ORDER_ASC
}
},
],
},
highlight: {
highlightParameters: [
{
fieldName:"Col_Nested.Sub_Col_Text",
preTag: "",
postTag: "",
fragmentsOrder: TableStore.HighlightFragmentOrder.TEXT_SEQUENCE,
fragmentSize: 20,
numberOfFragments: 3,
}
]
}
},
}
},
getTotalCount: true // TotalCount in the result indicates the total number of rows in the table. The default value is false, which means the total count is not returned.
},
columnToGet: { // The columns to return. You can set this to RETURN_SPECIFIED (return custom columns), RETURN_ALL (return all columns), RETURN_ALL_FROM_INDEX (return all columns from the search index), or RETURN_NONE (return no columns).
returnType: TableStore.ColumnReturnType.RETURN_ALL
}
}, function (err, data) {
if (err) {
console.log('error:', err);
return;
}
console.log('success:', JSON.stringify(data.rows, null, 2));
printSearchHit(data.searchHits, "");
});
/**
* Prints the content of searchHit.
* @param searchHits The searchHits.
* @param prefix The prefix to add for nested structures to print hierarchical information.
*/
function printSearchHit(searchHits, prefix) {
TableStore.util.arrayEach(searchHits, function (searchHit) {
if (searchHit.highlightResultItem != null) {
console.log(prefix + "Highlight: \n");
var strBuilder = ""
for (const [key,val] of searchHit.highlightResultItem.highlightFields.entries()) {
strBuilder += key + ":[";
strBuilder += val.fragments.join(",") + "]\n";
console.log(strBuilder);
}
}
for (const [key,val] of searchHit.searchInnerHits.entries()) {
console.log(prefix + "Path: " + key + "\n");
console.log(prefix + "InnerHit: \n");
printSearchHit(val.subSearchHits, prefix + " ");
}
});
}FAQ
References
The following query types are supported by search indexes: term query, terms query, match all query, match query, match phrase query, prefix query, range query, wildcard query, Boolean query, geo query, nested query, vector query, and exists query. You can select a query type to query data based on your business requirements.
If you want to sort or paginate the rows that meet the query conditions, you can use the sorting and paging feature. For more information, see Sorting and paging.
If you want to collapse the result set based on a specific column, you can use the collapse (distinct) feature. 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, such as obtaining the extreme values, sum, and total number of rows, you can perform aggregation operations or execute SQL statements. For more information, see Aggregation and SQL query.
If you want to quickly 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.