MAPAGGREGATE

Returns a LONG VARBINARY VMap with key and value pairs supplied from two VARCHAR input columns. This function requires an OVER clause.

Syntax

MAPAGGREGATE (keys-column1, values-column2 [USING PARAMETERS param=value[,...]])

Arguments

keys-column

Table column with the keys for the key/value pairs of the returned VMap data. Keys with a NULL value are excluded. If there are duplicate keys, the duplicate key and value that appear first in the query result are used, while the other duplicates are omitted.
values-column Table column with the values for the key/value pairs of the returned VMap data.

Parameters

max_vmap_length

Maximum length in bytes for the VMap result, an integer between 1-32000000 inclusive.

Default: 130000
on_overflow

Overflow behavior for cases when the VMap result is larger than the max_vmap_length. The value must be one of the following strings:

  • 'ERROR': Returns an error when overflow occurs.

  • 'TRUNCATE': Stops aggregating key/value pairs if the result exceeds max_vmap_length. The query executes, but the resulting VMap does not have all key/value pairs. When the provided max_vmap_length is not large enough to store an empty VMap, the result returned is NULL. Note that you need to specify order criteria in the OVER clause to get consistent results.

  • 'RETURN_NULL': Return NULL if overflow occurs.

Default: 'ERROR'

Examples

The following examples use this input table:

=> SELECT * FROM inventory;
  product     | stock 
--------------+--------
  Planes      | 100  
  Trains      | 50  
  Automobiles | 200   
(3 rows)

Call MAPAGGREGATE as follows to return the raw_map data of the resulting VMap:

=> SELECT raw_map FROM (SELECT MAPAGGREGATE(product, stock) OVER(ORDER BY product) FROM inventory) inventory;
raw_map
------------------------------------------------------------------------------------------------------------
\001\000\000\000\030\000\000\000\003\000\000\000\020\000\000\000\023\000\000\000\026\000\000\00020010050\003
\000\000\000\020\000\000\000\033\000\000\000!\000\000\000AutomobilesPlanesTrains
(1 row)

To transform the returned raw_map data into string representation, use MAPAGGREGATE with MAPTOSTRING:

=> SELECT MAPTOSTRING(raw_map) FROM (SELECT MAPAGGREGATE(product, stock) OVER(ORDER BY product) FROM 
inventory) inventory;
MAPTOSTRING
--------------------------------------------------------------
{
  "Automobiles": "200",
  "Planes": "100",
  "Trains": "50"
}
(1 row)

If you run the above query with on_overflow left as default and a max_vmap_length less than the returned VMap size, the function returns with an error message indicating the need to increase VMap length: 

=> SELECT MAPTOSTRING(raw_map) FROM (SELECT MAPAGGREGATE(product, stock USING PARAMETERS max_vmap_length=60) 
OVER(ORDER BY product) FROM inventory) inventory;                     
----------------------------------------------------------------------------------------------------------
ERROR 5861:  Error calling processPartition() in User Function MapAggregate at [/data/jenkins/workspace
/RE-PrimaryBuilds/RE-Build-Master_2/server/udx/supported/flextable/Dict.cpp:1324], error code: 0, message: 
Exception while finalizing map aggregation: Output VMap length is too small [60]. HINT: Set the parameter 
max_vmap_length=71 and retry your query

Switching the value of on_overflow allows you to alter how MAPAGGREGATE behaves in the case of overflow. For example, changing on_overflow to 'RETURN_NULL' causes the above query to execute and return NULL:

SELECT raw_map IS NULL FROM (SELECT MAPAGGREGATE(product, stock USING PARAMETERS max_vmap_length=60, 
on_overflow='RETURN_NULL') OVER(ORDER BY product) FROM inventory) inventory;
?column?
----------
t
(1 row)

If on_overflow is set to 'TRUNCATE', the resulting VMap has enough space for two of the key/value pairs, but must cut the third:

SELECT raw_map IS NULL FROM (SELECT MAPAGGREGATE(product, stock USING PARAMETERS max_vmap_length=60, 
on_overflow='TRUNCATE') OVER(ORDER BY product) FROM inventory) inventory;
MAPTOSTRING
---------------------------------------------
{
  "Automobiles": "200",
  "Planes": "100"
}
(1 row)

See Also