MAPKEYS
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_data |
Any 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)