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
Parameter | Description |
---|---|
field_name | The name of the column. |
query | The query type. Set the value to ExistsQuery. |
get_total_count | Specifies 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_name | The name of the data table. |
index_name | The name of the search index. |
columns_to_get | Specifies whether to return all columns of each row that meets the query conditions. You can configure return_type and column_names for this parameter.
|
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 laterIf 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) )