Exists query is also called NULL query or NULL-value query. This query is used in sparse data to determine whether a column of a row exists. For example, you can query the rows in which the value of the address column is not empty.

Note
  • If you want to check whether a column contains empty values, you must use ExistsQuery together with must_not_queries of BoolQuery.
  • If one of the following conditions is met, the system considers that a column does not to exist. In this example, the city column is used.
    • The type of the city column in the search index is a basic type such as keyword. If a row in which the city column does not exist in the data table, the search index considers that the city column does not exist.
    • The type of the city column in the search index is a basic type such as keyword. If a row in which the value of the city column is an empty array in the data table ("city" = "[]"), the search index considers that the city column does not exist.

Prerequisites

  • The OTSClient is initialized. For more information, see Initialization.
  • A data table is created. Data is written to the table.
  • A search index is created for the data table. For more information, see Create search indexes.

Parameters

ParameterDescription
field_nameThe name of the column.
queryThe query type. Set the value to ExistsQuery.
get_total_countSpecifies whether to return the total number of rows that meet the query conditions. The default value of this parameter is false, which specifies that the total number of rows that meet the query conditions is not returned.

If you set this parameter to true, the query performance is compromised.

table_nameThe name of the data table.
index_nameThe name of the search index.
columns_to_getSpecifies whether to return all columns of each row that meets the query conditions. You can configure return_type and column_names for this parameter.
  • If you set return_type to ColumnReturnType.SPECIFIED, you can use column_names to specify the columns to return.
  • If you set return_type to ColumnReturnType.ALL, all columns are returned.
  • If you set return_type to ColumnReturnType.NONE, only the primary key columns are returned.

Examples

The following sample code shows how to query the rows in which the city column is not empty:

  • Perform an exists query by using Tablestore SDK for Python V5.2.1 or later
    If you use Tablestore SDK for Python V5.2.1 or later to perform an exists query, a SearchResponse object is returned by default. The following code shows a sample request:
    query = ExistsQuery("city")
search_response = client.search(
    table_name, index_name,
    SearchQuery(query, limit=100, get_total_count=True),
    ColumnsToGet(return_type=ColumnReturnType.ALL)
)
    You can use the following sample request to obtain results of the Tuple type: 
    query = ExistsQuery("city")
rows, next_token, total_count, is_all_succeed, agg_results, group_by_results = client.search(
    table_name, index_name,
    SearchQuery(query, limit=100, get_total_count=True),
    ColumnsToGet(return_type=ColumnReturnType.ALL)
).v1_response()
  • Perform an exists query by using a version of Tablestore SDK for Python that is earlier than V5.2.1

    If you use a version of Tablestore SDK for Python that is earlier than V5.2.1 to perform an exists query, results of the Tuple type are returned by default. The following code shows a sample request:

    query = ExistsQuery("city")
rows, next_token, total_count, is_all_succeed = client.search(
    table_name, index_name,
    SearchQuery(query, limit=100, get_total_count=True),
    ColumnsToGet(return_type=ColumnReturnType.ALL)
)