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.

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
fieldNameThe name of the column.
queryThe query type. Set the value to ExistsQuery.
getTotalCountSpecifies 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.

tableNameThe name of the data table.
indexNameThe name of the search index.
columnsToGetSpecifies 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

Query all rows in which the specified column is not empty.

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());
        }
    }
}