MAPKEYSINFO
Returns virtual column information from a given map. This transform function requires an OVER(PARTITION BEST)
clause.
Syntax
MAPKEYSINFO (VMap-data)
Arguments
VMap-data |
Any 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 |
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)