This is the multi-page printable view of this section.
Click here to print.
Return to the regular view of this page.
Map functions
The flex map functions let you extract and manipulate nested map data.
The flex map functions let you extract and manipulate nested map data.
The first argument of all flex map functions (except EMPTYMAP and MAPAGGREGATE) takes a VMap. The VMap can originate from the __raw__ column in a flex table or be returned from a map or extraction function.
All map functions (except EMPTYMAP and MAPAGGREGATE) accept either a LONG VARBINARY or a LONG VARCHAR map argument.
In the following example, the outer MAPLOOKUP function operates on the VMap data returned from the inner MAPLOOKUP function:
=> MAPLOOKUP(MAPLOOKUP(ret_map, 'batch'), 'scripts')
You can use flex map functions exclusively with:
1 - EMPTYMAP
Constructs a new VMap with one row but without keys or data.
Constructs a new VMap with one row but without keys or data. Use this transform function to populate a map without using a flex parser. Instead, you use either from SQL queries or from map data present elsewhere in the database.
Syntax
EMPTYMAP()
Examples
Create an Empty Map
=> SELECT EMPTYMAP();
emptymap
------------------------------------------------------------------
\001\000\000\000\004\000\000\000\000\000\000\000\000\000\000\000
(1 row)
Create an Empty Map from an Existing Flex Table
If you create an empty map from an existing flex table, the new map has the same number of rows as the table from which it was created.
This example shows the result if you create an empty map from the darkdata table, which has 12 rows of JSON data:
=> SELECT EMPTYMAP() FROM darkdata;
emptymap
------------------------------------------------------------------
\001\000\000\000\004\000\000\000\000\000\000\000\000\000\000\000
\001\000\000\000\004\000\000\000\000\000\000\000\000\000\000\000
\001\000\000\000\004\000\000\000\000\000\000\000\000\000\000\000
\001\000\000\000\004\000\000\000\000\000\000\000\000\000\000\000
\001\000\000\000\004\000\000\000\000\000\000\000\000\000\000\000
\001\000\000\000\004\000\000\000\000\000\000\000\000\000\000\000
\001\000\000\000\004\000\000\000\000\000\000\000\000\000\000\000
\001\000\000\000\004\000\000\000\000\000\000\000\000\000\000\000
\001\000\000\000\004\000\000\000\000\000\000\000\000\000\000\000
\001\000\000\000\004\000\000\000\000\000\000\000\000\000\000\000
\001\000\000\000\004\000\000\000\000\000\000\000\000\000\000\000
\001\000\000\000\004\000\000\000\000\000\000\000\000\000\000\000
(12 rows)
See also
2 - MAPAGGREGATE
Returns a LONG VARBINARY VMap with key and value pairs supplied from two VARCHAR input columns.
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
3 - MAPCONTAINSKEY
Determines whether a VMap contains a virtual column (key).
Determines whether a VMap contains a virtual column (key). This scalar function returns true (t), if the virtual column exists, or false (f) if it does not. Determining that a key exists before calling maplookup() lets you distinguish between NULL returns. The maplookup() function uses for both a non-existent key and an existing key with a NULL value.
Syntax
MAPCONTAINSKEY (VMap-data, 'virtual-column-name')
Arguments
VMap-dataAny VMap data. The VMap can exist as:
virtual-column-name- Name of the key to check.
Examples
This example shows how to use the mapcontainskey() functions with maplookup(). View the results returned from both functions. Check whether the empty fields that maplookup() returns indicate a NULL value for the row (t) or no value (f):
You can use mapcontainskey( ) to determine that a key exists before calling maplookup(). The maplookup() function uses both NULL returns and existing keys with NULL values to indicate a non-existent key.
=> SELECT MAPLOOKUP(__raw__, 'user.location'), MAPCONTAINSKEY(__raw__, 'user.location')
FROM darkdata ORDER BY 1;
maplookup | mapcontainskey
-----------+----------------
| t
| t
| t
| t
Chile | t
Narnia | t
Uptown.. | t
chicago | t
| f
| f
| f
| f
(12 rows)
See also
4 - MAPCONTAINSVALUE
Determines whether a VMap contains a specific value.
Determines whether a VMap contains a specific value. Use this scalar function to return true (t) if the value exists, or false (f) if it does not.
Syntax
MAPCONTAINSVALUE (VMap-data, 'virtual-column-value')
Arguments
VMap-dataAny VMap data. The VMap can exist as:
virtual-column-value- Value to confirm.
Examples
This example shows how to use mapcontainsvalue() to determine whether or not a virtual column contains a particular value. Create a flex table (ftest), and populate it with some virtual columns and values. Name both virtual columns one:
=> CREATE FLEX TABLE ftest();
CREATE TABLE
=> copy ftest 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.
>> {"one":1, "two":2}
>> {"one":"one","2":"2"}
>> \.
Call mapcontainsvalue() on the ftest map data. The query returns false (f) for the first virtual column, and true (t) for the second , which contains the value one:
=> SELECT MAPCONTAINSVALUE(__raw__, 'one') FROM ftest;
mapcontainsvalue
------------------
f
t
(2 rows)
See also
5 - MAPITEMS
Returns information about items in a VMap.
Returns information about items in a VMap. Use this transform function with one or more optional arguments to access polystructured values within the VMap data. This function requires an over() clause.
Syntax
MAPITEMS (VMap-data [, passthrough-arg[,...] ])
Arguments
VMap-dataAny VMap data. The VMap can exist as:
max_key_length- In a
__raw__ column, determines the maximum length of keys that the function can return. Keys that are longer than *max_key_length* cause the query to fail. Defaults to the smaller of VMap column length and 65K.
max_value_length- In a
__raw__ column, determines the maximum length of values the function can return. Values that are larger than *max_value_length* cause the query to fail. Defaults to the smaller of VMap column length and 65K.
passthrough-arg- One or more arguments indicating keys within the map data in
VMap-data.
Examples
The following examples illustrate using MAPITEMS()with the over(PARTITION BEST) clause.
This example determines the number of virtual columns in the map data using a flex table, labeled darkmountain. Query using the count() function to return the number of virtual columns in the map data:
=> SELECT COUNT(keys) FROM (SELECT MAPITEMS(darkmountain.__raw__) OVER(PARTITION BEST) FROM
darkmountain) AS a;
count
-------
19
(1 row)
The next example determines what items exist in the map data:
=> SELECT * FROM (SELECT MAPITEMS(darkmountain.__raw__) OVER(PARTITION BEST) FROM darkmountain) AS a;
keys | values
-------------+---------------
hike_safety | 50.6
name | Mt Washington
type | mountain
height | 17000
hike_safety | 12.2
name | Denali
type | mountain
height | 29029
hike_safety | 34.1
name | Everest
type | mountain
height | 14000
hike_safety | 22.8
name | Kilimanjaro
type | mountain
height | 29029
hike_safety | 15.4
name | Mt St Helens
type | volcano
(19 rows)
The following example shows how to restrict the length of returned values to 100000:
=> SELECT LENGTH(keys), LENGTH(values) FROM (SELECT MAPITEMS(__raw__ USING PARAMETERS max_value_length=100000) OVER() FROM t1) x;
LENGTH | LENGTH
--------+--------
9 | 98899
(1 row)
Directly Query a Key Value in a VMap
Review the following JSON input file, simple.json. In particular, notice the array called three_Array, and its four values:
{
"one": "one",
"two": 2,
"three_Array":
[
"three_One",
"three_Two",
3,
"three_Four"
],
"four": 4,
"five_Map":
{
"five_One": 51,
"five_Two": "Fifty-two",
"five_Three": "fifty three",
"five_Four": 54,
"five_Five": "5 x 5"
},
"six": 6
}
-
Create a flex table mapper:
=> CREATE FLEX TABLE mapper();
CREATE TABLE
-
Load simple.json into the flex table mapper:
=> COPY mapper FROM '/home/dbadmin/data/simple.json' parser fjsonparser (flatten_arrays=false, flatten_maps=false);
Rows Loaded
-------------
1
(1 row)
-
Call MAPKEYS on the flex table's __raw__ column to see the flex table's keys, but not the key submaps. The return values indicate three_Array as one of the virtual columns:
=> SELECT MAPKEYS(__raw__) OVER() FROM mapper;
keys
-------------
five_Map
four
one
six
three_Array
two
(6 rows)
-
Call mapitems on flex table mapper with three_Array as a pass-through argument to the function. The call returns these array values:
=> SELECT __identity__, MAPITEMS(three_Array) OVER(PARTITION BY __identity__) FROM mapper;
__identity__ | keys | values
--------------+------+------------
1 | 0 | three_One
1 | 1 | three_Two
1 | 2 | 3
1 | 3 | three_Four
(4 rows)
See also
6 - MAPKEYS
Returns the virtual columns (and values) present in any VMap data.
Returns the virtual columns (and values) present in any VMap data. This transform function requires an OVER(PARTITION BEST) clause.
Syntax
MAPKEYS (VMap-data)
Arguments
VMap-dataAny VMap data. The VMap can exist as:
max_key_length- In a
__raw__ column, specifies the maximum length of keys that the function can return. Keys that are longer than *max_key_length* cause the query to fail. Defaults to the smaller of VMap column length and 65K.
Examples
Determine Number of Virtual Columns in Map Data
This example shows how to create a query, using an over(PARTITION BEST) clause with a flex table, darkdata to find the number of virtual column in the map data. The table is populated with JSON tweet data.
=> SELECT COUNT(keys) FROM (SELECT MAPKEYS(darkdata.__raw__) OVER(PARTITION BEST) FROM darkdata) AS a;
count
-------
550
(1 row)
Query Ordered List of All Virtual Columns in the Map
This example shows a snippet of the return data when you query an ordered list of all virtual columns in the map data:
=> SELECT * FROM (SELECT MAPKEYS(darkdata.__raw__) OVER(PARTITION BEST) FROM darkdata) AS a;
keys
-------------------------------------
contributors
coordinates
created_ at
delete.status.id
delete.status.id_str
delete.status.user_id
delete.status.user_id_str
entities.hashtags
entities.media
entities.urls
entities.user_mentions
favorited
geo
id
.
.
.
user.statuses_count
user.time_zone
user.url
user.utc_offset
user.verified
(125 rows)
Specify the Maximum Length of Keys that MAPKEYS Can Return
=> SELECT MAPKEYS(__raw__ USING PARAMETERS max_key_length=100000) OVER() FROM mapper;
keys
-------------
five_Map
four
one
six
three_Array
two
(6 rows)
See also
7 - MAPKEYSINFO
Returns virtual column information from a given map.
Returns virtual column information from a given map. This transform function requires an OVER(PARTITION BEST) clause.
Syntax
MAPKEYSINFO (VMap-data)
Arguments
VMap-dataAny VMap data. The VMap can exist as:
max_key_length- In a
__raw__ column, determines the maximum length of keys that the function can return. Keys that are longer than *max_key_length* cause the query to fail. Defaults to the smaller of VMap column length and 65K.
Returns
This function is a superset of the MAPKEYS() function. It returns the following information about each virtual column:
|
Column |
Description |
keys |
The virtual column names in the raw data. |
length |
The data length of the key name, which can differ from the actual string length. |
type_oid |
The OID type into which the value should be converted. Currently, the type is always 116 for a LONG VARCHAR, or 199 for a nested map that is stored as a LONG VARBINARY. |
row_num |
The number of rows in which the key was found. |
field_num |
The field number in which the key exists. |
Examples
This example shows a snippet of the return data you receive if you query an ordered list of all virtual columns in the map data:
=> SELECT * FROM (SELECT MAPKEYSINFO(darkdata.__raw__) OVER(PARTITION BEST) FROM darkdata) AS a;
keys | length | type_oid | row_num | field_num
----------------------------------------------------------+--------+----------+---------+-----------
contributors | 0 | 116 | 1 | 0
coordinates | 0 | 116 | 1 | 1
created_at | 30 | 116 | 1 | 2
entities.hashtags | 93 | 199 | 1 | 3
entities.media | 772 | 199 | 1 | 4
entities.urls | 16 | 199 | 1 | 5
entities.user_mentions | 16 | 199 | 1 | 6
favorited | 1 | 116 | 1 | 7
geo | 0 | 116 | 1 | 8
id | 18 | 116 | 1 | 9
id_str | 18 | 116 | 1 | 10
.
.
.
delete.status.id | 18 | 116 | 11 | 0
delete.status.id_str | 18 | 116 | 11 | 1
delete.status.user_id | 9 | 116 | 11 | 2
delete.status.user_id_str | 9 | 116 | 11 | 3
delete.status.id | 18 | 116 | 12 | 0
delete.status.id_str | 18 | 116 | 12 | 1
delete.status.user_id | 9 | 116 | 12 | 2
delete.status.user_id_str | 9 | 116 | 12 | 3
(550 rows)
Specify the Maximum Length of Keys that MAPKEYSINFO Can Return
=> SELECT MAPKEYSINFO(__raw__ USING PARAMETERS max_key_length=100000) OVER() FROM mapper;
keys
-------------
five_Map
four
one
six
three_Array
two
(6 rows)
See also
8 - MAPLOOKUP
Returns single-key values from VMAP data.
Returns single-key values from VMAP data. This scalar function returns a LONG VARCHAR, with values, or NULL if the virtual column does not have a value.
Using maplookup is case insensitive to virtual column names. To avoid loading same-name values, set the fjsonparser parser reject_on_duplicate parameter to true when data loading.
You can control the behavior for non-scalar values in a VMAP (like arrays), when loading data with the fjsonparser or favroparser parsers and its flatten-arrays argument. See JSON data and the FJSONPARSER reference.
For information about using maplookup() to access nested JSON data, see Querying nested data.
Syntax
MAPLOOKUP (VMap-data, 'virtual-column-name' [USING PARAMETERS [case_sensitive={false | true}] [, buffer_size=n] ] )
Parameters
VMap-dataAny VMap data. The VMap can exist as:
virtual-column-name- The name of the virtual column whose values this function returns.
buffer_size- [Optional parameter] Specifies the maximum length (in bytes) of each value returned for
virtual-column-name. To return all values for virtual-column-name, specify a buffer_size equal to or greater than (=>) the number of bytes for any returned value. Any returned values greater in length than buffer_size are rejected.
Default: 0 (No limit on buffer_size)
case_sensitive- [Optional parameter]
Specifies whether to return values for virtual-column-name if keys with different cases exist.
Example:
(... USING PARAMETERS case_sensitive=true)
Default: false
Examples
This example returns the values of one virtual column, user.location:
=> SELECT MAPLOOKUP(__raw__, 'user.location') FROM darkdata ORDER BY 1;
maplookup
-----------
Chile
Nesnia
Uptown
.
.
chicago
(12 rows)
Using maplookup buffer_size
Use the buffer_size= parameter to indicate the maximum length of any value that maplookup returns for the virtual column you specify. If none of the returned key values can be greater than n bytes, use this parameter to allocate n bytes as the buffer_size.
For the next example, save this JSON data to a file, simple_name.json:
{
"name": "sierra",
"age": "63",
"eyes": "brown",
"weapon": "doggie"
}
{
"name": "janis",
"age": "10",
"eyes": "blue",
"weapon": "humor"
}
{
"name": "ben",
"age": "43",
"eyes": "blue",
"weapon": "sword"
}
{
"name": "jen",
"age": "38",
"eyes": "green",
"weapon": "shopping"
}
-
Create a flex table, logs.
-
Load the simple_name.json data into logs, using the fjsonparser. Specify the flatten_arrays option as True:
=> COPY logs FROM '/home/dbadmin/data/simple_name.json'
PARSER fjsonparser(flatten_arrays=True);
-
Use maplookup with buffer_size=0 for the logs table name key. This query returns all of the values:
=> SELECT MAPLOOKUP(__raw__, 'name' USING PARAMETERS buffer_size=0) FROM logs;
MapLookup
-----------
sierra
ben
janis
jen
(4 rows)
-
Next, call maplookup() three times, specifying the buffer_size parameter as 3, 5, and 6, respectively. Now, maplookup() returns values with a byte length less than or equal to (<=) buffer_size:
=> SELECT MAPLOOKUP(__raw__, 'name' USING PARAMETERS buffer_size=3) FROM logs;
MapLookup
-----------
ben
jen
(4 rows)
=> SELECT MAPLOOKUP(__raw__, 'name' USING PARAMETERS buffer_size=5) FROM logs;
MapLookup
-----------
janis
jen
ben
(4 rows)
=> SELECT MAPLOOKUP(__raw__, 'name' USING PARAMETERS buffer_size=6) FROM logs;
MapLookup
-----------
sierra
janis
jen
ben
(4 rows)
Disambiguate Empty Output Rows
This example shows how to interpret empty rows. Using maplookup without first checking whether a key exists can be ambiguous. When you review the following output, 12 empty rows, you cannot determine whether a user.location key has:
-
A non-NULL value
-
A NULL value
-
No value
=> SELECT MAPLOOKUP(__raw__, 'user.location') FROM darkdata;
maplookup
-----------
(12 rows)
To disambiguate empty output rows, use the mapcontainskey() function in conjunction with maplookup(). When maplookup returns an empty field, the corresponding value from mapcontainskey indicates t for a NULL or other value, or ffor no value.
The following example output using both functions lists rows with NULL or a name value as t, and rows with no value as f:
=> SELECT MAPLOOKUP(__raw__, 'user.location'), MAPCONTAINSKEY(__raw__, 'user.location')
FROM darkdata ORDER BY 1;
maplookup | mapcontainskey
-----------+----------------
| t
| t
| t
| t
Chile | t
Nesnia | t
Uptown | t
chicago | t
| f >>>>>>>>>>No value
| f >>>>>>>>>>No value
| f >>>>>>>>>>No value
| f >>>>>>>>>>No value
(12 rows)
Check for Case-Sensitive Virtual Columns
You can use maplookup() with the case_sensitive parameter to return results when key names with different cases exist.
-
Save the following sample content as a JSON file. This example saves the file as repeated_key_name.json:
{
"test": "lower1"
}
{
"TEST": "upper1"
}
{
"TEst": "half1"
}
{
"test": "lower2",
"TEst": "half2"
}
{
"TEST": "upper2",
"TEst": "half3"
}
{
"test": "lower3",
"TEST": "upper3"
}
{
"TEst": "half4",
"test": "lower4",
"TEST": "upper4"
}
{
"TesttestTesttestTesttestTesttestTesttestTesttestTesttestTesttestTesttestTesttestTesttestTesttest
TesttestTesttestTesttestTesttest":"1",
"TesttestTesttestTesttestTesttestTesttestTesttestTesttestTesttestTesttestTesttestTesttest
TesttestTesttestTesttestTesttestTest12345":"2"
}
-
Create a flex table, dupe, and load the JSON file:
=> CREATE FLEX TABLE dupe();
CREATE TABLE
dbt=> COPY dupe FROM '/home/release/KData/repeated_key_name.json' parser fjsonparser();
Rows Loaded
-------------
8
(1 row)
See also
9 - 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:
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.
-
Create sample table:
=> CREATE FLEX TABLE vmapdata1();
CREATE TABLE
-
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}
>> \.
-
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;
-
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
10 - MAPSIZE
Returns the number of virtual columns present in any VMap data.
Returns the number of virtual columns present in any VMap data. Use this scalar function to determine the size of keys.
Syntax
MAPSIZE (VMap-data)
Arguments
VMap-dataAny VMap data. The VMap can exist as:
Examples
This example shows the returned sizes from the number of keys in the flex table darkmountain:
=> SELECT MAPSIZE(__raw__) FROM darkmountain;
mapsize
---------
3
4
4
4
4
(5 rows)
See also
11 - 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-dataAny VMap data. The VMap can exist as:
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.
-
Create sample table:
=> CREATE FLEX TABLE darkdata();
CREATE TABLE
-
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"}
>> \.
-
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)
-
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
12 - MAPVALUES
Returns a string representation of the top-level values from a VMap.
Returns a string representation of the top-level values from a VMap. This transform function requires an OVER() clause.
Syntax
MAPVALUES (VMap-data)
Arguments
VMap-dataAny VMap data. The VMap can exist as:
max_value_length- In a
__raw__ column, specifies the maximum length of values the function can return. Values that are larger than *max_value_length* cause the query to fail. Defaults to the smaller of VMap column length and 65K.
Examples
The following example shows how to query a darkmountain flex table, using an over() clause (in this case, the over(PARTITION BEST) clause) with mapvalues().
=> SELECT * FROM (SELECT MAPVALUES(darkmountain.__raw__) OVER(PARTITION BEST) FROM darkmountain) AS a;
values
---------------
29029
34.1
Everest
mountain
29029
15.4
Mt St Helens
volcano
17000
12.2
Denali
mountain
14000
22.8
Kilimanjaro
mountain
50.6
Mt Washington
mountain
(19 rows)
Specify the Maximum Length of Values that MAPVALUES Can Return
=> SELECT MAPVALUES(__raw__ USING PARAMETERS max_value_length=100000) OVER() FROM mapper;
keys
-------------
five_Map
four
one
six
three_Array
two
(6 rows)
See also
13 - MAPVERSION
Returns the version or invalidity of any map data.
Returns the version or invalidity of any map data. This scalar function returns the map version (such as 1) or -1, if the map data is invalid.
Syntax
MAPVERSION (VMap-data)
Arguments
VMap-dataAny VMap data. The VMap can exist as:
Examples
The following example shows how to use mapversion() with the darkmountainflex table, returning mapversion 1 for the flex table map data:
=> SELECT MAPVERSION(__raw__) FROM darkmountain;
mapversion
------------
1
1
1
1
1
(5 rows)
See also