Query JSON data using the Tablestore Python SDK - Tablestore

JSON fields support the Object type and the Nested type. For Object type queries, use the desired query type directly. For Nested type queries, wrap the query conditions in a NestedQuery to ensure that multiple conditions are evaluated against the same nested element.

Prerequisites

The Tablestore client is initialized.
A search index is created on the data table with the JSON field type configured.

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.

Examples

Query JSON data of the Nested type

The following example queries rows where a single nested element in the address field meets both conditions: address.country is 'China' and address.city is 'Seattle'. Because the Nested type requires all conditions to match within the same sub-element, the query must use NestedQuery to wrap BoolQuery.

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)

Query JSON data of the Object type

The following example queries rows where the address column meets both conditions: address.country is 'China' and address.city is 'Seattle'. Because the Object type does not require conditions to match within the same sub-element, use BoolQuery directly without NestedQuery.

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)