Maps in SQL

Concepts covered: Maps

Key-value pairs as a single value

A map holds pairs: each key appears once and points to one value. Its type names both halves, MAP(VARCHAR, INTEGER) for string keys with whole-number values. Where an array answers the question of what sits at position 3, a map answers what is stored under the key sauce. Keys carry the meaning, so you look things up by name and never count positions.

The order_extras table records how many of each extra a customer requested, 1 column per extra. The restaurant team is about to add chopsticks, straws and extra ice to the list. Adding a column for every new extra means altering the table and every query on it each time. Packing the counts into a map keyed by the extra's name gives one extras value per order that grows by gaining keys.

MAP(keys, values) builds a map from 2 arrays of the same length: the first supplies the keys, the second the values, paired up by position. Here the keys array is a literal and the values array is built from the row's columns, so each row gets a map of its own counts.

SELECT
  order_id,
  MAP(ARRAY['utensils', 'napkins', 'sauce'], ARRAY[utensils, napkins, sauce]) AS extras
FROM order_extras

The result prints each pair as key=value inside braces. Every row now holds one value of type MAP(VARCHAR, INTEGER), and a fourth extra would be a fourth key inside that value, with no change to the column list.

Looking up a key

Square brackets after a map take a key instead of a position. extras['sauce'] returns the value stored under sauce. As with arrays, the bracket form raises an error if the key is absent, and ELEMENT_AT(map, key) returns NULL instead. Run both against a key that exists and one that does not.

SELECT
  order_id,
  MAP(ARRAY['utensils', 'napkins', 'sauce'], ARRAY[utensils, napkins, sauce])['sauce'] AS sauce_packets,
  ELEMENT_AT(MAP(ARRAY['utensils', 'napkins', 'sauce'], ARRAY[utensils, napkins, sauce]), 'chopsticks') AS chopsticks
FROM order_extras

Order 5002 requested 0 sauce packets, and 0 is a stored value, so the lookup returns 0. No order has a chopsticks key yet, so ELEMENT_AT returns NULL on every row. Writing ['chopsticks'] with brackets would stop the query with an error naming the missing key, which is the behavior you want during development and the one you avoid in a scheduled report.

Keys, values and size

MAP_KEYS(map) returns the keys as an array, MAP_VALUES(map) returns the values as an array in the matching order, and CARDINALITY(map) counts the pairs. These are how you inspect a map whose keys you do not know in advance, such as an extras map after the team has added a few more.

SELECT
  order_id,
  MAP_KEYS(MAP(ARRAY['utensils', 'napkins', 'sauce'], ARRAY[utensils, napkins, sauce])) AS extra_names,
  CARDINALITY(MAP(ARRAY['utensils', 'napkins', 'sauce'], ARRAY[utensils, napkins, sauce])) AS extra_count
FROM order_extras

Array

• Same-typed values held in sequence
• Looked up by position, counting from 1
• CARDINALITY counts elements, CONTAINS searches them

Map

• Unique keys, each with one value
• Looked up by key, order of pairs carries no meaning
• CARDINALITY counts pairs, MAP_KEYS lists the keys

An order's extras map is {utensils=0, napkins=2, sauce=0}. Which expression returns NULL?

• extras['utensils']
• CARDINALITY(extras)
• ELEMENT_AT(extras, 'straws')
• extras['straws']

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.