MAPTOSTRING

Recursively builds a string representation VMap data, including nested JSON maps.

Recursively builds a string representation VMap data, including nested JSON maps. Use this transform function to display the VMap contents in a readable LONG VARCHAR format. Use maptostring to see how map data is nested before querying virtual columns with mapvalues().

Syntax

MAPTOSTRING ( VMap-data [ USING PARAMETERS canonical_json={true | false} ] )

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

Parameters

canonical_json
=bool
[Optional parameter]

Produces canonical JSON output by default, using the first instance of any duplicate keys in the map data.

Use this parameter as other UDF parameters, preceded by using parameters, as shown in the examples. Setting this argument to false maintains the previous behavior of maptostring() and returns same-name keys and their values.

Default: canonical-json=true

Examples

The following example shows how to create a sample flex table, darkdataand load JSON data from STDIN. By calling maptostring() twice with both values for the canonical_json parameter, you can see the different results on the flex table __raw__ column data.

  1. Create sample table:

    => CREATE FLEX TABLE darkdata();
CREATE TABLE

  2. Load sample JSON data from STDIN:

    => COPY darkdata 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, "aaa": 2, "AAA": 3, "bbb": "aaa\"bbb"}
>> \.

  3. Call maptostring() with its default behavior using canonical JSON output, and then review the flex table contents. The function returns the first duplicate key and its value ("aaa": "1") but omits remaining duplicate keys ("aaa": "2"):

    => SELECT MAPTOSTRING (__raw__) FROM darkdata;
                        maptostring
------------------------------------------------------------
 {
   "AAA" : "3",
   "aaa" : "1",
   "bbb" : "aaa\"bbb"
}
(1 row)

  4. Next, call maptostring() with using parameters canonical_json=false). This time, the function returns the first duplicate keys and their values:

    => SELECT MAPTOSTRING(__raw__ using parameters canonical_json=false) FROM darkdata;
                          maptostring
---------------------------------------------------------------
 {
            "aaa":  "1",
            "aaa":  "2",
        "AAA":  "3",
        "bbb":  "aaa"bbb"
 }
(1 row)

See also