array_unique_agg
Description
Aggregates distinct values (including NULL
) in an ARRAY column into an array (from multiple rows to one row).
This function is supported from v3.2.
Syntax
ARRAY_UNIQUE_AGG(col)
Parameters
col
: the column whose values you want to aggregate. Supported data type is ARRAY.
Return value
Returns a value of the ARRAY type.
Usage notes
- The order of the elements in an array is random.
- The data type of the elements in the returned array is the same as the data type of the elements in the input column.
- Returns
NULL
if there is no matched value.
Examples
Take the following data table as an example:
mysql > select * from array_unique_agg_example;
+------+--------------+
| a | b |
+------+--------------+
| 2 | [1,null,2,4] |
| 2 | [1,null,3] |
| 1 | [1,1,2,3] |
| 1 | [2,3,4] |
+------+--------------+
Example 1: Group the values in column a
and aggregate distinct values in column b
into an array.
mysql > select a, array_unique_agg(b) from array_unique_agg_example group by a;
+------+---------------------+
| a | array_unique_agg(b) |
+------+---------------------+
| 1 | [4,1,2,3] |
| 2 | [4,1,2,3,null] |
+------+---------------------+
Example 2: Aggregate values in column b
using the WHERE clause. If no data meets the filter condition, a NULL
value is returned.
mysql > select array_unique_agg(b) from array_unique_agg_example where a < 0;
+---------------------+
| array_unique_agg(b) |
+---------------------+
| NULL |
+---------------------+
Keywords
ARRAY_UNIQUE_AGG, ARRAY