MultiValued (ARRAY) Comparisons
Columns stored in the content index can have multiple values, and those multivalued columns can be compared by using the ARRAY comparison predicate.
The ARRAY comparison predicate has the following syntax:
...WHERE <column> <comp_op> [<quantifier>] <comparison_list>
...WHERE <column> <comp_op> <value>
An error is returned if the column reference is not a multivalued column. The column data type must be compatible with the elements of the comparison list. If necessary, the column reference can be cast as another data type.
The comparison operator (comp_op) can be any of the normal comparison operators. In a multivalued comparison, the comparison operators have slightly different meanings depending on whether a quantifier is used. Quantifiers identify whether a comparison should be made against all or some of the values in the comparison list. The functions of the comparison operators are given in the tables describing each quantifier (ALL and SOME) later in this document.
The value after the operator specifies a single literal value that is compared against all elements of the multivalued column. If any element matches the value, the predicate is true.
The comparison list specifies an array of literal values that are compared against the multivalued column. The syntax for the comparison list follows:
ARRAY '['<literal> [,<literal>]']'
Important
Be aware of the comparison list syntax. The group of literals that make up the comparison list must be surrounded by square brackets. Do not surround individual elements of the comparison list by square brackets. Therefore, ARRAY [1] and ARRAY [1,2,3] are valid, but ARRAY [1[,2][,3]] is not.
The method used to determine whether the multivalued comparison returns true or false is specified by the optional quantifier. The following sections describe each quantifier, and how each comparison operator works when the quantifier is used.
Absent Quantifier
If no quantifier is specified, each element on the left side of the comparison is compared to the element in the same position on the right side. The comparison begins with the first element in the arrays, and progresses through the last element. If all the elements on the left side are equivalent to the corresponding elements on the right side, the number of array elements is used to determine which array is greater.
The following table shows the operation of the comparison operators when no quantifier is specified and provides a brief description of each.
Operator  Description 

=  'Equal to' returns true when each leftside element has the same value as the corresponding rightside element, and both arrays have the same number of elements. 
!= or <>  'Not equal to' returns true when one or more leftside elements have values that differ from the corresponding rightside elements, or when the leftside and rightside arrays do not have the same number of elements. 
>  'Greater than' returns true when the value of each leftside element is greater than the value of the corresponding rightside element. If all the leftside element values exactly match the corresponding rightside elements and the leftside array has more elements than the rightside array, 'greater than' returns true. 
>=  'Greater than or equal to' returns true when the value of every leftside element is greater than or equal to the value of the corresponding rightside element. If all the leftside element values are equal to or greater than the corresponding rightside elements and the leftside array has the same or more elements than the rightside array, 'greater than' returns true. 
<  'Less than' returns true when the value of each leftside element is less than the value of the corresponding rightside element. 'Less than' also returns true when the leftside side has fewer elements than the rightside side. 
<=  'Less than or equal to' returns true when the value of every leftside element is less than or equal to the value of the corresponding rightside element. If all the leftside element values are equal to or less than the corresponding rightside elements and the leftside array has the same or fewer elements than the rightside array, 'greater than' returns true. 
ALL Quantifier
The ALL quantifier specifies that each element in the left side is compared against every element on the right side. To return true, the comparison must be true for each element on the left side when compared to every element on the right side. The number of elements in the left and right array sides has no effect on the result.
The following table shows how each comparison operator functions with the ALL quantifier.
Operator  Description 

=  'Equal to' returns true when each leftside element value is the same as every rightside element value. 
!= or <>  'Not equal to' returns true when at least one of the leftside element values is different from any of the rightside element values. 
>  'Greater than' returns true when each leftside element value is greater than every rightside element value. 
>=  'Greater than or equal to' returns true when each leftside element value is greater than or equal to every rightside element value. 
<  'Less than' returns true when each leftside element value is less than every rightside element value. 
<=  'Less than or equal to' returns true when each leftside element value is less than or equal to every rightside element value. 
SOME (or ANY) Quantifier
The SOME quantifier and the ANY quantifier can be used interchangeably. Like the ALL quantifier, the SOME quantifier specifies that each element in the left side is compared against every element on the right side. To return true, the comparison must be true for at least one of the elements on the left side when compared to any element on the right side. The number of elements on the left and right side arrays has no effect on the result.
The following table shows how each comparison operator functions with the SOME quantifier.
Operator  Description 

=  'Equal to' returns true when at least one of the leftside element values is the same as any of the rightside element values. 
!= or <>  'Not equal to' returns true when none of the leftside element values is the same as any of the rightside element values. 
>  'Greater than' returns true when at least one of the leftside element values is greater than any one of the rightside element values. 
>=  'Greater than or equal to' returns true when at least one of the leftside element values is greater than or equal to any one of the rightside element values. 
<  'Less than' returns true when at least one of the leftside element values is less than any one of the rightside element values. 
<=  'Less than or equal to' returns true when at least one of the leftside element values is less than or equal to any one of the rightside element values. 
Examples
The following example checks whether documents are in the "Finance" or "Planning" categories:
SELECT System.ItemUrl FROM SystemIndex WHERE System.Category =
SOME ARRAY['Finance','Planning']
The following comparisons all evaluate true. Remember that in actual use, the search query syntax requires the left side to be a property, not a literal value.
ARRAY [1,2] > ARRAY [1,1]
ARRAY [1,2] > ARRAY [1,1,2]
ARRAY [1,2] < ARRAY [1,2,3]
ARRAY [1,2] = SOME ARRAY [1,12,27,35,2]
ARRAY [1,1] != ALL ARRAY [1,2]
ARRAY [1,20,21,22] < SOME ARRAY [0,40]
ARRAY [1,20,21,22] < ANY ARRAY [0,40]
Related topics

Reference

Conceptual