Query JSON data using the Tablestore Java SDK - Tablestore

JSON data can be of the Object type or the Nested type. To query a JSON Object, you can use the desired query type directly. To query a JSON Nested type, you must wrap the query conditions in a `NestedQuery`.

Prerequisites

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

Limits

Vector type fields cannot be used in JSON.

Query scenarios

Assume a data table contains an `id` column of the String type and an `address` column of the String type. The `address` column stores data in JSON format.

Assume 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 a Nested type. If the `address` column is an Object type, the query returns the row.

Sample code

JSON Nested type query example

The following example queries for rows that satisfy two conditions in the same nested object of the `address` field: 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 for 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());
}