MAP type
MAP is a nested SQL data type storing an ordered collection of unique key-value pairs, useful for representing dynamic or sparse attributes within a single column.
Overview
A MAP column holds key-value pairs where all keys share one type and all values share one type — similar to a dictionary or hash map in a programming language. It's the right nested type when the set of "attribute names" per row isn't fixed or known ahead of time.
Copy code
CREATE TABLE tbl (id INTEGER, attrs MAP(VARCHAR, VARCHAR));
INSERT INTO tbl VALUES (1, MAP {'color': 'red', 'size': 'M'});
SELECT attrs['color'] AS color FROM tbl;
MAP vs STRUCT
Both MAP and STRUCT hold key-value-like data, but they serve different purposes:
- STRUCT has a fixed, known set of named fields, and different fields can have different value types (like a row/record type). Accessing a missing field is an error.
- MAP has a dynamic, per-row set of keys, but all values must share one type. Looking up a missing key returns
NULLinstead of erroring.
Use STRUCT for fixed schemas (e.g., an {street, city, zip} address), and MAP for open-ended or sparse key sets (e.g., arbitrary event properties or feature flags).
DuckDB specifics
DuckDB supports the MAP(key_type, value_type) type declaration, the MAP {key: value, ...} literal syntax, and the MAP(key_list, value_list) function form for building a map from two parallel lists:
Copy code
SELECT MAP(['a', 'b', 'c'], [1, 2, 3]) AS m;
DuckDB also provides map_from_entries([(key, value), ...]) to build a map from a list of key-value pairs, plus accessor functions map_keys(), map_values(), and map_entries(). Keys in a DuckDB MAP need not be strings — any comparable type, including nested types like lists, is allowed as a key.
Related terms
A struct in DuckDB is a composite data type that allows you to group multiple fields of different types into a single unit.
Key-value store →A key-value store is a simple database model that maps unique keys to values, optimized for very fast lookups, writes, and updates by key, without the fixed schema or query flexibility of a relational database.
Zone map (min-max index) →A zone map, or min-max index, is a lightweight index that stores the minimum and maximum value of a column for each block of data, letting queries skip blocks that can't match a filter.
LIST and ARRAY type →LIST and ARRAY are nested SQL data types for storing an ordered collection of values in a single column — LIST is variable-length, ARRAY is fixed-length.
Surrogate key →A surrogate key is a system-generated identifier — typically a sequential integer or UUID — assigned to a row that has no business meaning of its own, used as the primary key in dimension and fact tables.
UNNEST →UNNEST expands a list (array) or struct column into multiple rows or columns, turning nested data into a flat, relational shape.
FAQS
STRUCT has a fixed set of named fields that can each have a different type, and accessing an undefined field errors. MAP holds a dynamic, variable set of keys where every value must share the same type, and looking up a missing key returns NULL instead of an error.
Yes. DuckDB MAP keys can be any comparable type, not just VARCHAR — for example integers or even nested list values are valid map keys.
