ARRAY_AGG
Description
Concatenates the values (including null values) in a column into an array, which can be used for pivoting rows into columns.
Syntax
ARRAY_AGG(<col>)
Parameters
| Parameter | Description |
|---|---|
<col> | An expression that determines the values to be placed into the array (usually column names). |
Return Value
Returns a value of ARRAY type.Special cases:
- The order of elements in the array is not guaranteed.
- Returns the array generated by the conversion. The element type in the array is consistent with the type of col.
Example
-- setup
create table test_doris_array_agg(c1 int, c2 varchar(20)) distributed by hash(c1) buckets 1 properties ("replication_num"="1");
insert into test_doris_array_agg values (1,'a'),(1,'b'),(2,'c'),(2,null),(3,null);
select * from test_doris_array_agg;
+------+------+
| c1 | c2 |
+------+------+
| 1 | a |
| 1 | b |
| 2 | c |
| 2 | NULL |
| 3 | NULL |
+------+------+
select c1, array_agg(c2) from test_doris_array_agg group by c1;
+------+-----------------+
| c1 | array_agg(`c2`) |
+------+-----------------+
| 1 | ["a","b"] |
| 2 | [NULL,"c"] |
| 3 | [NULL] |
+------+-----------------+
