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`) ) DISTRIBUTED 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)ImportantAn 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> |