ARRAY_CONTAINS

array_contains

SinceVersion 1.2.0

Description

Checks whether an array contains a specified value. Returns true if found, false otherwise. If the array is NULL, returns NULL.

Syntax

array_contains(ARRAY<T> arr, T value)

Parameters

arr：ARRAY type, the array to check. Supports column names or constant values.
value：T type, the value to search for. Type must be compatible with array element type.

T supported types:

Numeric types: TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE, DECIMAL
String types: CHAR, VARCHAR, STRING
Date and time types: DATE, DATETIME, DATEV2, DATETIMEV2
Boolean type: BOOLEAN
IP types: IPV4, IPV6

Return Value

Return type: BOOLEAN

Return value meaning:

true: if the array contains the specified value
false: if the array does not contain the specified value
NULL: if the input array is NULL

Return value behavior description:

Boundary condition behavior:
- Returns false when the input array is empty
- Returns NULL when the input array is NULL
- Returns false when array element type does not match the search value type
- For null values in array elements: null elements are processed normally, can search for null elements in arrays
Exception value behavior:
- Returns unsupported error when array elements are of unsupported types
Cases that return NULL:
- When the input array is NULL

Type compatibility rules:

Numeric type compatibility:
- Integer types can be compared with each other (TINYINT, SMALLINT, INT, BIGINT, LARGEINT)
- Floating-point types can be compared with each other (FLOAT, DOUBLE)
- Decimal types can be compared with each other (DECIMAL32, DECIMAL64, DECIMAL128I, DECIMALV2, DECIMAL256)
- Integers and floating-point numbers can be compared with each other
String type compatibility:
- CHAR, VARCHAR, STRING types can be compared with each other
Date and time type compatibility:
- DATE and DATEV2 can be compared with each other
- DATETIME and DATETIMEV2 can be compared with each other

Examples

Table creation example

CREATE TABLE array_contains_test (
    id INT,
    int_array ARRAY<INT>,
    string_array ARRAY<STRING>
)
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 3
PROPERTIES (
    "replication_num" = "1"
);

-- Insert test data
INSERT INTO array_contains_test VALUES
(1, [1000, 2000, 3000], ['apple', 'banana', 'cherry']),
(2, [], []),
(3, NULL, NULL),
(4, [1000, null, 3000], ['apple', null, 'cherry']);

Query examples:

Check if an array contains a specific integer value: This example returns false because 5 is not in int_array.

SELECT array_contains(int_array, 5) FROM array_contains_test WHERE id = 1;
+-------------------------------+
| array_contains(int_array, 5)  |
+-------------------------------+
| 0                             |
+-------------------------------+

Check if a string array contains a specific string: This example returns true because 'banana' is in string_array.

SELECT array_contains(string_array, 'banana') FROM array_contains_test WHERE id = 1;
+------------------------------------------+
| array_contains(string_array, 'banana')   |
+------------------------------------------+
| 1                                        |
+------------------------------------------+

Currently it is an empty array. This example returns false because there are no values in the empty array.

SELECT array_contains(int_array, 1000) FROM array_contains_test WHERE id = 2;
+----------------------------------+
| array_contains(int_array, 1000)  |
+----------------------------------+
| 0                                |
+----------------------------------+

Currently it is a NULL array, this example returns NULL.

SELECT array_contains(int_array, 1000) FROM array_contains_test WHERE id = 3;
+----------------------------------+
| array_contains(int_array, 1000)  |
+----------------------------------+
| NULL                             |
+----------------------------------+

Check if an array contains null In this example, the value_expr parameter is null, and there are no null elements in the array, so it returns false.

SELECT array_contains([1, 2, 3], null);
+---------------------------------+
| array_contains([1, 2, 3], null) |
+---------------------------------+
|                               0 |
+---------------------------------+

Check if an array contains null In this example, the value_expr parameter is null, and the array contains SQL null values, so it returns true.

SELECT array_contains([null, 1, 2], null);
+------------------------------------+
| array_contains([null, 1, 2], null) |
+------------------------------------+
|                                  1 |
+------------------------------------+

When the search value type is incompatible with array element type, returns false.

SELECT array_contains([1, 2, 3], 'string');
+-------------------------------------+
| array_contains([1, 2, 3], 'string') |
+-------------------------------------+
| 0                                   |
+-------------------------------------+

When the search value type cannot be type-converted with array elements, an error is returned

SELECT array_contains([1, 2, 3], [4, 5, 6]);
ERROR 1105 (HY000): errCode = 2, detailMessage = can not cast from origin type ARRAY<TINYINT> to target type=TINYINT

Unsupported complex types will throw an error. In this example, the array is a nested array type, returning an unsupported error.

SELECT array_contains([[1,2],[2,3]], [1,2]);
ERROR 1105 (HY000): errCode = 2, detailMessage = (10.16.10.6)[RUNTIME_ERROR]execute failed or unsupported types for function array_contains(Array(Nullable(Array(Nullable(TINYINT)))), Array(Nullable(TINYINT)))

Notes

Performance considerations: When dealing with large arrays, if performance is a major concern, you can use inverted indexes for accelerated queries, but there are some usage restrictions to note:

The property for creating an array inverted index can only be a non-tokenized index
The element type T of the array must be a data type that supports creating inverted indexes
If the query condition parameter T is NULL, the index cannot be used for acceleration
Index acceleration only occurs when the function is used as a predicate filter condition

-- Table creation example
CREATE TABLE `test_array_index` (
    `apply_date` date NULL COMMENT '',
    `id` varchar(60) NOT NULL COMMENT '',
    `inventors` array<text> NULL COMMENT '' -- Add non-tokenized inverted index to array column when creating table
  ) ENGINE=OLAP
  DUPLICATE KEY(`apply_date`, `id`)
  COMMENT 'OLAP'
  DISTRIBUTED BY HASH(`id`) BUCKETS 1
  PROPERTIES (
  "replication_allocation" = "tag.location.default: 1",
  "is_being_synced" = "false",
  "storage_format" = "V2",
  "light_schema_change" = "true",
  "disable_auto_compaction" = "false",
  "enable_single_replica_compaction" = "false"
  );
-- Query example
SELECT id, inventors FROM test_array_index WHERE array_contains(inventors, 'x') ORDER BY id;

Keywords

ARRAY, CONTAIN, CONTAINS, ARRAY_CONTAINS

array_contains​

Description​

Syntax​

Parameters​

Return Value​

Examples​

Notes​

Keywords​