MAPTOSTRING

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

Recursively builds a string representation of VMap data, including nested JSON maps. Use this transform function to display the VMap contents in a LONG VARCHAR format. You can use MAPTOSTRING to see how map data is nested before querying virtual columns with MAPVALUES.

Syntax

MAPTOSTRING ( VMap-data [ USING PARAMETERS param=value ] )

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
Boolean, whether to produce canonical JSON format, using the first instance of any duplicate keys in the map data. If false, the function returns duplicate keys and their values.

Default: true

Examples

The following example uses this table definition and sample data:

=> CREATE FLEX TABLE darkdata();
CREATE TABLE

=> 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"}
>> \.

Calling MAPTOSTRING with the default value of canonical_json returns only the first instance of the duplicate key:

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

With canonical_json set to false, the function returns all of the keys, including duplicates:

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

See also