MAPAGGREGATE

Returns a LONG VARBINARY VMap with keys and value pairs supplied from two VARCHAR input columns of an existing columnar table. Using this function requires specifying an over() clause for the source table.

Syntax

MAPAGGREGATE(source_column1, source_column2)

Arguments

source_column1

Table column with values to use as the keys of the key/value pair of the returned VMap data. 

source_column2
 Table column with values to use as the values in the key/value pair of the returned VMap data.

Examples

This example creates a columnar table btest, with two VARCHAR columns, named keys and values, and adds three sets of values: 

=> CREATE TABLE btest(keys varchar(10), values varchar(10));
CREATE TABLE
=>  COPY btest FROM stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> one|1
>> two|2
>> three|3
>> \.

After populating the btest table, call mapaggregate(), using the the over (PARTITION BEST) clause. This call returns the raw_map data:

=> SELECT MAPAGGREGATE(keys, values) OVER(PARTITION BEST) FROM btest;
                 raw_map                     
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------
 \001\000\000\000\023\000\000\000\003\000\000\000\020\000\000\000\021\000\000\000\022\000\000\000132
\003\000\000\000\020\000\000\000\023\000\000\000\030\000\000\000onethreetwo
(1 row)

The next example illustrates using MAPTOSTRING() with the returned raw_map from mapaggregate() to see the values:

=> SELECT MAPTOSTRING(raw_map) FROM (SELECT MAPAGGREGATE(keys, values) OVER(PARTITION BEST) FROM btest) bit;
                 maptostring
----------------------------------------------
 {
        "one":  "1",
        "three":        "3",
        "two":  "2"
 }
(1 row)

See Also