MAPITEMS

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_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
passthrough_arg [Optional] 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) FRPM 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)

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
}
  1. Create a flex table, mapper:  
    => CREATE FLEX TABLE mapper();
CREATE TABLE
  2. Load simple.json into the flex table mapper: 
    3. => COPY mapper FROM '/home/dbadmin/data/simple.json' parser fjsonparser (flatten_arrays=false, 
flatten_maps=false);
 Rows Loaded
-------------
           1
(1 row)
  3. 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:
    4. => SELECT MAPKEYS(__raw__) OVER() FROM mapper;
    keys
-------------
 five_Map
 four
 one
 six
 three_Array
 two
(6 rows)
  4. Call mapitems on flex table mapper with three_Array as a pass-through argument to the function. The call returns these array values: 
    5. => 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