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