SQL map functions

Use map functions to create and operate on Arrow maps in SQL queries.

• element_at
• make_map
• map
• map_extract
• map_keys
• map_values

element_at

Alias of map_extract.

make_map

Returns an Arrow map with the specified key and value.

make_map(key, value)

Arguments

• key: Expression to use for the key. Can be a constant, column, function, or any combination of arithmetic or string operators.
• value: Expression to use for the value. Can be a constant, column, function, or any combination of arithmetic or string operators.

SELECT
  make_map(room, temp) AS make_map
FROM
  home
LIMIT 4

map

Returns an Arrow map with the specified key-value pairs. Keys are mapped to values by their positions in each respective list. Each key must be unique and non-null.

map(key_list, value_list)
-- or
map { key: value, ... }

Arguments

• key_list: List of keys to use in the map. Each key must be unique and non-null.
• value_list: List of values to map to the corresponding keys.

SELECT
  map(
    [400, 401, 402, 403, 404],
    ['Bad Request', 'Unauthorized', 'Payment Required', 'Forbidden', 'Not Found']
  ) AS map

SELECT
  map {
    400: 'Bad Request',
    401: 'Unauthorized',
    402: 'Payment Required',
    403: 'Forbidden',
    404: 'Not Found'
  } AS map

map_extract

Returns a list containing the value for the given key or an empty list if the Returns a list containing the value for the given key, or an empty list if the key is not present in the map. The returned list will contain exactly one element (the value) when the key is found.

map_extract(map, key)

Arguments

• map: Map expression. Can be a constant, column, or function, and any combination of map operators.
• key: Key to extract from the map. Can be a constant, column, or function, any combination of arithmetic or string operators, or a named expression of the previously listed.

Aliases

• element_at

Related functions

get_field

WITH constants AS (
  SELECT map(
    [0, 1, 2, 3, 4, 5, 6],
    ['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday']
  ) AS days_of_week
)
SELECT 
  weather.time,
  map_extract(c.days_of_week, date_part('dow', time))[1] AS day_of_week
FROM
  weather,
  constants AS c
ORDER BY
  weather.time
LIMIT 6

map_keys

Returns a list of all keys in the map.

map_keys(map)

Arguments

• map: Map expression. Can be a constant, column, or function, and any combination of map operators.

Related functions

get_field

SELECT map_keys(map {'a': 1, 'b': NULL, 'c': 3}) AS map_keys

map_values

Returns a list of all values in the map.

map_values(map)

Arguments

• map: Map expression. Can be a constant, column, or function, and any combination of map operators.

SELECT map_values(map {'a': 1, 'b': NULL, 'c': 3}) AS map_values