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-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.
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
}

  1. 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