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
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 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
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)