docs/sql-manual/sql-functions/scalar-functions/array-functions/array-concat.md

{ “title”: “ARRAY_CONCAT”, “language”: “en”, “description”: "Concatenates all input arrays into a single array. The function accepts one or more arrays as parameters and connects them into a new array in the " }

array_concat

Description

Concatenates all input arrays into a single array. The function accepts one or more arrays as parameters and connects them into a new array in the order of the parameters.

Syntax

array_concat(ARRAY<T> arr1, [ARRAY<T> arr2, ...])

Parameters

arr1, arr2, ...：ARRAY<T> type, the arrays to concatenate. Supports column names or constant values.

T supported types:

Numeric types: TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE, DECIMAL
String types: CHAR, VARCHAR, STRING
Date and time types: DATE, DATETIME, DATEV2, DATETIMEV2
Boolean type: BOOLEAN
IP types: IPV4, IPV6
Complex data types: ARRAY, MAP, STRUCT

Return Value

Return type: ARRAY<T>

Return value meaning:

The concatenated new array containing all elements from the input arrays, maintaining the original order
NULL: if any input array is NULL

Usage notes:

Empty arrays will be ignored and will not add any elements
When there is only one array and it is empty, returns an empty array; when there is only one array and it is NULL, returns NULL
Complex types (nested arrays, MAP, STRUCT) require completely consistent structure when concatenating, otherwise an error will be thrown
For null values in array elements: null elements will be normally retained in the concatenation result

Examples

CREATE TABLE array_concat_test (
    id INT,
    int_array1 ARRAY<INT>,
    int_array2 ARRAY<INT>,
    string_array1 ARRAY<STRING>,
    string_array2 ARRAY<STRING>
)
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 3
PROPERTIES (
    "replication_num" = "1"
);

INSERT INTO array_concat_test VALUES
(1, [1, 2, 3], [4, 5, 6], ['a', 'b'], ['c', 'd']),
(2, [10, 20], [30, 40], [], ['x', 'y']),
(3, NULL, [100, 200], NULL, ['z']),
(4, [], [], [], []),
(5, [1, null, 3], [null, 5, 6], ['a', null, 'c'], ['d', 'e']);

Query examples:

Concatenating multiple array literals:

SELECT array_concat([1, 2], [7, 8], [5, 6]);
+--------------------------------------+
| array_concat([1, 2], [7, 8], [5, 6]) |
+--------------------------------------+
| [1, 2, 7, 8, 5, 6]                   |
+--------------------------------------+

String array concatenation:

SELECT array_concat(string_array1, string_array2) FROM array_concat_test WHERE id = 1;
+--------------------------------------------+
| array_concat(string_array1, string_array2) |
+--------------------------------------------+
| ["a", "b", "c", "d"]                       |
+--------------------------------------------+

Empty array concatenation:

SELECT array_concat([], []);
+----------------------+
| array_concat([], []) |
+----------------------+
| []                   |
+----------------------+

NULL array concatenation:

SELECT array_concat(int_array1, int_array2) FROM array_concat_test WHERE id = 3;
+--------------------------------------+
| array_concat(int_array1, int_array2) |
+--------------------------------------+
| NULL                                 |
+--------------------------------------+

Array concatenation containing null elements: null elements will be normally retained in the concatenation result.

SELECT array_concat(int_array1, int_array2) FROM array_concat_test WHERE id = 5;
+--------------------------------------+
| array_concat(int_array1, int_array2) |
+--------------------------------------+
| [1, null, 3, null, 5, 6]             |
+--------------------------------------+

Type compatibility example: concatenating int_array1 and string_array1, string elements cannot be converted to int, resulting in null.

SELECT array_concat(int_array1, string_array1) FROM array_concat_test WHERE id = 1;
+-----------------------------------------+
| array_concat(int_array1, string_array1) |
+-----------------------------------------+
| [1, 2, 3, null, null]                   |
+-----------------------------------------+

Complex type examples:

Nested array concatenation, can be concatenated when structures are consistent.

SELECT array_concat([[1,2],[3,4]], [[5,6],[7,8]]);
+--------------------------------------------+
| array_concat([[1,2],[3,4]], [[5,6],[7,8]]) |
+--------------------------------------------+
| [[1, 2], [3, 4], [5, 6], [7, 8]]           |
+--------------------------------------------+

When nested array structures are inconsistent, an error is thrown.

SELECT array_concat([[1,2]], [{'k':1}]);
ERROR 1105 (HY000): errCode = 2, detailMessage = can not cast from origin type ARRAY<ARRAY<INT>> to target type=ARRAY<DOUBLE>

Map type concatenation, can be concatenated when structures are consistent.

SELECT array_concat([{'k':1}], [{'k':2}]);
+------------------------------------+
| array_concat([{'k':1}], [{'k':2}]) |
+------------------------------------+
| [{"k":1}, {"k":2}]                 |
+------------------------------------+

Struct type concatenation, can be concatenated when structures are consistent.

SELECT array_concat(array(named_struct('name','Alice','age',20)), array(named_struct('name','Bob','age',30)));
+--------------------------------------------------------------------------------------------------------+
| array_concat(array(named_struct('name','Alice','age',20)), array(named_struct('name','Bob','age',30))) |
+--------------------------------------------------------------------------------------------------------+
| [{"name":"Alice", "age":20}, {"name":"Bob", "age":30}]                                                 |
+--------------------------------------------------------------------------------------------------------+

When struct structures are inconsistent, an error is thrown.

SELECT array_concat(array(named_struct('name','Alice','age',20)), array(named_struct('id',1,'score',95.5,'age',10)));
ERROR 1105 (HY000): errCode = 2, detailMessage = can not cast from origin type ARRAY<STRUCT<name:VARCHAR(5),age:TINYINT>> to target type=ARRAY<DOUBLE>

Incorrect number of parameters will cause an error.

SELECT array_concat();
ERROR 1105 (HY000): errCode = 2, detailMessage = Can not found function 'array_concat' which has 0 arity. Candidate functions are: [array_concat(Expression, Expression, ...)]

Passing non-array type will cause an error.

SELECT array_concat('not_an_array');
ERROR 1105 (HY000): errCode = 2, detailMessage = Can not find the compatibility function signature: array_concat(VARCHAR(12))

Notes

Ensure that all input array element types are compatible, especially for nested complex types where the structure should be consistent to avoid type conversion errors at runtime.

Keywords

ARRAY, CONCAT, ARRAY_CONCAT