This topic describes how to use Boolean query to query the rows based on a combination of subqueries. Tablestore returns the rows that match the subqueries. Each subquery can be of any type, including BoolQuery.

Prerequisites

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

Parameters

Parameter Description
tableName The name of the data table.
indexName The name of the search index.
mustQueries The list of subqueries that the query results must match. This parameter is equivalent to the AND operator.
mustNotQueries The list of subqueries that the query results must not match. This parameter is equivalent to the NOT operator.
filterQueries The list of subqueries. Only rows that match all subfilters are returned. filter is similar to query except that filter does not calculate the relevance score based on the number of subfilters that the row matches.
shouldQueries The list of subqueries that the query results can or cannot match. This parameter is equivalent to the OR operator.

Only rows that meet the minimum number of subquery conditions specified by shouldQueries are returned.

A higher overall relevance score indicates that more subquery conditions specified by shouldQueries are met.

minimumShouldMatch The minimum number of subquery conditions specified by shouldQueries that the rows must meet. If no other subquery conditions except the subquery conditions that are specified by shouldQueries are specified, the default value of the minimumShouldMatch parameter is 1. If other subquery conditions, such as subquery conditions specified by mustQueries, mustNotQueries, and filterQueries are specified, the default value of the minimumShouldMatch parameter is 0.

Examples

The following code provides an example on how to construct a Boolean query to query the rows based on a combination of subqueries:

var client = require('../client');
var TableStore = require('../../index.js');
var Long = TableStore.Long;
/**
 * Perform a Boolean query to implement the following operations: (col2 < 4 or col 3 < 5) or (col2 = 4 and (col3 = 5 or col3 = 6)). Logic:
 * boolQuery1 = rangeQuery(col2<4) or rangeQuery(col3<5)
 * boolQuery2 = termQuery(col3=5) or (col3=6)
 * boolQuery3 = termQuery(col2=4) and boolquery2
 * boolQuery4 = boolQuery1 or boolQuery3
 */
client.search({
    tableName: "sampleTable",
    indexName: "sampleSearchIndex",
    searchQuery: {
        offset: 0, // Query the offset value. 
        limit: 10, // To query the number of only matched rows without any detailed data, you can set limit to 0. In this case, Tablestore returns the number of matched rows without data from the table. 
        getTotalCount: false, // The value of TotalCount indicates the total number of rows that meet the query conditions. By default, getTotalCount is false, which indicates that the total number of matched rows is not returned. Retain the default value in this example. 
        query: { // Construct boolQuery4. Specify the query condition to meet at least one of boolQuery1 and boolQuery3. 
            queryType: TableStore.QueryType.BOOL_QUERY,
            query: {
                shouldQueries: [ // Specify mustQueries, shouldQueries, or mustNotQueries. 
                    { // Construct boolQuery1. Specify the query condition to meet at least one of Query Condition 1 and Query Condition 2. 
                        queryType: TableStore.QueryType.BOOL_QUERY,
                        query: {
                            // Specify shouldQueries to query the rows that contain the col2 column whose value is smaller than 4 or contain the col3 column whose value is smaller than 5. 
                            shouldQueries:[
                                {
                                    // Query Condition 1: Perform a range query to query the rows that contain the col2 column whose value is smaller than 4. 
                                    queryType: TableStore.QueryType.RANGE_QUERY,
                                    query:{
                                        fieldName: "col2",
                                        rangeTo: 4
                                    }
                                },
                                {
                                    // Query Condition 2: Perform a range query to query the rows that contain the col3 column whose value is smaller than 5.                                     queryType: TableStore.QueryType.RANGE_QUERY,
                                    query:{
                                        fieldName: "col3",
                                        rangeTo: 5
                                    }
                                }
                            ],
                            minimumShouldMatch:1

                        }
                    },
                    { // Construct boolQuery3. Specify the query condition to meet the conditions of Query Condition 3 and boolQuery2. 
                        queryType: TableStore.QueryType.BOOL_QUERY,
                        query: {
                            mustQueries: [
                                // Specify mustQueries to query the rows that contain the col2 column whose value is equal to 4 and contain the col3 column whose value is equal to 5 or 6. 
                                {
                                    // Query Condition 3: Perform a term query to query the rows that contain the col2 column whose value is equal to 4. 
                                    queryType:TableStore.QueryType.TERM_QUERY,
                                    query: {
                                        fieldName : "col2",
                                        term: 4
                                    }
                                },
                                { // Construct boolQuery2: Specify the query condition to meet at least one of Query Condition 4 and Query Condition 5. 
                                    queryType: TableStore.QueryType.BOOL_QUERY,
                                    query: {
                      TERM                  // Specify shouldQueries to query the rows that contain the col3 column whose value is equal to 5 or 6. 
                                        shouldQueries:[
                                            {
                                                // Query Condition 4: Perform a term query to query the rows that contain the col3 column whose value is equal to 5. 
                                                queryType: TableStore.QueryType.TERM_QUERY,
                                                query:{
                                                    fieldName:"col3",
                                                    term: 5
                                                }
                                            },
                                            {
                                                // Query Condition 5: Perform a term query to query the rows that contain the col3 column whose value is equal to 6.                                                 queryType: TableStore.QueryType.TERM_QUERY,
                                                query:{
                                                    fieldName:"col3",
                                                    term: 6
                                                }
                                            }
                                        ],
                                        minimumShouldMatch:1
                                    }
                                }
                            ]
                        }
                    }
                ],
                minimumShouldMatch: 1 // Specify the minimum number of conditions that must be met. This parameter is valid when subquery conditions are specified only by shouldQueries. 
            }
        },
    },
    columnToGet: { // Set RETURN_SPECIFIED to return the specified columns, RETURN_ALL to return all columns, or RETURN_NONE to return no attribute columns.        
        returnType: TableStore.ColumnReturnType.RETURN_SPECIFIED,
        returnNames: ["col2", "col3", "col4"]
    }
}, function (err, data) {
    if (err) {
        console.log('error:', err);
        return;
    }
    console.log('success:', JSON.stringify(data, null, 2));
});