MAP: Dynamic Key-Value Pairs

A map is 1 column holding key-value pairs where each row chooses its own keys. All keys share a type and all values share a type, and the schema says nothing about which keys will appear. That makes it the shape for attributes that vary from record to record: a product catalog where a laptop has ram_gb and screen_in, a shirt has size and color, and a mug has capacity_ml. A struct would need a field for every attribute any product might have, hundreds of fields, nearly all NULL on every row.

CREATE TABLE products (
  product_id BIGINT,
  name VARCHAR,
  category VARCHAR,
  attributes MAP(VARCHAR, VARCHAR)
)

product_id	name	category	attributes
7	Ultrabook 14	laptop	{ram_gb: 16, screen_in: 14}
8	Linen shirt	apparel	{size: M, color: navy}
9	Stoneware mug	kitchen	{capacity_ml: 350, color: cream}

Looking up a key uses element_at, which returns NULL when the key is absent, the same way it does for arrays. The subscript form attributes['color'] raises an error on product 7, where no such key exists, and takes the query down with it.

SELECT
  product_id,
  element_at(attributes, 'color') AS color,
  cardinality(attributes) AS attribute_count
FROM products

product_id	color	attribute_count
7	NULL	2
8	navy	2
9	cream	2

Filtering on a key is the same call in a WHERE clause: WHERE element_at(attributes, 'size') = 'M' returns the shirt. Unnesting a map produces 1 row per pair, with the key and the value as 2 columns, which turns the question of which keys exist at all into an aggregate:

SELECT
  kv.attr_key,
  count(*) AS products_with_key
FROM products p
CROSS JOIN UNNEST(p.attributes) AS kv(attr_key, attr_value)
GROUP BY kv.attr_key
ORDER BY products_with_key DESC

That query is the one to run every month on a map column. Keys that appear on most rows and show up in filters are candidates to move out of the map and into a column of their own, a point the next section makes concrete.

What a map gives up

The flexibility costs 3 guarantees. First, type. Every value in MAP(VARCHAR, VARCHAR) is text, so ram_gb is stored as '16', and element_at(attributes, 'ram_gb') > '8' compares 2 strings, where '16' sorts before '8' because '1' comes before '8'. The laptop fails the filter. Correct code casts every time: CAST(element_at(attributes, 'ram_gb') AS INTEGER) > 8. Second, the key set. Nothing stops 1 upstream team writing color and another writing colour, and both land in the table. Third, visibility. A reader of the schema learns that attributes exist and nothing about what they are, so documentation has to carry what the schema would otherwise say.

Shape	Keys or fields	Values	Reach for it when
STRUCT	Fixed, named in the schema	Each field has its own type	Every row has the same fields
ARRAY	Positions 1, 2, 3, ...	All one type, order matters	A row holds a list of things
MAP	Vary per row, chosen at write time	All one type, no order	Attributes differ from row to row

A map whose values are numbers, dates and words all stored as text is a sign that the common fields want a struct, with the map kept for the rest.

The products table above is queried with WHERE attributes['ram_gb'] > '8'. What happens?

The laptop is returned, since 16 is greater than 8 and the comparison is numeric
The query errors on the first product without a ram_gb key, and even on the laptop '16' > '8' is false
The query errors because ram_gb is a map key and the engine only accepts column names
The laptop is returned and the shirt and mug come back with a NULL comparison result

About this lesson

This section is part of the Nested Data 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.