COLLECT_LIST

description

Syntax

ARRAY<T> collect_list(expr)

Returns an array consisting of all values in expr within the group. The order of elements in the array is non-deterministic. NULL values are excluded.

notice

  1. Only supported in vectorized engine

example

  1. mysql> set enable_vectorized_engine=true;
  2. mysql> select k1,k2,k3 from collect_test order by k1;
  3. +------+------------+-------+
  4. | k1 | k2 | k3 |
  5. +------+------------+-------+
  6. | 1 | 2022-07-05 | hello |
  7. | 2 | 2022-07-04 | NULL |
  8. | 2 | 2022-07-04 | hello |
  9. | 3 | NULL | world |
  10. | 3 | NULL | world |
  11. +------+------------+-------+
  12. mysql> select k1,collect_list(k2),collect_list(k3) from collect_test group by k1 order by k1;
  13. +------+--------------------------+--------------------+
  14. | k1 | collect_list(`k2`) | collect_list(`k3`) |
  15. +------+--------------------------+--------------------+
  16. | 1 | [2022-07-05] | [hello] |
  17. | 2 | [2022-07-04, 2022-07-04] | [hello] |
  18. | 3 | NULL | [world, world] |
  19. +------+--------------------------+--------------------+

keywords

COLLECT_LIST,COLLECT_SET,ARRAY