Shape / columns · All operations

Work with map values

A variant of Parse nested values

Build maps, look up a key, list keys or values, and merge maps.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Build a map, look up a key, list its keys and values and merge two maps

It reads the orders table the proofs share. Create it first:

SQL
CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL
CREATE TEMP TABLE result AS
SELECT order_id,
       map_from_entries([('customer', customer), ('region', region)]) AS m,
       m['region'] AS region_lookup,
       m['missing'] AS missing_lookup,
       map_keys(m) AS keys,
       map_values(m) AS vals,
       map_concat(m, MAP {'region': 'unknown', 'source': 'import'}) AS merged
FROM orders;
SELECT * FROM result ORDER BY order_id;
SELECT CASE WHEN ((SELECT [region_lookup, missing_lookup] FROM result WHERE order_id = 1) = ['north', NULL] AND (SELECT keys FROM result WHERE order_id = 1) = ['customer', 'region'] AND (SELECT vals FROM result WHERE order_id = 1) = ['Alice Ng', 'north'] AND (SELECT merged FROM result WHERE order_id = 1) = MAP {'customer': 'Alice Ng', 'region': 'unknown', 'source': 'import'} AND (SELECT region_lookup FROM result WHERE order_id = 9) IS NULL AND (SELECT merged['region'] FROM result WHERE order_id = 9) = 'unknown') THEN 'as expected' ELSE error('the map did not give its keys, values and a looked-up key, or the merge did not let the second map override the first') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the map did not give its keys, values and a looked-up key, or the merge did not let the second map override the first”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Apache DataFusion (1)
  • Map Functionsread 2026-09-27

    6 functions, 1 of them aliases: map, map_keys, map_values, map_entries, map_extract, …

Azure Data Factory (1)
  • Map functionsread 2026-09-27

    4 functions: associate, keyValues, mapAssociation, reassociate, …

DuckDB (1)
Ibis (7)
  • Mapread 2026-09-27

    Build a map from an array of keys and an array of values

  • MapContainsread 2026-09-27

    Test whether a map has a given key

  • MapGetread 2026-09-27

    Look up a map value by key, with a default when the key is absent

  • MapKeysread 2026-09-27

    Keys of a map as an array

  • MapLengthread 2026-09-27

    Count the keys of a map

  • MapMergeread 2026-09-27

    Combine two maps into one; on a shared key the value from the first map is kept

  • MapValuesread 2026-09-27

    Values of a map as an array

Background

An associative array, or map, stores key/value pairs with each possible key at most once, and supports lookup, remove and insert operations.[1] A DuckDB MAP need not have the same keys in each row, so it suits data whose schema is unknown beforehand or varies per row.[2]

When to use it

A DuckDB MAP has a single type for all its keys and a single type for all its values, whereas each value of a STRUCT may have a different type.[2] Looking up a key that a DuckDB MAP does not contain returns NULL, rather than throwing an error as a STRUCT does.[2] DuckDB's map_contains checks whether a map contains a given key.[3] Use map_contains rather than a NULL test on the looked-up value wherever a key present with a NULL value must be told apart from a missing key. (Meridian's view) Looking up a key of the wrong type in a DuckDB MAP throws an error.[2]

DuckDB's map_concat merges maps, and on a key collision the value is taken from the last map with that key.[3] In DuckDB, the keys of a MAP are case-sensitive, while the keys of a STRUCT are case-insensitive.[4]

The method

DuckDB builds a MAP with MAP {'key1': 10, ...}, with map_from_entries from a list of key-value pairs, or from a list of keys and a list of values.[2] DuckDB's map_entries returns a list of struct(k, v), one for each key-value pair in the map.[3] The two major solutions to the dictionary problem, the design of efficient data structures for associative arrays, are hash tables and search trees.[1]

Sources

  1. [1]Associative array - Wikipediasecondary source
  2. [2]Map Type – DuckDBdocumentation
  3. [3]Map Functions – DuckDBdocumentation
  4. [4]Data Types – DuckDBdocumentation