JSON

Key differences

Selection guidelines

Choose Nested when queries must strictly preserve field correlations within nested objects. Choose Object when high query performance is the priority and strict field correlation is not required.

Step 1: Select the JSON type and data format

The following sections compare Object and Nested types, show supported data formats, and demonstrate how to write JSON data.

// Array format
[{ "country": "China", "city": "Hangzhou" }, { "country": "USA", "city": "Seattle" }]

// Non-array format
{ "country": "China", "city": "Hangzhou" }

private static void putRow(SyncClient client) {
    // Construct the primary key.
    PrimaryKeyBuilder primaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
    primaryKeyBuilder.addPrimaryKeyColumn("id", PrimaryKeyValue.fromString("10001"));
    PrimaryKey primaryKey = primaryKeyBuilder.build();

    // Specify the table name.
    RowPutChange rowPutChange = new RowPutChange("<TABLE_NAME>", primaryKey);

    // Build the raw JSON data.
    List<Map<String, Object>> addresses = Arrays.asList(
    new HashMap<String, Object>() {{ put("country", "China"); put("city", "Hangzhou"); }},
    new HashMap<String, Object>() {{ put("country", "USA");   put("city", "Seattle"); }}
    );
    String jsonString = JSON.toJSONString(addresses); 

    rowPutChange.addColumn(new Column("address", ColumnValue.fromString(jsonString)));

    client.putRow(new PutRowRequest(rowPutChange));
}

Step 2: Configure the field structure and create an index

The following example configures a single-level JSON field with two sub-fields. Define the field type and properties for each sub-field to make them queryable.

List<FieldSchema> subFieldSchemas = new ArrayList<FieldSchema>();
subFieldSchemas.add(new FieldSchema("country", FieldType.KEYWORD)
    .setIndex(true).setEnableSortAndAgg(true));
subFieldSchemas.add(new FieldSchema("city", FieldType.KEYWORD)
    .setIndex(true).setEnableSortAndAgg(true));

FieldSchema jsonFieldSchema = new FieldSchema("address", FieldType.Json)
    .setJsonType(JsonType.OBJECT) // Set to JsonType.OBJECT or JsonType.NESTED
    .setSubFieldSchemas(subFieldSchemas);

Step 3: Query data

• Object: Flattens nested data. Access fields by concatenating parent and child field names with a period (.). Because fields are flattened, values from different nested objects can cross-match.

• Nested: Preserves the independence of each nested object. Wrap query conditions in a NestedQuery to ensure field matching occurs only within the same nested object.

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());
}

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());
}

Single-level JSON

The following Java example creates a single-level JSON field tags (select the JSON type based on your requirements) with three sub-fields:

• tagName: Keyword type. Used for exact matching and aggregation of tag names.

• score: Double type. Used for numerical calculations and sorting of tag weights.

• time: Date type in epoch_millis format. Used for time range queries and time series analysis.

Both array and non-array formats are supported for data writes:

// Array format
[{"tagName":"tag1", "score":0.8,"time": 1730690237000 }, {"tagName":"tag2", "score":0.2,"time": 1730691557000}]
// Non-array format
{"tagName":"tag1", "score":0.8,"time": 1730690237000 }

Complete schema configuration:

List<FieldSchema> subFieldSchemas = new ArrayList<FieldSchema>();
subFieldSchemas.add(new FieldSchema("tagName", FieldType.KEYWORD)
    .setIndex(true).setEnableSortAndAgg(true));
subFieldSchemas.add(new FieldSchema("score", FieldType.DOUBLE)
    .setIndex(true).setEnableSortAndAgg(true));
subFieldSchemas.add(new FieldSchema("time", FieldType.DATE)
    .setDateFormats(Arrays.asList("epoch_millis")));

FieldSchema nestedFieldSchema = new FieldSchema("tags", FieldType.Json)
    .setJsonType(JsonType.OBJECT) // Replace with JsonType.NESTED as needed
    .setSubFieldSchemas(subFieldSchemas);

Multi-level JSON

The following Java example creates a multi-level JSON field user (select the JSON type based on your requirements) with basic user information and nested address data. Multi-level JSON fields support a mix of Nested and Object types.

• Basic fields: name (Keyword, for exact name queries), age (Long, for age range filtering), birth (Date, format yyyy-MM-dd HH:mm:ss.SSS, for birthday queries), phone (Keyword, for contact matching).

• Nested field: address (select the JSON type based on your requirements), containing province, city, and street (all Keyword type) for hierarchical location queries.

Sample user data:

{
  "name": "John",
  "age": 20,
  "birth": "2014-10-10 12:00:00.000",
  "phone": "1390000****",
  "address": {
    "province": "Zhejiang",
    "city": "Hangzhou",
    "street": "1201, Sunshine Community, Yangguang Avenue"
  }
}

Complete schema configuration for multi-level JSON:

// Sub-field schema for address (path: user.address)
List<FieldSchema> addressSubFiledSchemas = new ArrayList<>();
addressSubFiledSchemas.add(new FieldSchema("province",FieldType.KEYWORD));
addressSubFiledSchemas.add(new FieldSchema("city",FieldType.KEYWORD));
addressSubFiledSchemas.add(new FieldSchema("street",FieldType.KEYWORD));

// Sub-field schema for user (path: user)
List<FieldSchema> subFieldSchemas = new ArrayList<>();
subFieldSchemas.add(new FieldSchema("name",FieldType.KEYWORD));
subFieldSchemas.add(new FieldSchema("age",FieldType.LONG));
subFieldSchemas.add(new FieldSchema("birth",FieldType.DATE)
    .setDateFormats(Arrays.asList("yyyy-MM-dd HH:mm:ss.SSS")));
subFieldSchemas.add(new FieldSchema("phone",FieldType.KEYWORD));
subFieldSchemas.add(new FieldSchema("address",FieldType.JSON)
    .setJsonType(JsonType.NESTED) // Replace with JsonType.OBJECT as needed
    .setSubFieldSchemas(addressSubFiledSchemas));

// Create the parent field: user
List<FieldSchema> fieldSchemas = new ArrayList<>();
fieldSchemas.add(new FieldSchema("user",FieldType.JSON)
    .setJsonType(JsonType.OBJECT) // Replace with JsonType.NESTED as needed
    .setSubFieldSchemas(subFieldSchemas));