Multi-Valued (ARRAY) Comparisons
Columns stored in the content index can have multiple values, and those multi-valued columns can be compared with the ARRAY comparison predicate.
The syntax for the ARRAY comparison predicate follows:
...WHERE <column> <comp_op> [<quantifier>] <comparison_list>
An error is returned if the column reference is not a multi-valued 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 multi-valued comparison, the comparison operators have slightly different meanings depending on whether a quantifier is used, and which one is used. For this reason, the functions of the comparison operators are given in the tables describing each quantifier.
The comparison list specifies an array of literal values that are compared against the multi-valued 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, as shown in bold. Do not surround individual elements of the comparison list by square brackets. The non-bold square brackets in the syntax indicate that part is optional. Therefore, ARRAY [1,2,3] is legal, but ARRAY [1[,2][,3]] is not.
The method used to determine whether the multi-valued comparison returns true or false is specified by the optional quantifier. The following sections describe each quantifier, and how each comparison operator functions when the quantifier is used.
Absent Quantifier
If no quantifier is specified, each element on the left-hand (LH) side of the comparison is compared to the element in the same position on the right-hand (RH) side. The comparison begins with the first element in the arrays, and progresses through the last element. If all the elements on the LH side are equivalent to the corresponding elements on the RH side, then 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 qualifier is specified and provides a brief description of each.
| Operator | Description |
|---|---|
| = | 'Equal to' returns true when each LH element has the same value as the corresponding RH element, and both arrays have the same number of elements. |
| != or <> | 'Not equal to' returns true when one or more LH elements have values that differ from the corresponding RH elements, or when the LH and RH arrays do not have the same number of elements. |
| > | 'Greater than' returns true when the value of each LH element is greater than the value of the corresponding RH element. If all the LH element values exactly match the corresponding RH elements, and the RH array has elements with no corresponding LH elements, 'greater than' returns true. |
| >= | 'Greater than or equal to' returns true when the value of every LH element is greater than or equal to the value of the corresponding RH element. |
| < | 'Less than' returns true when the value of each LH element is less than the value of the corresponding RH element. 'Less than' also returns true when the LH side has fewer elements than the RH side. |
| <= | 'Less than or equal to' returns true when the value of every LH element is less than or equal to the value of the corresponding RH element. |
ALL Quantifier
The ALL quantifier specifies that each element in the left-hand (LH) side is compared against every element on the right-hand (RH) side. To return true, the comparison must be true for all elements on the LH side when compared to every element on the RH side. The number of elements in the LH and RH 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 LH element value is the same as every RH element value. |
| != or <> | 'Not equal to' returns true when one or more of the LH element values is different from any of the RH element values. |
| > | 'Greater than' returns true when every LH element value is greater than every RH element value. |
| >= | 'Greater than or equal to' returns true when every LH element value is greater than or equal to every RH element value. |
| < | 'Less than' returns true when every LH element value is less than every RH element value. |
| <= | 'Less than or equal to' returns true when every LH element value is less than or equal to every RH element value. |
SOME (or ANY) Quantifier
The SOME quantifier and the ANY quantifier can be used interchangeably. The SOME quantifier specifies that each element in the left-hand (LH) side is compared against every element on the right-hand (RH) side. To return true, the comparison must be true for at least one of the elements on the LH side when compared to every element on the RH side. The number of elements on the LH and RH 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 LH element values is the same as any of the RH element values. |
| != or <> | 'Not equal to' returns true when none of the LH element values is the same as any of the RH element values. |
| > | 'Greater than' returns true when at least one of the LH element values is greater than any one of the RH element values. |
| >= | 'Greater than or equal to' returns true when at least one of the LH element values is greater than or equal to any one of the RH element values. |
| < | 'Less than' returns true when at least one of the LH element values is less than any one of the RH element values. |
| <= | 'Less than or equal to' returns true when at least one of the LH element values is less than or equal to any one of the RH element values. |
Examples
The following example checks whether documents are in the "Finance" or "Planning" categories:
...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-hand 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]]