Array type (Public Preview)

UPSERT

You can write array data in one of the following two ways.

• Use square brackets ([]) or curly braces ({}) to enclose the array elements and write the array as a string. For example: '[element1, element2, ...]' or '{element1, element2, ...}'.

  Note

  If an element is a string, enclose it in double quotation marks (""). For example, '["a", "b", "c"]'.

  The following are examples:

  UPSERT INTO t1(id, intArr, integerArr, bigintArr, smallintArr, tinyintArr, floatArr, doubleArr, varcharArr, booleanArr) VALUES (1, '[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 (1, '{1, 2}', '{1, 2}', '{1, 2}', '{1, 2}', '{1, 2}', '{1.1, 2.2}', '{1.1, 2.2}', '{"a", "b"}', '{true, false}');

• Use the array constructor: ARRAY[element1, element2, ...].

  Note

  If an element is a string, enclose it in single quotation marks (''). For example, ARRAY['a', 'b', 'c'].

  The following is an example:

  UPSERT INTO t2(id, intArr, integerArr, bigintArr, smallintArr, tinyintArr, floatArr, doubleArr, varcharArr, booleanArr) VALUES (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]);

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 |
+------+------------------+