COLLECT_LIST

description

Syntax

ARRAY<T> collect_list(expr)

Returns an array consisting of all values in expr within the group, and ,with the optional max_size parameter limits the size of the resulting array to max_size elements.The order of elements in the array is non-deterministic. NULL values are excluded. It has an alias group_array.

notice

1. Only supported in vectorized engine

example

1. mysql> set enable_vectorized_engine=true;

3. mysql> select k1,k2,k3 from collect_list_test order by k1;
4. +------+------------+-------+
5. | k1   | k2         | k3    |
6. +------+------------+-------+
7. |    1 | 2023-01-01 | hello |
8. |    2 | 2023-01-02 | NULL  |
9. |    2 | 2023-01-02 | hello |
10. |    3 | NULL       | world |
11. |    3 | 2023-01-02 | hello |
12. |    4 | 2023-01-02 | sql   |
13. |    4 | 2023-01-03 | sql   |
14. +------+------------+-------+

16. mysql> select collect_list(k1),collect_list(k1,3) from collect_list_test;
17. +-------------------------+--------------------------+
18. | collect_list(`k1`)      | collect_list(`k1`,3)     |
19. +-------------------------+--------------------------+
20. | [1,2,2,3,3,4,4]         | [1,2,2]                  |
21. +-------------------------+--------------------------+

23. mysql> select k1,collect_list(k2),collect_list(k3,1) from collect_list_test group by k1 order by k1;
24. +------+-------------------------+--------------------------+
25. | k1   | collect_list(`k2`)      | collect_list(`k3`,1)     |
26. +------+-------------------------+--------------------------+
27. |    1 | [2023-01-01]            | [hello]                  |
28. |    2 | [2023-01-02,2023-01-02] | [hello]                  |
29. |    3 | [2023-01-02]            | [world]                  |
30. |    4 | [2023-01-02,2023-01-03] | [sql]                    |
31. +------+-------------------------+--------------------------+

keywords

COLLECT_LIST,GROUP_ARRAY,COLLECT_SET,ARRAY