Query JSON data using Tablestore Go SDK - Tablestore - Alibaba Cloud Documentation Center

JSON data includes Object and Nested types. To query data of the Object type, you can use the required query type directly. To query data of the Nested type, you must wrap the query conditions in a NestedQuery.

Prerequisites

You have initialized a Tablestore client.
You have created a search index on the data table and configured the type for the JSON field.

Limits

Vector type fields cannot be used in JSON.

Query scenario

Assume that a data table contains an `id` column (String) and an `address` column (String). The `address` column stores data in JSON format.

Assume the value of the `address` column in a row is [{ "country": "China", "city": "hangzhou" }, { "country": "usa", "city": "Seattle" }]. If the query conditions are country="China" and city="Seattle", the row is returned if the `address` column is set to the Object type. The row is not returned if the `address` column is set to the Nested type.

Sample code

JSON Nested type query example

The following example shows how to query for rows where a single nested object in the `address` field meets two conditions: address.country is "China" and address.city is "Seattle".

import (
    "fmt"
    "github.com/aliyun/aliyun-tablestore-go-sdk/tablestore"
    "github.com/aliyun/aliyun-tablestore-go-sdk/tablestore/search"
)

func nestedQuery1(client *tablestore.TableStoreClient) {
    searchRequest := &tablestore.SearchRequest{}
    searchRequest.SetTableName("<TABLE_NAME>")
    searchRequest.SetIndexName("<SEARCH_INDEX_NAME>")

    // Condition 1: address.country = "China"
    termQuery1 := &search.TermQuery{
        FieldName: "address.country",
        Term:      "China",
    }

    // Condition 2: address.city = "Seattle"
    termQuery2 := &search.TermQuery{
        FieldName: "address.city",
        Term:      "Seattle",
    }

    // Combine the two conditions. Both must be met.
    boolQuery := &search.BoolQuery{
        MustQueries: []search.Query{
            termQuery1,
            termQuery2,
        },
    }

    // Nested query
    nestedQuery := &search.NestedQuery{
        Path:      "address",
        Query:     boolQuery,
        ScoreMode: search.ScoreMode_None,
    }

    // Build and execute the search.
    searchQuery := search.NewSearchQuery()
    searchQuery.SetQuery(nestedQuery)
    searchRequest.SetSearchQuery(searchQuery)

    resp, err := client.Search(searchRequest)
    if err != nil {
        fmt.Printf("Search failed: %v\n", err)
        return
    }

    fmt.Printf("Found %d rows\n", len(resp.Rows))
        for _, row := range resp.Rows {
        fmt.Printf("Row: %+v\n", row.PrimaryKey)
    }
}

JSON Object type query example

The following example shows how to query for rows where the conditions are met across multiple nested objects in the `address` column: address.country is "China" and address.city is "Seattle".

import (
    "fmt"
    "github.com/aliyun/aliyun-tablestore-go-sdk/tablestore"
    "github.com/aliyun/aliyun-tablestore-go-sdk/tablestore/search"
)

func boolQuery(client *tablestore.TableStoreClient) {
    searchRequest := &tablestore.SearchRequest{}
    searchRequest.SetTableName("<TABLE_NAME>")
    searchRequest.SetIndexName("<SEARCH_INDEX_NAME>")

    // Condition 1: address.country = "China"
    termQuery1 := &search.TermQuery{
        FieldName: "address.country",
        Term:      "China",
    }

    // Condition 2: address.city = "Seattle"
    termQuery2 := &search.TermQuery{
        FieldName: "address.city",
        Term:      "Seattle",
    }

    // Use the Must condition of BoolQuery to query for sub-rows that meet both conditions.
    // Combine the two conditions. Both must be met.
    boolQuery := &search.BoolQuery{
        MustQueries: []search.Query{
            termQuery1,
            termQuery2,
        },
    }

    // Build and execute the search.
    searchQuery := search.NewSearchQuery()
    searchQuery.SetQuery(boolQuery)
    searchRequest.SetSearchQuery(searchQuery)

    // Execute the search.
    resp, err := client.Search(searchRequest)
    if err != nil {
        fmt.Printf("Search failed: %v\n", err)
        return
    }

    fmt.Printf("Found %d rows\n", len(resp.Rows))
    for _, row := range resp.Rows {
        fmt.Printf("Row: %+v\n", row.PrimaryKey)
    }
}