MAPPUT

Accepts a VMap and one or more key/value pairs and returns a new VMap with the key/value pairs added.

Accepts a VMap and one or more key/value pairs and returns a new VMap with the key/value pairs added. Keys must be set using the auxiliary function SetMapKeys(), and can only be constant strings. If the VMap has any of the new input keys, then the original values are replaced by the new ones.

Syntax

MAPPUT (VMap-data, value[,...] USING PARAMETERS keys=SetMapKeys('key'[,...])

Arguments

VMap-data
Any VMap data. The VMap can exist as:

  • The __raw__ column of a flex table

  • Data returned from a map function such as MAPLOOKUP.

  • Other database content

value[,...]
One or more values to add to the VMap specified in VMap-data.

Parameters

keys
The result of SetMapKeys(). SetMapKeys() takes one or more constant string arguments.

The following example shows how to create a flex table and use COPY to enter some basic JSON data. After creating a second flex table, insert the new VMap results from mapput(), with additional key/value pairs.

  1. Create sample table:

    => CREATE FLEX TABLE vmapdata1();
CREATE TABLE

  2. Load sample JSON data from STDIN:

    => COPY vmapdata1 FROM stdin parser fjsonparser();
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> {"aaa": 1, "bbb": 2, "ccc": 3}
>> \.

  3. Create another flex table and use the function to insert data into it: => CREATE FLEX TABLE vmapdata2(); => INSERT INTO vmapdata2 SELECT MAPPUT(__raw__, '7','8','9' using parameters keys=SetMapKeys('xxx','yyy','zzz')) from vmapdata1;

  4. View the difference between the original and the new flex tables:

    => SELECT MAPTOSTRING(__raw__) FROM vmapdata1;
                     maptostring
-----------------------------------------------------
 {
   "aaa" : "1",
   "bbb" : "2",
   "ccc" : "3"
 }
 (1 row)

=> SELECT MAPTOSTRING(__raw__) from vmapdata2;
                     maptostring
-------------------------------------------------------
 {
   "mapput" : {
      "aaa" : "1",
      "bbb" : "2",
      "ccc" : "3",
      "xxx" : "7",
      "yyy" : "8",
      "zzz" : "9"
   }
 }

See also