An array judgment function performs logic judgment on the input array data and returns a Boolean value. OceanBase Database supports the array_contains() array judgment function and the ANY() operator.
array_contains()
The array_contains() function verifies whether a specific element is included in an array. The syntax is as follows:
array_contains(arr1, element)
The arguments are described as follows:
- The data passed in by using the
arr1argument must be of the ARRAY type. - The element specified by the
elementargument can be of any type.
The returned value 1 indicates that the specified element is included in the array, and 0 indicates that the element is not included in the array.
Here are some examples:
SELECT array_contains([1,2,3], 2);
+----------------------------+
| array_contains([1,2,3], 2) |
+----------------------------+
| 1 |
+----------------------------+
1 row in set
SELECT array_contains([1,2,3], 6);
+----------------------------+
| array_contains([1,2,3], 6) |
+----------------------------+
| 0 |
+----------------------------+
1 row in set
SELECT array_contains(["hello", "hi"], "hel");
+----------------------------------------+
| array_contains(["hello", "hi"], "hel") |
+----------------------------------------+
| 0 |
+----------------------------------------+
1 row in set
SELECT array_contains(["hello", "hi"], "hi");
+---------------------------------------+
| array_contains(["hello", "hi"], "hi") |
+---------------------------------------+
| 1 |
+---------------------------------------+
1 row in set
SELECT array_contains([[1,2],[3,4]], [3.0,4.0]);
+------------------------------------------+
| array_contains([[1,2],[3,4]], [3.0,4.0]) |
+------------------------------------------+
| 1 |
+------------------------------------------+
1 row in set
ANY
The functionality, input arguments, and return values of the ANY() operator are the same as those of the array_contains() function. The difference between the two lies in the syntax.
element = ANY(arr1)
Here is an example:
SELECT 2 = ANY([1,2,3]);
SELECT 6 = ANY([1,2,3]);
SELECT "hel" = ANY(["hello", "hi"]);
SELECT "hi" = ANY(["hello", "hi"]);
SELECT [3.0,4.0] = ANY([[1,2],[3,4]]);