AnalyticDB for MySQL version 3.1.1 and later support the ARRAY and MAP types. This topic describes the definition, precautions, and examples of the ARRAY type.

Definition

An array is used to store data and supports duplicate elements, which is similar to a list in Java. All data within an array is of the same data type. For example, if array<int> is defined for Column A, the child elements in Column A are all of the INT type. Nested structures are supported. Example: array<array<string>>.

Precautions

You cannot create indexes for columns of the ARRAY or MAP type. We recommend that you combine search conditions with SQL statements to filter data instead of directly filtering data. Minimize the amount of data to be scanned.

Examples

Create a table
Create Table `array_test` (
 `a` int,
 `b` array<int>,
 `c` array<array<string>>,
 primary key (`a`)
) DISTRIBUTE BY HASH(`a`)
Write data
For example, you can insert a row of data where b is set to [1,2,3] and c is set to [["a"],["b","c"]].
insert into array_test values (1, '[1,2,3]', '[["a"],["b","c"]]');
Query data
mysql> select * from array_test;
+------+---------+-------------------+
| a    | b       | c                 |
+------+---------+-------------------+
|    1 | [1,2,3] | [["a"],["b","c"]] |
+------+---------+-------------------+
1 row in set (0.08 sec)
Notice
  • An array subscript starts from 1 instead of 0.
  • You can use b[1] to obtain the address of data in an array, which is equivalent to the element_at(b, 1) function.
mysql> select a,b[1],element_at(b,1),c[2],element_at(c,2) from array_test;
+------+------+-----------------+-----------+-----------------+
| a    | b[1] | element_at(b,1) | c[2]      | element_at(c,2) |
+------+------+-----------------+-----------+-----------------+
|    1 |    1 |               1 | ["b","c"] | ["b","c"]       |
+------+------+-----------------+-----------+-----------------+
1 row in set (0.11 sec)

Columns of the ARRAY or MAP type are returned in query results in the JSON format. For example, if c[2] corresponds to a nested subcolumn and its type is defined as array<string>, this subcolumn is returned in the JSON format.

Supported functions

Function Description Return type
element_at Returns a value. The subscript starts from 1. Example: element_at(array[1,2], 1) ==> 1. T
size Returns the number of elements. int
contains Specifies whether an array contains child elements. Example: contains(array[1,2], 2) ==> 1. BOOL
array_max Returns the maximum child element in an array. T
array_min Returns the minimum child element in an array. T
array_position Returns the index of the first occurrence of an element in an array. Example: array_position(array['a','b','b'],'b') ==>2. int
array_remove Removes all child elements equal to the given value from an array. Example: array_remove(array['a','b','b'],'b')==>['a']. array<T>
array_sort Sorts an array. Example: array_sort(array[3,2,1]) ==> [1,2,3]. array<T>
reverse Reverses the order of the child elements in an array. Example: reverse(array[5,9,3]) ==>[3,9,5]. array<T>
shuffle Randomizes the order of the elements in an array. Example: shuffle(array[1,5,8])==> [5,1,8]. array<T>
slice Extracts the selected child elements of an array. Example: array slice(array[1,2,3,4,5], 3,2) ==> [3,4]. array<T>
concat Merges the child elements of two or more arrays without removing duplicates. Example: concat(array[1], array[1,2]) ==>[1,1,2]. array<T>
array_distinct Removes the duplicate child elements from an array. Example: array_distinct(array[1,1,2]) ==>[1,2]. array<T>
array_union Merges the child elements of two or more arrays without duplicates. Example: array_union(array[1], array[1,2])==>[1,2]. array<T>
array_intersect Compares the elements of two or more arrays and returns the matches. Example: array_intersect(array[1], array[1,2]) ==>[1]. array<T>
array_join Concatenates the elements in an array by using the given delimiter, which is similar to Joiner. Example: array_join(array[1,2,3,4],'a') ==>1a2a3a4. string
flatten Flattens a nested array into a single array. Example: flatten(array[array[1,2],array[3]])==>[1,2,3]. array<X>