MULTIMAP_AGG aggregates key-value pairs into a map where each key maps to an array of all associated values. Keys are provided by the first argument and values by the second. NULL keys are ignored.
Usage notes
MaxCompute V2.0 data types: If your SQL statement uses TINYINT, SMALLINT, INT, FLOAT, VARCHAR, TIMESTAMP, or BINARY, enable the MaxCompute V2.0 data type edition first:
Session level: Prepend the following statement to your SQL and run them together:
set odps.sql.type.system.odps2=true;Project level: Run the following statement as the project owner:
setproject odps.sql.type.system.odps2=true;The change takes effect within 10 to 15 minutes. For details, see Project operations and Data type editions.
Memory usage: SQL statements that include multiple aggregate functions may cause memory overflow if project resources are insufficient. Optimize the SQL statement or purchase additional computing resources as needed.
Syntax
map<K, array<V>> multimap_agg(K a, V b)Parameters
| Parameter | Description |
|---|---|
a | The key of the map. NULL keys are ignored. |
b | The value associated with the key. All values that share the same key are collected into an array. |
Return value
Returns a map<K, array<V>> where each key maps to an array of all its associated values.
Example
select multimap_agg(a, b) from
values (1L, 'apple'), (2L, 'hi'), (null, 'good'), (1L, 'pie') t(a, b);Sample result:
+----------------------------------+
| _c0 |
+----------------------------------+
| {"2":["hi"],"1":["apple","pie"]} |
+----------------------------------+Related functions
MULTIMAP_AGG is an aggregate function. For other aggregate functions, see Aggregate functions.