COLLECT_LIST

collect_list([distinct] expr)

Description

The collect_list function is used to collect and return an array from a set of input data. Users can optionally use the distinct keyword to return a set without duplicate elements.

Parameter Description

• expr: An expression of any type, used to collect elements from the input data.

Return Result

• Returns an array where the element type is the same as the input parameter type.
• If the distinct keyword is set, a deduplicated set is returned.
• The function does not guarantee the order of elements in the return result.
• If the input data contains null values, null will not be included in the calculation.

Usage Example

1. Return an array containing non-duplicate elements:

   SELECT collect_list(DISTINCT col) FROM VALUES (1), (2), (1), (null) AS tab(col); +----------------------------+ | collect_list(DISTINCT col) | +----------------------------+ | [2,1] | +----------------------------+

2. Return an array containing duplicate elements:

   SELECT collect_list(col) FROM VALUES (1), (2), (1), (null) AS tab(col); +-------------------+ | collect_list(col) | +-------------------+ | [1,2,1] | +-------------------+

3. Collecting Characters from String Data:

   SELECT collect_list(col) FROM VALUES ("apple"), ("banana"), ("cherry"), (null) AS tab(col); +-----------------------------+ | collect_list(col) | +-----------------------------+ | ["apple","banana","cherry"] | +-----------------------------+

4. Collect and return an array containing null values:

   SELECT collect_list(col) FROM VALUES (true), (false), (null) AS tab(col); +-------------------+ | collect_list(col) | +-------------------+ | [true,false] | +-------------------+