Query JSON data using the Tablestore Java SDK - Tablestore

JSON data includes Object and Nested types. To query JSON Object data, use the desired query type directly. To query JSON Nested data, wrap the query conditions in a NestedQuery.

Prerequisites

Initialize a Tablestore client.
Create a search index on the data table and configure the JSON field type.

Limits

Vector type fields cannot be used in JSON.

Query scenarios

Assume that a data table contains an `id` column of the String type and an `address` column of the String type. The `address` column stores JSON-formatted data.

Assume that a row has an `address` column with the value [{ "country": "China", "city": "hangzhou" }, { "country": "usa", "city": "Seattle" }]. A query for country="China" and city="Seattle" does not return the row if the `address` column is of the Nested type, but returns the row if it is of the Object type.

Sample code

JSON Nested type query example

The following example queries rows where the same nested object of the `address` field satisfies two conditions: address.country is "China" and address.city is "Seattle".

public static void nestedQuery(SyncClient client) {
    // Condition 1: The value of the country field in the address sub-row must be "China".
    TermQuery termQuery1 = new TermQuery();
    termQuery1.setFieldName("address.country");
    termQuery1.setTerm(ColumnValue.fromString("China"));

    // Condition 2: The value of the city field in the address sub-row must be "Seattle".
    TermQuery termQuery2 = new TermQuery();
    termQuery2.setFieldName("address.city");
    termQuery2.setTerm(ColumnValue.fromString("Seattle"));

    // Use the AND condition of BoolQuery to query for sub-rows that meet both conditions.
    List<Query> mustQueries = new ArrayList<>();
    mustQueries.add(termQuery1);
    mustQueries.add(termQuery2);
    BoolQuery boolQuery = new BoolQuery();
    boolQuery.setMustQueries(mustQueries);

    // Set BoolQuery within NestedQuery to require a sub-row to meet multiple query conditions at the same time.
    NestedQuery nestedQuery = new NestedQuery();    // Set the query type to NestedQuery.
    nestedQuery.setPath("address");   // Set the path of the nested type 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);

    SearchResponse resp = client.search(searchRequest);
    System.out.println("Row: " + resp.getRows());
}

JSON Object type query example

The following example queries rows where the `address` field satisfies two conditions across its nested objects: address.country is "China" and address.city is "Seattle".

public static void boolQuery(SyncClient client) {
    // Condition 1: The value of the country field in the address sub-row must be "China".
    TermQuery termQuery1 = new TermQuery();
    termQuery1.setFieldName("address.country");
    termQuery1.setTerm(ColumnValue.fromString("China"));

    // Condition 2: The value of the city field in the address sub-row must be "Seattle".
    TermQuery termQuery2 = new TermQuery();
    termQuery2.setFieldName("address.city");
    termQuery2.setTerm(ColumnValue.fromString("Seattle"));

    // Use the AND condition of BoolQuery to query for sub-rows that meet both conditions.
    List<Query> mustQueries = new ArrayList<>();
    mustQueries.add(termQuery1);
    mustQueries.add(termQuery2);
    BoolQuery boolQuery = new BoolQuery();
    boolQuery.setMustQueries(mustQueries);


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

    SearchRequest searchRequest = new SearchRequest("<TABLE_NAME>", "<SEARCH_INDEX_NAME>", searchQuery);

    SearchResponse resp = client.search(searchRequest);
    System.out.println("Row: " + resp.getRows());
}