Array type (Public Preview)

UPSERT

Use the array constructor to write array data: ARRAY[element1, element2, ...]. You can also write arrays as strings: '[element1, element2, ...]' or '{element1, element2, ...}'. In string format, string-type elements must be enclosed in double quotation marks, for example, '["a", "b"]'. An error occurs if double quotation marks are missing.

Examples of the constructor syntax:

UPSERT INTO t1(id, intArr, integerArr, bigintArr, smallintArr, tinyintArr, floatArr, doubleArr, varcharArr, booleanArr) VALUES (1, ARRAY[1, 2], ARRAY[1, 2], ARRAY[1, 2], ARRAY[1, 2], ARRAY[1, 2], ARRAY[1.1, 2.2], ARRAY[1.1, 2.2], ARRAY['a', 'b'], ARRAY[true, false]);

UPSERT INTO t2(id, intArr, integerArr, bigintArr, smallintArr, tinyintArr, floatArr, doubleArr, varcharArr, booleanArr) VALUES (1, ARRAY[1, 2], ARRAY[1, 2], ARRAY[1, 2], ARRAY[1, 2], ARRAY[1, 2], ARRAY[1.1, 2.2], ARRAY[1.1, 2.2], ARRAY['a', 'b'], ARRAY[true, false]), (2, ARRAY[3, 4], ARRAY[3, 4], ARRAY[3, 4], ARRAY[3, 4], ARRAY[3, 4], ARRAY[3.3, 4.4], ARRAY[3.3, 4.4], ARRAY['c', 'd'], ARRAY[true, false]);

The following examples use the string syntax, which produces the same result as the constructor syntax above:

UPSERT INTO t1(id, intArr, integerArr, bigintArr, smallintArr, tinyintArr, floatArr, doubleArr, varcharArr, booleanArr) VALUES (3, '[1, 2]', '[1, 2]', '[1, 2]', '[1, 2]', '[1, 2]', '[1.1, 2.2]', '[1.1, 2.2]', '["a", "b"]', '[true, false]');

UPSERT INTO t2(id, intArr, integerArr, bigintArr, smallintArr, tinyintArr, floatArr, doubleArr, varcharArr, booleanArr) VALUES (3, '{1, 2}', '{1, 2}', '{1, 2}', '{1, 2}', '{1, 2}', '{1.1, 2.2}', '{1.1, 2.2}', '{"a", "b"}', '{true, false}');

UPDATE

Use the array_append function to add elements to the end of an array. You can add up to four elements at a time.

• Example 1: Use the array_append function to add one element to the end of an array.

  UPDATE t1 SET intArr = ARRAY_APPEND(intArr, 3), integerArr = ARRAY_APPEND(integerArr, 3), bigintArr = ARRAY_APPEND(bigintArr, 3), smallintArr = ARRAY_APPEND(smallintArr, 3), tinyintArr = ARRAY_APPEND(tinyintArr, 3), floatArr = ARRAY_APPEND(floatArr, 3.3), doubleArr = ARRAY_APPEND(doubleArr, 3.3), varcharArr = ARRAY_APPEND(varcharArr, 'c'), booleanArr = ARRAY_APPEND(booleanArr, true) WHERE id = 1;

• Example 2: Use the array_append function to add four elements to the end of an array.

  UPDATE t1 SET intArr = ARRAY_APPEND(intArr, 4, 5, 6, 7), integerArr = ARRAY_APPEND(integerArr, 4, 5, 6, 7), bigintArr = ARRAY_APPEND(bigintArr, 4, 5, 6, 7), smallintArr = ARRAY_APPEND(smallintArr, 4, 5, 6, 7), tinyintArr = ARRAY_APPEND(tinyintArr, 4, 5, 6, 7), floatArr = ARRAY_APPEND(floatArr, 4.4, 5.5, 6.6, 7.7), doubleArr = ARRAY_APPEND(doubleArr, 4.4, 5.5, 6.6, 7.7), varcharArr = ARRAY_APPEND(varcharArr, 'd', 'e', 'f', 'g'), booleanArr = ARRAY_APPEND(booleanArr, false, false, false, false) WHERE id = 1;

SELECT

Function overview

For array data, Lindorm provides the following functions to support more complex query requirements.

Containment check (array contains element)

Containment check (array contains array)

Use the ARRAY_CONTAINS(arr, array_literal) function to check if the array column arr contains every element of the array array_literal.

• Example 1: Check if the INT array column intArr contains every element of ARRAY[1,2].

  SELECT id, intArr FROM t1 WHERE id >= 0 AND id <= 10 AND ARRAY_CONTAINS(intArr, ARRAY[1,2]);

  Result:

  +------+-----------------+
  | id   | intArr          |
  +------+-----------------+
  |    1 | [1,2,3,4,5,6,7] |
  +------+-----------------+

• Example 2: Check if the DOUBLE array column doubleArr contains every element of ARRAY[1.1,1.1].

  SELECT id, doubleArr FROM t1 WHERE id >= 0 AND id <= 10 AND ARRAY_CONTAINS(doubleArr, ARRAY[1.1, 1.1]);

  Result:

  +------+-------------------------------+
  | id   | doubleArr                     |
  +------+-------------------------------+
  |    1 | [1.1,2.2,3.3,4.4,5.5,6.6,7.7] |
  +------+-------------------------------+

• Example 3: Check if the VARCHAR array column varcharArr contains every element of ARRAY['c', 'b', 'a'].

  SELECT id, varcharArr FROM t1 WHERE id >= 0 AND id <= 10 AND ARRAY_CONTAINS(varcharArr, ARRAY['c', 'b', 'a']);

  Result:

  +------+-------------------------------+
  | id   | varcharArr                    |
  +------+-------------------------------+
  |    1 | ["a","b","c","d","e","f","g"] |
  +------+-------------------------------+

• Example 4: Check if the BOOLEAN array column booleanArr contains every element of ARRAY[true, true, true].

  SELECT id, booleanArr FROM t1 WHERE id >= 0 AND id <= 10 AND ARRAY_CONTAINS(booleanArr, ARRAY[true, true, true]);

  Result:

  +------+-------------------------------------------+
  | id   | booleanArr                                |
  +------+-------------------------------------------+
  |    1 | [true,false,true,false,false,false,false] |
  +------+-------------------------------------------+

Array overlap check

Use the ARRAYS_OVERLAP(arr, array_literal) function to check if the array column arr and the array array_literal have any elements in common. If either parameter is null, the function returns null. If there are no common elements, or if the only common elements are null, the function returns false.

• Example 1: Check if the INT array column intArr and the array ARRAY[1, 10, 100] have common elements.

  SELECT id, intArr FROM t1 WHERE id >= 0 AND id <= 10 AND ARRAYS_OVERLAP(intArr, ARRAY[1, 10, 100]);

  Result:

  +------+-----------------+
  | id   | intArr          |
  +------+-----------------+
  |    1 | [1,2,3,4,5,6,7] |
  +------+-----------------+

• Example 2: Check if the DOUBLE array column doubleArr and the array ARRAY[1.1, 1.1] have common elements.

  SELECT id, doubleArr FROM t2 WHERE id >= 0 AND id <= 10 AND ARRAYS_OVERLAP(doubleArr, ARRAY[1.1, 1.1]);

  Result:

  +------+-----------+
  | id   | doubleArr |
  +------+-----------+
  |    1 | [1.1,2.2] |
  +------+-----------+

• Example 3: Check if the VARCHAR array column varcharArr and the array ARRAY['a', 'c'] have common elements.

  SELECT id, varcharArr FROM t2 WHERE id >= 0 AND id <= 10 AND ARRAYS_OVERLAP(varcharArr, ARRAY['a', 'c']);

  Result:

  +------+------------+
  | id   | varcharArr |
  +------+------------+
  |    1 | ["a","b"]  |
  |    2 | ["c","d"]  |
  +------+------------+

• Example 4: Check if the BOOLEAN array column booleanArr and the array ARRAY[true] have common elements.

  SELECT id, booleanArr FROM t2 WHERE id >= 0 AND id <= 10 AND ARRAYS_OVERLAP(booleanArr, ARRAY[true]);

  Result:

  +------+--------------+
  | id   | booleanArr   |
  +------+--------------+
  |    1 | [true,false] |
  |    2 | [true,false] |
  +------+--------------+

Count array elements

SELECT id, ARRAY_SIZE(varcharArr) AS varcharArraySize FROM t1;

+------+------------------+
| id   | varcharArraySize |
+------+------------------+
|    1 |                7 |
+------+------------------+

Prerequisites

Search indexes are enabled.

The search engine version is 3.11.0 or later.

The LTS engine version is 3.8.13.3 or later.

DDL

Columns of the array type can be used as index columns for search indexes. The syntax is the same as that for regular columns. For more information, see CREATE INDEX. The following example creates a search index for table t1.

CREATE INDEX idx_t1_array USING SEARCH ON t1 (id, intArr, integerArr, bigintArr, smallintArr, tinyintArr, floatArr, doubleArr, varcharArr, booleanArr);

Queries that support search indexes

After a search index is created, the usage and semantics of functions are the same as those in the Functions section. The following functions are supported. Other functions are not supported.

• Array contains element.

  SELECT id, varcharArr FROM t1 WHERE ARRAY_CONTAINS(varcharArr, 'c');
  
  SELECT id, intArr FROM t1 WHERE 6 = ANY(intArr);

• Array contains array.

  SELECT id, varcharArr FROM t1 WHERE ARRAY_CONTAINS(varcharArr, ARRAY['c', 'e']);

• Array overlap.

  SELECT id, intArr FROM t1 WHERE ARRAYS_OVERLAP(intArr, ARRAY[6, 100]);

Queries that do not support search indexes

The following queries do not support search indexes. Before creating a search index, make sure that your business queries do not include the following queries. These queries do not automatically fall back to a full table scan. Array equality and filter conditions based on the number of elements cause a Detect inefficient query error when no primary key range condition is available. Accessing elements by index (arr[i]) can still be executed.

• Access array elements by index: arr[i].

  SELECT id FROM t1 WHERE varcharArr[1] = 'a';

• Array equality: arr = array_literal.

  SELECT id FROM t1 WHERE varcharArr = ARRAY['a', 'b', 'c', 'd', 'e', 'f', 'g'];

• Filter by number of elements: CARDINALITY(arr) and ARRAY_SIZE(arr).

  SELECT id FROM t1 WHERE CARDINALITY(varcharArr) > 5;