Boolean query - Tablestore - Alibaba Cloud Documentation Center

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

An OTSClient instance 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
table_name	The name of the data table.
index_name	The name of the search index.
must_queries	The list of subqueries that the query results must match. This parameter is equivalent to the AND operator.
must_not_queries	The list of subqueries that the query results must not match. This parameter is equivalent to the NOT operator.
filter_queries	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.
should_queries	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 should_queries are returned. A higher overall relevance score indicates that more subquery conditions specified by should_queries are met.
minimum_should_match	The minimum number of subquery conditions specified by should_queries that the rows must meet.If no other subquery conditions except the subquery conditions that are specified by should_queries are specified, the default value of the minimum_should_match parameter is 1. If other subquery conditions, such as subquery conditions specified by must_queries, must_not_queries, and filter_queries, are specified, the default value of the minimum_should_match parameter is 0.

Examples

In the following example, condition A requires that the value of the keyword column exactly matches keyword, and condition B requires that the value of the long column is greater than or equal to 100 and less than 101.

Example 1

The following sample code provides an example on how to perform a Boolean query to query rows that meet condition A and condition B:

$request = array(
    'table_name' => 'php_sdk_test',
    'index_name' => 'php_sdk_test_search_index',
    'search_query' => array(
        'offset' => 0,
        'limit' => 2,
        'get_total_count' => true,
        'query' => array(
            'query_type' => QueryTypeConst::BOOL_QUERY,
            'query' => array(
                'must_queries' => array(
                    array(
                        'query_type' => QueryTypeConst::TERM_QUERY,
                        'query' => array(
                            'field_name' => 'keyword',
                            'term' => 'keyword'
                        )
                    ),
                    array(
                        'query_type' => QueryTypeConst::RANGE_QUERY,
                        'query' => array(
                            'field_name' => 'long',
                            'range_from' => 100,
                            'include_lower' => true,
                            'range_to' => 101,
                            'include_upper' => false
                        )
                    )
                ),
            )
        ),
        'sort' => array(
            array(
                'field_sort' => array(
                    'field_name' => 'keyword',
                    'order' => SortOrderConst::SORT_ORDER_ASC
                )
            ),
        )
    ),
    'columns_to_get' => array(
        'return_type' => ColumnReturnTypeConst::RETURN_SPECIFIED,
        'return_names' => array('keyword', 'long')
    )
);
$response = $otsClient->search($request);

Example 2

The following sample code provides an example on how to perform a Boolean query to query rows that do not meet both condition A and condition B:

$request = array(
    'table_name' => 'php_sdk_test',
    'index_name' => 'php_sdk_test_search_index',
    'search_query' => array(
        'offset' => 0,
        'limit' => 2,
        'get_total_count' => true,
        'query' => array(
            'query_type' => QueryTypeConst::BOOL_QUERY,
            'query' => array(
                'must_not_queries' => array(
                    array(
                        'query_type' => QueryTypeConst::TERM_QUERY,
                        'query' => array(
                            'field_name' => 'keyword',
                            'term' => 'keyword'
                        )
                    ),
                    array(
                        'query_type' => QueryTypeConst::RANGE_QUERY,
                        'query' => array(
                            'field_name' => 'long',
                            'range_from' => 100,
                            'include_lower' => true,
                            'range_to' => 101,
                            'include_upper' => false
                        )
                    )
                ),
            )
        ),
        'sort' => array(
            array(
                'field_sort' => array(
                    'field_name' => 'keyword',
                    'order' => SortOrderConst::SORT_ORDER_ASC
                )
            ),
        )
    ),
    'columns_to_get' => array(
        'return_type' => ColumnReturnTypeConst::RETURN_SPECIFIED,
        'return_names' => array('keyword', 'long')
    )
);
$response = $otsClient->search($request);

Example 3

The following sample code provides an example on how to perform a Boolean query to filter the rows and return rows that meet condition A and condition B. filter does not calculate the relevance score based on the number of subfilters that the rows match.

$request = array(
    'table_name' => 'php_sdk_test',
    'index_name' => 'php_sdk_test_search_index',
    'search_query' => array(
        'offset' => 0,
        'limit' => 2,
        'get_total_count' => true,
        'query' => array(
            'query_type' => QueryTypeConst::BOOL_QUERY,
            'query' => array(
                'filter_queries' => array(
                    array(
                        'query_type' => QueryTypeConst::TERM_QUERY,
                        'query' => array(
                            'field_name' => 'keyword',
                            'term' => 'keyword'
                        )
                    ),
                    array(
                        'query_type' => QueryTypeConst::RANGE_QUERY,
                        'query' => array(
                            'field_name' => 'long',
                            'range_from' => 100,
                            'include_lower' => true,
                            'range_to' => 101,
                            'include_upper' => false
                        )
                    )
                ),
            )
        ),
        'sort' => array(
            array(
                'field_sort' => array(
                    'field_name' => 'keyword',
                    'order' => SortOrderConst::SORT_ORDER_ASC
                )
            ),
        )
    ),
    'columns_to_get' => array(
        'return_type' => ColumnReturnTypeConst::RETURN_SPECIFIED,
        'return_names' => array('keyword', 'long')
    )
);
$response = $otsClient->search($request);

ShouldQueries(A OR B)

The following sample code provides an example on how to query rows that meet at least one of condition A and condition B:

$request = array(
    'table_name' => 'php_sdk_test',
    'index_name' => 'php_sdk_test_search_index',
    'search_query' => array(
        'offset' => 0,
        'limit' => 2,
        'get_total_count' => true,
        'query' => array(
            'query_type' => QueryTypeConst::BOOL_QUERY,
            'query' => array(
                'should_queries' => array(
                    array(
                        'query_type' => QueryTypeConst::TERM_QUERY,
                        'query' => array(
                            'field_name' => 'keyword',
                            'term' => 'keyword'
                        )
                    ),
                    array(
                        'query_type' => QueryTypeConst::RANGE_QUERY,
                        'query' => array(
                            'field_name' => 'long',
                            'range_from' => 100,
                            'include_lower' => true,
                            'range_to' => 101,
                            'include_upper' => false
                        )
                    )
                ),
                'minimum_should_match' => 1
            )
        ),
        'sort' => array(
            array(
                'field_sort' => array(
                    'field_name' => 'keyword',
                    'order' => SortOrderConst::SORT_ORDER_ASC
                )
            ),
        )
    ),
    'columns_to_get' => array(
        'return_type' => ColumnReturnTypeConst::RETURN_SPECIFIED,
        'return_names' => array('keyword', 'long')
    )
);
$response = $otsClient->search($request);

Example 4

The following sample code provides an example on how to perform a Boolean query that includes multiple subqueries of the BoolQuery type. In (col2 < 4 or col3 < 5) or (col2 = 4 and (col3 = 5 or col3 = 6)), each subquery of the BoolQuery type is connected by AND or OR.

$request = array(
    'table_name' => 'php_sdk_test',
    'index_name' => 'php_sdk_test_index',
    'search_query' => [
        'offset' => 0,
        'limit' => 10,
        'get_total_count' => false,
        'query' => [
            'query_type' => QueryTypeConst::BOOL_QUERY,
            'query' => [
                // Final combination: (col2 < 4 or col3 < 5) or (col2 = 4 and (col3 = 5 or col3 = 6)) 
                'should_queries' => [
                    [
                        'query_type' => QueryTypeConst::BOOL_QUERY,
                        'query' => [
                            // Combination 1: col2 < 4 or col3 < 5 
                            'should_queries' => [
                                [
                                    'query_type' => QueryTypeConst::RANGE_QUERY,
                                    // Condition 1: col2 < 4 
                                    'query' => [
                                        'field_name' => 'col2',
                                        'range_to' => 4
                                    ]
                                ],
                                [
                                    'query_type' => QueryTypeConst::RANGE_QUERY,
                                    // Condition 2: col3 < 5 
                                    'query' => [
                                        'field_name' => 'col3',
                                        'range_to' => 5
                                    ]
                                ]
                            ]
                        ]
                    ],
                    [
                        'query_type' => QueryTypeConst::BOOL_QUERY,
                        // Combination 2: (col2 = 4 and (col3 = 5 or col3 = 6)) 
                        'query' => [
                            'must_queries' => [
                                [
                                    'query_type' => QueryTypeConst::TERM_QUERY,
                                    // Condition 3: col2 = 4 
                                    'query' => [
                                        'field_name' => 'col2',
                                        'term' => 4
                                    ]
                                ],
                                [
                                    'query_type' => QueryTypeConst::BOOL_QUERY,
                                    // Combination 3: (col3 = 5 or col3 = 6) 
                                    'query' => [
                                        'should_queries' => [
                                            [
                                                'query_type' => QueryTypeConst::TERM_QUERY,
                                                // Condition 4: col3 = 5 
                                                'query' => [
                                                    'field_name' => 'col3',
                                                    'term' => 5
                                                ]
                                            ],
                                            [
                                                'query_type' => QueryTypeConst::TERM_QUERY,
                                                // Condition 5: col3 = 6 
                                                'query' => [
                                                    'field_name' => 'col3',
                                                    'term' => 6
                                                ]
                                            ]
                                        ],
                                        'minimum_should_match' => 1
                                    ]
                                ]
                            ]
                        ]
                    ]
                ],
                'minimum_should_match' => 1
            ]
        ]
    ],
    // You can configure the columns_to_get parameter to specify the columns to return or specify that all columns are returned. If you do not configure this parameter, only the primary key columns are returned. 
    'columns_to_get' => [ 
        //'return_type' => ColumnReturnTypeConst::RETURN_ALL // Specify that all columns are returned. 
        // Specify that the col1 and col2 columns are returned. 
        'return_type' => ColumnReturnTypeConst::RETURN_SPECIFIED, 
        'return_names' => array('col1', 'col2')
    ]
);