Map Operations

A map stores key-value pairs in one cell. Each key appears once, and every value shares one type. Listing settings fit that shape: each host sets a check-in time, a pet policy and a guest limit. The examples build a settings map per listing from 3 columns of listing_settings, keeping every value as VARCHAR so times, words and numbers can live in the same map.

element_at(settings, 'pets') reads a value by key, the same function that read arrays by position. A key the map lacks returns NULL. The bracket form settings['pets'] fails the query on a missing key, so it belongs only where every row is known to carry that key.

Merging with map_concat

map_concat(a, b) combines 2 maps. A key found in only one map passes through; when both hold a key, the value from the later argument wins. That rule turns argument order into a policy. Put platform defaults first and host settings second, and hosts override the defaults. Reverse them, and the defaults overwrite what hosts chose, with no error to warn you.

WITH with_settings AS (
  SELECT
    listing_id,
    MAP(ARRAY['checkin', 'pets', 'max_guests'], ARRAY[checkin, pets, max_guests]) AS settings,
    MAP(ARRAY['pets', 'quiet_hours', 'checkout'], ARRAY['no', '22:00', '11:00']) AS defaults
  FROM listing_settings
)
SELECT
  listing_id,
  element_at(map_concat(defaults, settings), 'pets') AS host_wins,
  element_at(map_concat(settings, defaults), 'pets') AS default_wins,
  element_at(map_concat(defaults, settings), 'quiet_hours') AS quiet_hours,
  cardinality(map_concat(defaults, settings)) AS rule_count
FROM with_settings

Listing 102's host chose no, so both orders agree on that row, and only 101 and 103 reveal which order ran. Quiet hours come from the defaults in either order, since no host set them. Each merged map holds 5 keys: the 3 host settings plus quiet_hours and checkout, with pets counted once.

Filtering rows and entries

Two kinds of filter apply to maps. A row filter keeps rows whose map holds some entry: WHERE element_at(settings, 'pets') = 'yes'. A row without a pets key gets NULL from element_at, the comparison is NULL, and the row drops out. An entry filter keeps chosen keys and discards the rest, and one way that needs no new function is to rebuild the map from the keys you want, reading each value with element_at.

WITH with_settings AS (
  SELECT
    listing_id,
    MAP(ARRAY['checkin', 'pets', 'max_guests'], ARRAY[checkin, pets, max_guests]) AS settings
  FROM listing_settings
)
SELECT
  listing_id,
  MAP(ARRAY['checkin', 'pets'], ARRAY[element_at(settings, 'checkin'), element_at(settings, 'pets')]) AS guest_view
FROM with_settings
WHERE element_at(settings, 'pets') = 'yes'

The rebuilt map always carries both keys. If a listing had no checkin entry, its guest_view would still list the key, paired with NULL, which a consumer may read differently from an absent key. To test whether a key exists, search the key array: contains(map_keys(settings), 'checkin').

Transforming through keys and values

map_keys(settings) and map_values(settings) return the map's contents as 2 arrays in matching order, which opens up every array function from the first 2 sections. Entry order inside a map is not something to depend on, so wrap map_keys in array_sort before displaying or comparing key lists. A value pulled out by key can be cast to its real type, which lets guest limits sort as numbers.

WITH with_settings AS (
  SELECT
    listing_id,
    MAP(ARRAY['checkin', 'pets', 'max_guests'], ARRAY[checkin, pets, max_guests]) AS settings
  FROM listing_settings
)
SELECT
  listing_id,
  array_sort(map_keys(settings)) AS setting_names,
  cardinality(settings) AS setting_count,
  CAST(element_at(settings, 'max_guests') AS INTEGER) AS max_guests
FROM with_settings
ORDER BY max_guests DESC

Arrays

• Read by position: element_at(arr, 2)
• Search values: contains(arr, v)
• Repeats allowed
• Order carries meaning

Maps

• Read by key: element_at(m, 'pets')
• Search keys: contains(map_keys(m), k)
• Each key once
• Entry order not guaranteed

Listing 103's settings map is {checkin=14:00, pets=yes, max_guests=2}. What do pets and n come back as?

WITH with_settings AS (
  SELECT
    listing_id,
    MAP(ARRAY['checkin', 'pets', 'max_guests'], ARRAY[checkin, pets, max_guests]) AS settings
  FROM listing_settings
),
merged AS (
  SELECT
    listing_id,
    map_concat(settings, MAP(ARRAY['pets', 'checkout'], ARRAY['no', '11:00'])) AS rules
  FROM with_settings
)
SELECT
  element_at(rules, 'pets') AS pets,
  cardinality(rules) AS n
FROM merged
WHERE listing_id = 103

• yes and 4, because a host's own setting always takes priority
• no and 5, because each map contributes its own pets entry
• yes and 5, because maps keep the first value and count every entry
• no and 4, because the later map wins on pets and keeps the key once

About this lesson

This section is part of the Complex Data: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.