Array decision functions

Array decision functions perform some checks on the input array data and return a Boolean value. seekdb currently supports the following array decision functions: array_contains(), array_contains_all(), and array_overlaps(). It also supports the ANY() operator.

array_contains

The array_contains() function checks whether an array contains a specified element. The syntax is as follows:

array_contains(arr1, element)

The input parameters are described as follows:

• arr1 must be an array.
• element can be of any type.

The function returns 1 if the array contains the element, and 0 otherwise.

Here are some examples:

SELECT array_contains([1,2,3], 2);
+----------------------------+
| array_contains([1,2,3], 2) |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.001 sec)

SELECT array_contains([1,2,3], 6);
+----------------------------+
| array_contains([1,2,3], 6) |
+----------------------------+
|                          0 |
+----------------------------+
1 row in set (0.001 sec)

SELECT array_contains(["hello", "hi"], "hel");
+----------------------------------------+
| array_contains(["hello", "hi"], "hel") |
+----------------------------------------+
|                                      0 |
+----------------------------------------+
1 row in set (0.001 sec)

SELECT array_contains(["hello", "hi"], "hi");
+---------------------------------------+
| array_contains(["hello", "hi"], "hi") |
+---------------------------------------+
|                                     1 |
+---------------------------------------+
1 row in set (0.001 sec)

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 (0.001 sec)

array_contains_all

The array_contains_all() function checks whether an array contains all elements of another array. The syntax is as follows:

array_contains_all(arr1, arr2)

The input parameters are described as follows:

• arr1 must be an array.
• arr2 must be an array.

The function returns 1 if the array contains all elements of the other array, and 0 otherwise.

The input parameters arr1 and arr2 must have the same (or convertible) element types. Otherwise, the function returns an error.

Here are some examples:

SELECT array_contains_all([1,2,3], [2]);
+----------------------------------+
| array_contains_all([1,2,3], [2]) |
+----------------------------------+
|                                1 |
+----------------------------------+
1 row in set (0.001 sec)

SELECT array_contains_all([2], [1,2,3]);
+----------------------------------+
| array_contains_all([2], [1,2,3]) |
+----------------------------------+
|                                0 |
+----------------------------------+
1 row in set (0.001 sec)

SELECT array_contains_all([1,2,3], null);
+-----------------------------------+
| array_contains_all([1,2,3], null) |
+-----------------------------------+
|                              NULL |
+-----------------------------------+
1 row in set (0.001 sec)

SELECT array_contains_all([1,2,3], [2.0]);
+------------------------------------+
| array_contains_all([1,2,3], [2.0]) |
+------------------------------------+
|                                  1 |
+------------------------------------+
1 row in set (0.001 sec)

SELECT array_contains_all(["hello", "hi"], ["hi"]);
+---------------------------------------------+
| array_contains_all(["hello", "hi"], ["hi"]) |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+
1 row in set (0.001 sec)

SELECT array_contains_all([[1,2],[3,4]], [[3.0,4.0]]);
+------------------------------------------------+
| array_contains_all([[1,2],[3,4]], [[3.0,4.0]]) |
+------------------------------------------------+
|                                              1 |
+------------------------------------------------+
1 row in set (0.001 sec)

SELECT array_contains_all([["hello", "world"], ["hi", "what"], ["are you?"]], [["are you?"]]);
+----------------------------------------------------------------------------------------+
| array_contains_all([["hello", "world"], ["hi", "what"], ["are you?"]], [["are you?"]]) |
+----------------------------------------------------------------------------------------+
|                                                                                      1 |
+----------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

array_overlaps

The array_overlaps() function checks whether two arrays have any common elements. The syntax is as follows:

array_overlaps(arr1, arr2)

The input parameters are described as follows:

• arr1 must be an array.
• arr2 must be an array.

The function returns 1 if the arrays have common elements, and 0 otherwise.

The input parameters arr1 and arr2 must have the same (or convertible) element types. Otherwise, the function returns an error.

Here are some examples:

SELECT array_overlaps([1,2,3], [2]);
+------------------------------+
| array_overlaps([1,2,3], [2]) |
+------------------------------+
|                            1 |
+------------------------------+
1 row in set (0.001 sec)

SELECT array_overlaps([1,2,3], null);
+-------------------------------+
| array_overlaps([1,2,3], null) |
+-------------------------------+
|                          NULL |
+-------------------------------+
1 row in set (0.001 sec)

SELECT array_overlaps([1,2,3], [2.0]);
+--------------------------------+
| array_overlaps([1,2,3], [2.0]) |
+--------------------------------+
|                              1 |
+--------------------------------+
1 row in set (0.001 sec)

SELECT array_overlaps([1.1,2.2,3.3], [2.2]);
+--------------------------------------+
| array_overlaps([1.1,2.2,3.3], [2.2]) |
+--------------------------------------+
|                                    1 |
+--------------------------------------+
1 row in set (0.001 sec)

SELECT array_overlaps(["hello", "hi"], ["hi"]);
+-----------------------------------------+
| array_overlaps(["hello", "hi"], ["hi"]) |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+
1 row in set (0.001 sec)

SELECT array_overlaps([[1,2],[3,4]], [[3,4]]);
+----------------------------------------+
| array_overlaps([[1,2],[3,4]], [[3,4]]) |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
1 row in set (0.001 sec)

SELECT array_overlaps([[1,2],[3,4]], [[3.0,4.0]]);
+--------------------------------------------+
| array_overlaps([[1,2],[3,4]], [[3.0,4.0]]) |
+--------------------------------------------+
|                                          1 |
+--------------------------------------------+
1 row in set (0.001 sec)

SELECT array_overlaps([["hello", "world"], ["hi", "what"], ["are you?"]], [["are you?"]]);
+------------------------------------------------------------------------------------+
| array_overlaps([["hello", "world"], ["hi", "what"], ["are you?"]], [["are you?"]]) |
+------------------------------------------------------------------------------------+
|                                                                                  1 |
+------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

ANY

The ANY() operator has the same functionality, input parameters, and return values as the array_contains() function. The syntax is slightly different:

element = ANY(arr1)

Here are some examples:

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]]);