COLLECT_LIST_ON_ARRAY

collect_list_on_array([distinct] array)

Description

This function is used to collect elements of the input array into a new array and return the new array. If the distinct parameter is specified, the elements in the resulting array are a deduplicated set.

Parameter Description

• expr: Input array type data.

Return Result

• Returns an array type data, with element types the same as the input array's element types.
• If the distinct parameter is specified, the elements in the resulting array are unique, with duplicates removed.
• The function does not guarantee the order of elements in the resulting array.
• null values in the input array do not affect the calculation of the resulting array.

Example

The following example demonstrates how to use the collect_list_on_array function to collect array elements and return a new array.

Example 1: Basic Usage

SELECT
  collect_list_on_array(a)
FROM values
(array(3, 3, 4)),
(null),
(array(2, 2, 3)),
(array(null)),
(array(1, null, 2)),
(array(1, 2, 2))
AS t(a);
+--------------------------+
| collect_list_on_array(a) |
+--------------------------+
| [3,3,4,2,2,3,1,2,1,2,2]  |
+--------------------------+