Map Functions and Operators

Subscript Operator: []

Description: The [] operator is used to retrieve a value corresponding to a given key from a mapping.

select age_map['li'] from (values (map(array['li','wang'],array[15,27]))) as table_age(age_map);-- 15

Map Functions

• cardinality(x)

  Description: Returns the cardinality of map x.

  select cardinality(map(array['num1','num2'],array[11,12]));-- 2

• element_at(map(K, V), key)

  Description: Returns the value of key in a map. If the map does not contain the key, null is returned.

  select element_at(map(array['num1','num2'],array[11,12]),'num1'); --11
  select element_at(map(array['num1','num2'],array[11,12]),'num3');-- NULL

• map()

  Description: Returns an empty map.

  select map();-- {}

• map(array(K), array(V)) -> map(K, V)

  Description: Returns a map based on the given key-value pair array. The map_agg() and multimap_agg() functions in the aggregate function can also be used to generate maps.

  SELECT map(ARRAY[1,3],ARRAY[2,4]);-- {1 -> 2, 3 -> 4}

• map_from_entries(array(row(K, V))) -> map(K, V)

  Description: Generates a map using a given array.

  SELECT map_from_entries(ARRAY[(1, 'x'), (2, 'y')]); -- {1 -> 'x', 2 -> 'y'}

• multimap_from_entries(array(row(K, V))) -> map(K, array(V))

  Description: Returns a composite map based on a given row array. Each key can correspond to multiple values.

  SELECT multimap_from_entries(ARRAY[(1, 'x'), (2, 'y'), (1, 'z')]); -- {1 -> ['x', 'z'], 2 -> ['y']}

• map_entries(map(K, V)) -> array(row(K, V))

  Description: Generates a row array using the given map.

  SELECT map_entries(MAP(ARRAY[1, 2], ARRAY['x', 'y'])); -- [ROW(1, 'x'), ROW(2, 'y')]

• map_concat(map1(K, V), map2(K, V), ..., mapN(K, V))

  Description: Constructs a new map using only the entry that maps the given function to true.

  SELECT map_filter(MAP(ARRAY[], ARRAY[]), (k, v) -> true); -- {}
  SELECT map_filter(MAP(ARRAY[10, 20, 30], ARRAY['a', NULL, 'c']), (k, v) -> v IS NOT NULL); -- {10 -> a, 30 -> c}
  SELECT map_filter(MAP(ARRAY['k1', 'k2', 'k3'], ARRAY[20, 3, 15]), (k, v) -> v > 10); -- {k1 -> 20, k3 -> 15}

• map_keys(x(K, V)) -> array(K)

  Description: Returns all arrays constructed by keys in a map.

  select map_keys(map(array['num1','num2'],array[11,12])); -- [num1, num2]

• map_values(x(K, V)) -> array(V)

  Description: Returns all value-constructed arrays in a map.

  select map_values(map(array['num1','num2'],array[11,12]));-- [11, 12]

• map_zip_with(map(K, V1), map(K, V2), function(K, V1, V2, V3))

  Description: Merges two given maps into a map by applying the function to a pair of values with the same key. For keys that appear only in one map, null is passed as the value for the missing key.

  SELECT map_zip_with(MAP(ARRAY[1, 2, 3], ARRAY['a', 'b', 'c']), -- {1 -> ad, 2 -> be, 3 -> cf} 
                       MAP(ARRAY[1, 2, 3], ARRAY['d', 'e', 'f']), 
                       (k, v1, v2) -> concat(v1, v2)); 
         _col0        
  --------------------
   {1=ad, 2=be, 3=cf} 
  (1 row)
    
   SELECT map_zip_with(MAP(ARRAY['k1', 'k2'], ARRAY[1, 2]), -- {k1 -> ROW(1, null), k2 -> ROW(2, 4), k3 -> ROW(null, 9)} 
                       MAP(ARRAY['k2', 'k3'], ARRAY[4, 9]), 
                       (k, v1, v2) -> (v1, v2)); 
                                         _col0                                       
  -------------------------------------------------------------------------------
  {k1={field0=1, field1=null}, k2={field0=2, field1=4}, k3={field0=null, field1=9}} 
  (1 row)
    
   SELECT map_zip_with(MAP(ARRAY['a', 'b', 'c'], ARRAY[1, 8, 27]), -- {a -> a1, b -> b4, c -> c9} 
                       MAP(ARRAY['a', 'b', 'c'], ARRAY[1, 2, 3]), 
                       (k, v1, v2) -> k || CAST(v1/v2 AS VARCHAR));
         _col0        
  --------------------
   {a=a1, b=b4, c=c9} 
  (1 row)

• transform_keys(map(K1, V), function(K1, V, K2)) -> map(K2, V)

  Description: For each entry in the map, map the key value K1 to the new key value K2 and keep the corresponding value unchanged.

  SELECT transform_keys(MAP(ARRAY[], ARRAY[]), (k, v) -> k + 1); -- {}
  
  SELECT transform_keys(MAP(ARRAY [1, 2, 3], ARRAY ['a', 'b', 'c']), (k, v) -> k + 1); -- {2 -> a, 3 -> b, 4 -> c}
  
  SELECT transform_keys(MAP(ARRAY ['a', 'b', 'c'], ARRAY [1, 2, 3]), (k, v) -> v * v); -- {1 -> 1, 4 -> 2, 9 -> 3}
  
  SELECT transform_keys(MAP(ARRAY ['a', 'b'], ARRAY [1, 2]), (k, v) -> k || CAST(v as VARCHAR)); -- {a1 -> 1, b2 -> 2}
  
  SELECT transform_keys(MAP(ARRAY [1, 2], ARRAY [1.0, 1.4]), -- {one -> 1.0, two -> 1.4}
                        (k, v) -> MAP(ARRAY[1, 2], ARRAY['one', 'two'])[k]);

• size(x) → bigint

  Description: Returns the capacity of Map(x) x.

  select size(map(array['num1','num2'],array[11,12]));; --2

• transform_values(map(K, V1), function(K, V2, V2)) -> map(K, V2)

  Description: Maps value V1 to value V2 for each entry in the map and keeps the corresponding key unchanged.

  SELECT transform_values(MAP(ARRAY[], ARRAY[]), (k, v) -> v + 1); -- {}
  
  SELECT transform_values(MAP(ARRAY [1, 2, 3], ARRAY [10, 20, 30]), (k, v) -> v + k); -- {1 -> 11, 2 -> 22, 3 -> 33}
  
  SELECT transform_values(MAP(ARRAY [1, 2, 3], ARRAY ['a', 'b', 'c']), (k, v) -> k * k); -- {1 -> 1, 2 -> 4, 3 -> 9}
  
  SELECT transform_values(MAP(ARRAY ['a', 'b'], ARRAY [1, 2]), (k, v) -> k || CAST(v as VARCHAR)); -- {a -> a1, b -> b2}
  
  SELECT transform_values(MAP(ARRAY [1, 2], ARRAY [1.0, 1.4]), -- {1 -> one_1.0, 2 -> two_1.4}
                          (k, v) -> MAP(ARRAY[1, 2], ARRAY['one', 'two'])[k] || '_' || CAST(v AS VARCHAR));