Categories:: Semi-structured and Structured Data Functions (Array/Object)

ARRAY_FLATTEN¶

Flattens an ARRAY of ARRAYs into a single ARRAY. The function effectively concatenates the ARRAYs that are elements of the input ARRAY and returns them as a single ARRAY.

Syntax¶

ARRAY_FLATTEN( <array> )

Copy

Arguments¶

array

The ARRAY of ARRAYs to flatten.

If any element of array is not an ARRAY, the function reports an error.

Returns¶

This function returns an ARRAY that is constructed by concatenating the ARRAYs in array.

If array is NULL or contains any elements that are NULL, the function returns NULL.

Usage Notes¶

If array contains multiple levels of nested ARRAYs, the function only removes one level of nesting.

For example, if the input ARRAY is:
```
[ [ [1, 2], [3] ], [ [4], [5] ] ]
```
The function returns:
```
[ [1, 2], [3], [4], [5] ]
```
Copy

Examples¶

The following example flattens an ARRAY of ARRAYs. Each element in the input ARRAY is an ARRAY of numbers. The example flattens the input ARRAY into an ARRAY containing the numbers as elements.

SELECT ARRAY_FLATTEN([[1, 2, 3], [4], [5, 6]]);

Copy

+-----------------------------------------+
| ARRAY_FLATTEN([[1, 2, 3], [4], [5, 6]]) |
|-----------------------------------------|
| [                                       |
|   1,                                    |
|   2,                                    |
|   3,                                    |
|   4,                                    |
|   5,                                    |
|   6                                     |
| ]                                       |
+-----------------------------------------+

The following example flattens an ARRAY that contains ARRAYs containing ARRAYs. The function removes the first level of nesting.

SELECT ARRAY_FLATTEN([[[1, 2], [3]], [[4], [5]]]);

Copy

+--------------------------------------------+
| ARRAY_FLATTEN([[[1, 2], [3]], [[4], [5]]]) |
|--------------------------------------------|
| [                                          |
|   [                                        |
|     1,                                     |
|     2                                      |
|   ],                                       |
|   [                                        |
|     3                                      |
|   ],                                       |
|   [                                        |
|     4                                      |
|   ],                                       |
|   [                                        |
|     5                                      |
|   ]                                        |
| ]                                          |
+--------------------------------------------+

The following example demonstrates that the function returns an error when an element of the input ARRAY is not an ARRAY.

SELECT ARRAY_FLATTEN([[1, 2, 3], 4, [5, 6]]);

Copy

100107 (22000): Not an array: 'Input argument to ARRAY_FLATTEN is not an array of arrays'

The following example demonstrates that the function returns NULL when an element of the input ARRAY is NULL.

SELECT ARRAY_FLATTEN([[1, 2, 3], NULL, [5, 6]]);

Copy

+------------------------------------------+
| ARRAY_FLATTEN([[1, 2, 3], NULL, [5, 6]]) |
|------------------------------------------|
| NULL                                     |
+------------------------------------------+

The following example demonstrates that the function flattens an ARRAY when an element of the input ARRAY is an ARRAY that contains a NULL element.

SELECT ARRAY_FLATTEN([[1, 2, 3], [NULL], [5, 6]]);

Copy

+--------------------------------------------+
| ARRAY_FLATTEN([[1, 2, 3], [NULL], [5, 6]]) |
|--------------------------------------------|
| [                                          |
|   1,                                       |
|   2,                                       |
|   3,                                       |
|   undefined,                               |
|   5,                                       |
|   6                                        |
| ]                                          |
+--------------------------------------------+