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

JSON data includes Object and Nested types. To query Object data, use the desired query type directly. To query 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".

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 queries rows where the `address` field satisfies two conditions across its nested objects: 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)
    }
}