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

  • A Tablestore client 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. To use exists query, set this parameter to TableStore.QueryType.EXISTS_QUERY.
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.
  • If you set returnType to TableStore.ColumnReturnType.RETURN_SPECIFIED, you need to configure returnNames to specify the columns that you want to return.
  • If you set the returnType parameter to TableStore.ColumnReturnType.RETURN_ALL, all columns are returned.
  • If you set the returnType parameter to TableStore.ColumnReturnType.RETURN_ALL_FROM_INDEX, all columns in the search index are returned. .
  • If you set the returnType parameter to TableStore.ColumnReturnType.RETURN_NONE, only the primary key columns are returned.

Examples

client.search({
    tableName: TABLE_NAME,
    indexName: INDEX_NAME,
    searchQuery: {
        offset: 0,
        limit: 10, // To query only the number of rows that meet the query conditions without returning specific data, you can set limit to 0. This way, Tablestore returns the number of rows that meet the query conditions without specific data from the table. 
        query: {
            queryType: TableStore.QueryType.EXISTS_QUERY,
                        query: {
                                fieldName: "pic_des"
                        },
        },
        getTotalCount: true // Specify 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. 
    },
    columnToGet: { // Specify the columns that you want to return. You can configure the RETURN_SPECIFIED parameter to return specified columns, the RETURN_ALL parameter to return all columns, the RETURN_ALL_FROM_INDEX parameter to return all columns in the search index, or the RETURN_NONE parameter to return only the primary key columns. 
        returnType: TableStore.ColumnReturnType.RETURN_SPECIFIED,
        returnNames: ["Col_1", "Col_2", "Col_3"]
    }
}, function (err, data) {
    if (err) {
        console.log('error:', err);
        return;
    }
    console.log('success:', JSON.stringify(data, null, 2));
});