JSON queries

You can use the Tablestore SDK for Java to query subfields of Object or Nested JSON fields in a search index. Object fields do not preserve child-object boundaries, whereas Nested fields preserve them.

Prerequisites

Install the Tablestore SDK for Java and initialize the client.
The target field is configured as a JSON field in the search index, and jsonType is set to OBJECT or NESTED. For more information, see Create a search index.

Feature description

JSON queries do not use a dedicated query type. Select a query method based on the jsonType of the JSON field in the search index.

JSON type	Field relationships	Query method
Object	Does not preserve the boundaries of objects in an array. Different query conditions can be satisfied by different objects.	Directly use a query type suitable for the subfield type and matching requirements. Specify the full path in each subfield name.
Nested	Stores each object in an array as an independent child row and preserves the relationships among fields in the same object.	Wrap the subquery in a `NestedQuery` and use `path` to specify the full path of the Nested field.

For example, assume that the address column in a table is of the String type and stores the following JSON array:

[
  { "country": "China", "city": "hangzhou" },
  { "country": "usa", "city": "Seattle" }
]

If you query for both country="China" and city="Seattle", the row is returned when address is configured as an Object field because different objects can satisfy the two conditions. The row is not returned when address is configured as a Nested field because no single object satisfies both conditions.

Call search to run a JSON query.

SearchResponse search(SearchRequest request)

Note

The subFieldSchemas of a JSON field cannot contain Vector fields.

Query an Object field

The following example queries rows in which address.country is China and address.city is Seattle. Because address is an Object field, different objects can satisfy the two conditions.

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

TermQuery countryQuery = new TermQuery();
countryQuery.setFieldName("address.country");
countryQuery.setTerm(ColumnValue.fromString("China"));

TermQuery cityQuery = new TermQuery();
cityQuery.setFieldName("address.city");
cityQuery.setTerm(ColumnValue.fromString("Seattle"));

BoolQuery objectQuery = new BoolQuery();
objectQuery.setMustQueries(Arrays.asList(countryQuery, cityQuery));

SearchQuery searchQuery = new SearchQuery();
searchQuery.setQuery(objectQuery);
searchQuery.setLimit(10);

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

Query a Nested field

The following example queries rows in which the same object in address has an address.country value of China and an address.city value of Seattle. For more information about query methods and parameters for Nested fields, see Nested query.

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

TermQuery countryQuery = new TermQuery();
countryQuery.setFieldName("address.country");
countryQuery.setTerm(ColumnValue.fromString("China"));

TermQuery cityQuery = new TermQuery();
cityQuery.setFieldName("address.city");
cityQuery.setTerm(ColumnValue.fromString("Seattle"));

BoolQuery childQuery = new BoolQuery();
childQuery.setMustQueries(Arrays.asList(countryQuery, cityQuery));

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

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

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

Parameters

Search request

request is of the SearchRequest type and contains the following parameters.

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

Query configuration

request.searchQuery is of the SearchQuery type and contains the following parameters.

Name	Type	Description
query (required)	Query	The query condition. For an Object field, directly set a query type suitable for the subfield type and matching requirements. For a Nested field, set this parameter to a `NestedQuery` object.
offset (optional)	Integer	The position from which the current query starts.
limit (optional)	Integer	The maximum number of rows to return. If you set this parameter to `0`, no rows are returned.
highlight (optional)	Highlight	The summary and highlighting configurations. For a Nested field, configure summary and highlighting for matching child rows by using `NestedQuery.innerHits`.
collapse (optional)	Collapse	The result collapsing configuration, which removes duplicate results based on the specified field.
sort (optional)	Sort	The sorting method for the results.
trackTotalCount (optional)	int	The expected maximum number of matched rows to count. The default value is `TRACK_TOTAL_COUNT_DISABLED`, which disables counting. Set this parameter to `TRACK_TOTAL_COUNT` to count all matched rows. A smaller value provides better query performance.
filter (optional)	SearchFilter	A filter applied to the results of `query`.
aggregationList (optional)	`List<Aggregation>`	The aggregation configurations.
groupByList (optional)	`List<GroupBy>`	The grouping configurations.
token (optional)	byte[]	The pagination token. Set this parameter to the `nextToken` value from the previous response to continue reading data. When `token` is set, the SDK clears `sort` because the token already contains the sorting condition.

Nested query condition

When you query a Nested field, request.searchQuery.query is of the NestedQuery type and contains the following parameters.

Name	Type	Description
path (required)	String	The path of the Nested field to query. To query a multi-level Nested field, specify the full path of the target field.
query (required)	Query	The query condition to run on the child rows at `path`. Specify the full path in each subfield name.
scoreMode (required)	ScoreMode	The method used to calculate the parent-row score when multiple child rows match. `None` does not calculate relevance scores for child rows. `Avg`, `Max`, `Min`, and `Total` use the average, maximum, minimum, and sum of child-row scores, respectively.
innerHits (optional)	InnerHits	The configurations used to return, sort, paginate, and highlight matching child rows. If you do not configure this parameter, details of 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 greater value increases the score of a matched row but does not change the matching scope.

Child-row return configuration

request.searchQuery.query.innerHits is of the InnerHits type and contains the following parameters.

Name	Type	Description
sort (optional)	Sort	The sorting method for matching child rows. `ScoreSort` and `DocSort` are supported. `FieldSort` is not supported.
offset (optional)	Integer	The position from which matching child rows are returned.
limit (optional)	Integer	The maximum number of matching child rows to return. The default value is `3`.
highlight (optional)	Highlight	The summary and highlighting configurations for matching child rows.

Returned columns

request.columnsToGet is of the SearchRequest.ColumnsToGet type and contains the following parameters.

Name	Type	Description
columns (optional)	`List<String>`	The names of the attribute columns to return. Configure this parameter only when both `returnAll` and `returnAllFromIndex` are `false`. If you do not configure this parameter, only primary key columns are returned.
returnAll (optional)	boolean	Specifies whether to return all attribute columns in the table. The default value is `false`.
returnAllFromIndex (optional)	boolean	Specifies whether to return all attribute columns that are indexed. The default value is `false`. You cannot set both this parameter and `returnAll` to `true`.

Response

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

Name	Type	Description
totalCount	long	The number of matched rows. Call `getTotalCount()` to obtain the value. The value depends on the `trackTotalCount` configuration.
rows	`List<Row>`	The rows returned by the current 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. From this field, you can obtain matched rows, scores, summary and highlighting results, and matching child rows of Nested fields.
nextToken	byte[]	The token for the next page. Call `getNextToken()` to obtain the value. If the value is not `null`, use it as `token` in the next request to continue reading data.
isAllSuccess	boolean	Indicates whether all index partitions were queried. Call `isAllSuccess()` to obtain the value. If the value is `false`, only partial results are returned, and `totalCount` may be less than the actual number of matched rows.