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 perform an exists query on a Nested column, you can use nested query.
  • If you want to check whether a column contains empty values, you must use ExistsQuery together with mustNotQueries 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.

API operations

You can call the Search or ParallelScan operation and set the query type to ExistsQuery to perform exists query.

Use Tablestore SDKs

You can use one of the following Tablestore SDKs to perform exists query:

Parameters

Parameter Description
fieldName The name of the column.
query The query type. Set the value to ExistsQuery.
getTotalCount Specifies whether to return the total number of rows that meet the query conditions. The default value of this parameter is false, which indicates 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.

tableName The name of the data table.
indexName The name of the search index.
columnsToGet Specifies whether to return all columns of each row that meets the query conditions. You can configure returnAll and columns for this parameter.

The default value of returnAll is false, which indicates that not all columns are returned. In this case, you can use columns to specify the columns that you want to return. If you do not specify the columns that you want to return, only the primary key columns are returned.

If you set returnAll to true, all columns are returned.

Sample code

public void existQuery(SyncClient syncClient) {
    // Query whether the col_keyword field exists. The col_keyword field is not a Nested field. 
    {
        {
            // Sample code that is used to query whether a field exists. The field is not a Nested field. 
            SearchQuery searchQuery = new SearchQuery();
            ExistsQuery existQuery = new ExistsQuery(); // Set the query type to ExistsQuery. 
            existQuery.setFieldName("col_keyword");
            searchQuery.setQuery(existQuery);
            SearchRequest searchRequest = new SearchRequest(tableName, indexName, searchQuery);
            SearchResponse resp = syncClient.search(searchRequest);
        }
        {
            // Construct a builder. 
            SearchResponse resp = syncClient.search(SearchRequest.newBuilder()
                    .indexName(indexName)
                    .tableName(tableName)
                    .searchQuery(
                            SearchQuery.newBuilder()
                                    .query(QueryBuilders.exists("col_keyword"))
                                    .limit(10)
                                    .build())
                    .build());
        }
    }
    // Nested structure: {"col_nested":[{keyword:"a","long":123}]} 
    {
        // Query whether the col_nested parent field exists. The col_nested field is of the Nested type. 
        {
            SearchResponse resp = syncClient.search(SearchRequest.newBuilder()
                    .indexName(indexName)
                    .tableName(tableName)
                    .searchQuery(
                            SearchQuery.newBuilder()
                                    .query(QueryBuilders.nested()
                                            .scoreMode(ScoreMode.None)
                                            .path("col_nested")
                                            .query(QueryBuilders.exists("col_nested")))
                                    .limit(10)
                                    .getTotalCount(false)
                                    .build())
                    .build());
        }
        // Query whether the col_nested.keyword field exists. The col_nested.keyword field is of the Nested type. 
        {
            SearchResponse resp = syncClient.search(SearchRequest.newBuilder()
                    .indexName(indexName)
                    .tableName(tableName)
                    .searchQuery(
                            SearchQuery.newBuilder()
                                    .query(QueryBuilders.nested()
                                            .scoreMode(ScoreMode.None)
                                            .path("col_nested")
                                            .query(QueryBuilders.exists("col_nested.keyword")))
                                    .limit(10)
                                    .getTotalCount(false)
                                    .build())
                    .build());
        }
    }
}