Query JSON data using the Tablestore Python SDK - Tablestore

JSON data can be of the Object type or the Nested type. 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 condition in a NestedQuery.

Prerequisites

Initialize the Tablestore client.
A search index is created on the data table, and the type for the JSON field is configured.

Limits

Vector type fields cannot be used in JSON.

Query scenarios

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

Assume that the `address` column in a row has the value [{ "country": "China", "city": "hangzhou" }, { "country": "usa", "city": "Seattle" }]. A query with the conditions country="China" and city="Seattle" does not retrieve this row if the `address` column is of the Nested type. However, the query retrieves the row if the `address` column is of the Object type.

Examples

Example of a JSON Nested type query

The following example shows how to query for rows where a single nested element of the `address` field satisfies two conditions: address.country is 'China' and address.city is 'Seattle'.

from tablestore import *

def nested_query(client):
    # Condition 1: The country in the address sub-row must be "China".
    term_query1 = TermQuery('address.country','China')

    # Condition 2: The city in the address sub-row must be "Seattle".
    term_query2 = TermQuery('address.city', 'Seattle')

    # Use BoolQuery with an AND condition to find sub-rows that meet both queries.
    bool_query = BoolQuery(
        must_queries=[term_query1, term_query2]
    )

    # Use BoolQuery inside NestedQuery to require a single sub-row to meet multiple conditions.
    nested_query = NestedQuery(
        path="address",  # The path to the nested column. This is the parent path of the field to query.
        query=bool_query,  # The inner query condition.
        score_mode=ScoreMode.NONE  # The scoring mode. Set to None.
    )

    # Build the search query.
    search_query = SearchQuery(
        query=nested_query
    )

    # Execute the search.
    resp = client.search(table_name='<TABLE_NAME>',index_name='<SEARCH_INDEX_NAME>',
                         search_query=search_query)
    print("Row:", resp.rows)

Example of a JSON Object type query

The following example shows how to query for rows that meet two conditions in the `address` column: address.country is 'China' and address.city is 'Seattle'.

from tablestore import *

def bool_query(client):
    # Condition 1: The country in the address sub-row must be "China".
    term_query1 = TermQuery('address.country','China')

    # Condition 2: The city in the address sub-row must be "Seattle".
    term_query2 = TermQuery('address.city','Seattle')

    # Use BoolQuery with an AND condition to find sub-rows that meet both queries.
    bool_query = BoolQuery(
        must_queries=[term_query1, term_query2]
    )

    # Build the search query.
    search_query = SearchQuery(
        query=bool_query
    )


    # Execute the search.
    resp = client.search(table_name='<TABLE_NAME>',index_name='<SEARCH_INDEX_NAME>',
                         search_query=search_query)
    print("Row:", resp.rows)