Maps in SQL

Concepts covered: Maps

A map is a collection of key-value pairs where each key maps to exactly one value. Maps are also called dictionaries or associative arrays in other contexts. In SQL, maps provide a way to store flexible, named attributes without defining a fixed schema.

Maps are particularly valuable for storing metadata, configuration settings, or properties that vary between rows. Instead of adding nullable columns for every possible attribute, you store them all in a single map column.

Map Basics

Maps can be created using the MAP constructor with alternating keys and values:

SELECT MAP(
  ARRAY['color', 'size', 'price'],
  ARRAY['red', 'large', '29.99']
) AS product_attrs
FROM system_info
LIMIT 1

The MAP function takes two arrays: one of keys and one of values. Keys must be unique within a map. Both keys and values have specific types, and all keys must share one type while all values share another.

Accessing Map Values

Retrieve values from a map using bracket notation with the key:

SELECT
  session_id,
  properties['browser'] AS browser,
  properties['os'] AS operating_system
FROM sessions

The bracket notation properties['browser'] retrieves the value associated with the key 'browser'. If the key does not exist in the map, the result is NULL.

Comparison & Use Cases

Maps and JSON objects look similar but serve different purposes:

Map Use Cases

Maps excel when you need to store and retrieve named properties efficiently without the parsing overhead of JSON.

Common Map Use Cases

• Session properties and user attributes
• Product specifications with varying fields
• Configuration settings per tenant
• Metric labels in observability data
• Feature flags and experiment assignments

About this lesson

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