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
- 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
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. A filter is similar to a query except that a 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)). The following logic is used:
* 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 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.
getTotalCount: false, // 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.
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: {
// 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: { // 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: ["col2", "col3", "col4"]
}
}, function (err, data) {
if (err) {
console.log('error:', err);
return;
}
console.log('success:', JSON.stringify(data, null, 2));
});