COLLECT_LIST

COLLECT_LIST

Description

Syntax:

ARRAY<T> collect_list(expr)

This function returns an array consisting of all elements (excluding NULL values) in expr. The order of elements in the array is non-deterministic.

Note:

It is only supported in vectorized engine.

Example

mysql> set enable_vectorized_engine=true;

mysql> select k1,k2,k3 from collect_test order by k1;
+------+------------+-------+
| k1   | k2         | k3    |
+------+------------+-------+
|    1 | 2022-07-05 | hello |
|    2 | 2022-07-04 | NULL  |
|    2 | 2022-07-04 | hello |
|    3 | NULL       | world |
|    3 | NULL       | world |
+------+------------+-------+

mysql> select k1,collect_list(k2),collect_list(k3) from collect_test group by k1 order by k1;
+------+--------------------------+--------------------+
| k1   | collect_list(`k2`)       | collect_list(`k3`) |
+------+--------------------------+--------------------+
|    1 | [2022-07-05]             | [hello]            |
|    2 | [2022-07-04, 2022-07-04] | [hello]            |
|    3 | NULL                     | [world, world]     |
+------+--------------------------+--------------------+

Keywords

COLLECT_LIST,COLLECT_SET,ARRAY