Store Nested Data Using the ARRAY Type - AnalyticDB for MySQL

AnalyticDB for MySQL 3.1.1 and later support the ARRAY and MAP data types. This page describes the ARRAY type: its definition, limitations, usage examples, and supported functions.

Definition

An ARRAY stores a collection of elements of the same data type and allows duplicates—similar to a list in Java. All elements in an array must share the same type. For example, defining array<int> for a column means every element in that column is of the INT type.

Nested arrays are supported. Example: array<array<string>>.

Limitations

Important

Indexes cannot be created on ARRAY or MAP columns.

To filter array data efficiently, combine search conditions in your SQL queries rather than scanning full arrays. Minimizing the data scanned improves query performance.

Work with arrays

Create a table

The following statement creates a table with two array columns: b of type array<int> and c of type array<array<string>>.

Create Table `array_test` (
  `a` int,
  `b` array<int>,
  `c` array<array<string>>,
  primary key (`a`)
) DISTRIBUTED BY HASH(`a`)

Insert data

Insert array values as JSON strings. The following statement inserts a row where b is [1,2,3] and c is [["a"],["b","c"]].

insert into array_test values (1, '[1,2,3]', '[["a"],["b","c"]]');

Query data

Query the table to retrieve all rows:

mysql> select * from array_test;
+------+---------+-------------------+
| a    | b       | c                 |
+------+---------+-------------------+
|    1 | [1,2,3] | [["a"],["b","c"]] |
+------+---------+-------------------+
1 row in set (0.08 sec)

ARRAY and MAP columns are returned in JSON format. For example, c[2], which is a nested array<string> subcolumn, is returned as a JSON array.

Access array elements

Use bracket notation or the element_at function to retrieve elements by subscript.

Important

Array subscripts start from 1, not 0. b[1] and element_at(b, 1) are equivalent.

The following table shows both access syntaxes and their results:

Expression	Result
`b[1]`	`1` (first element of `b`)
`element_at(b, 1)`	`1` (first element of `b`)
`c[2]`	`["b","c"]` (second element of `c`)
`element_at(c, 2)`	`["b","c"]` (second element of `c`)

Full query example:

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)

Supported functions

Function	Description	Return type
`element_at`	Returns the element at the given subscript (1-based). Example: `element_at(array[1,2], 1)` returns `1`.	T
`size`	Returns the number of elements in an array.	int
`contains`	Returns whether an array contains the specified value. Example: `contains(array[1,2], 2)` returns `1`.	BOOL
`array_max`	Returns the maximum element in an array.	T
`array_min`	Returns the minimum element in an array.	T
`array_position`	Returns the index of the first occurrence of an element. Example: `array_position(array['a','b','b'],'b')` returns `2`.	int
`array_remove`	Removes all elements equal to the specified value. Example: `array_remove(array['a','b','b'],'b')` returns `['a']`.	array<T>
`array_sort`	Sorts an array. Example: `array_sort(array[3,2,1])` returns `[1,2,3]`.	array<T>
`reverse`	Reverses the order of elements. Example: `reverse(array[5,9,3])` returns `[3,9,5]`.	array<T>
`shuffle`	Returns the array with elements in random order. Example: `shuffle(array[1,5,8])` may return `[5,1,8]`.	array<T>
`slice`	Returns a subarray. `start` is the 1-based starting index; `length` is the number of elements to return. Example: `slice(array[1,2,3,4,5], 3, 2)` returns `[3,4]`.	array<T>
`concat`	Merges two or more arrays, keeping duplicates. Example: `concat(array[1], array[1,2])` returns `[1,1,2]`.	array<T>
`array_distinct`	Removes duplicate elements from an array. Example: `array_distinct(array[1,1,2])` returns `[1,2]`.	array<T>
`array_union`	Merges two or more arrays and removes duplicates. Example: `array_union(array[1], array[1,2])` returns `[1,2]`.	array<T>
`array_intersect`	Returns elements that appear in all input arrays. Example: `array_intersect(array[1], array[1,2])` returns `[1]`.	array<T>
`array_join`	Concatenates array elements into a string using the specified delimiter. Example: `array_join(array[1,2,3,4],'a')` returns `1a2a3a4`.	string
`flatten`	Flattens a nested array into a single-level array. Example: `flatten(array[array[1,2],array[3]])` returns `[1,2,3]`.	array<X>