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:

  • The __raw__ column of a flex table
  • Data returned from a map function such as maplookup()
  • Other database content
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