COLLECT_SET

description

Syntax

ARRAY<T> collect_set(expr)

Returns an array consisting of all unique 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;

3. mysql> select k1,k2,k3 from collect_test order by k1;
4. +------+------------+-------+
5. | k1   | k2         | k3    |
6. +------+------------+-------+
7. |    1 | 2022-07-05 | hello |
8. |    2 | 2022-07-04 | NULL  |
9. |    2 | 2022-07-04 | hello |
10. |    3 | NULL       | world |
11. |    3 | NULL       | world |
12. +------+------------+-------+

14. mysql> select k1,collect_set(k2),collect_set(k3) from collect_test group by k1 order by k1;
15. +------+-------------------+-------------------+
16. | k1   | collect_set(`k2`) | collect_set(`k3`) |
17. +------+-------------------+-------------------+
18. |    1 | [2022-07-05]      | [hello]           |
19. |    2 | [2022-07-04]      | [hello]           |
20. |    3 | NULL              | [world]           |
21. +------+-------------------+-------------------+

keywords

COLLECT_SET,COLLECT_LIST,ARRAY