Join two mapping lists on a unique synthetic key

By Batu · English technical notes

Also published in our primary archive.

To join two mapping lists on a unique synthetic key, first validate each list, build an index for one side, then create new merged mappings for matching keys. Here, left contains A and B while right contains only B, so the corrected result contains only {'id': 'B', 'x': 2, 'y': 9}.

A nested-loop join can look correct with unique fixture data, but it does not enforce the uniqueness assumption. The duplicate control adds a second B row on the right. The nested loop then emits two B combinations, which may be an unwanted change in result shape. unique_index makes the contract executable: it raises ValueError for that duplicate key before an index is used. The successful path validates both inputs, indexes right, and retains left-side order while producing fresh merged dictionaries.

The assertions demonstrate this particular in-memory fixture: duplicate right-hand B values are rejected, the unique inputs yield one row, and neither original list changes. They do not validate required fields, resolve conflicting non-key fields, or define an outer-join policy. Dictionary unpacking shown here lets right-side fields overwrite equal left-side field names, so choose a different merge rule if that is not wanted.

The Python tutorial documents dictionaries as key-to-value mappings and shows dictionary construction and lookup patterns. This example uses long-established Python 3 features; it has no special minimum version beyond a supported Python 3 release.

AI assistance disclosure: This article was drafted with AI assistance and uses a synthetic, locally reproducible fixture.

Sources: Python data structures: dictionaries.

from copy import deepcopy


def unique_index(rows, key):
    index = {}
    for row in rows:
        value = row[key]
        if value in index:
            raise ValueError(f"duplicate {key}: {value}")
        index[value] = row
    return index


left = [{"id": "A", "x": 1}, {"id": "B", "x": 2}]
right = [{"id": "B", "y": 9}]
original_left = deepcopy(left)
original_right = deepcopy(right)

duplicate_right = right + [{"id": "B", "y": 10}]
nested_duplicate = [
    {**left_row, **right_row}
    for left_row in left
    for right_row in duplicate_right
    if left_row["id"] == right_row["id"]
]
assert nested_duplicate == [
    {"id": "B", "x": 2, "y": 9},
    {"id": "B", "x": 2, "y": 10},
]

try:
    unique_index(duplicate_right, "id")
except ValueError as error:
    duplicate_error = str(error)
else:
    raise AssertionError("duplicate right IDs should be rejected")
assert duplicate_error == "duplicate id: B"

unique_index(left, "id")
right_by_id = unique_index(right, "id")
joined = [
    {**left_row, **right_by_id[left_row["id"]]}
    for left_row in left
    if left_row["id"] in right_by_id
]

assert joined == [{"id": "B", "x": 2, "y": 9}]
assert left == original_left
assert right == original_right
print("nested duplicate:", nested_duplicate)
print("duplicate check:", duplicate_error)
print("left:", left)
print("right:", right)
print("joined:", joined)

nested duplicate: [{'id': 'B', 'x': 2, 'y': 9}, {'id': 'B', 'x': 2, 'y': 10}]
duplicate check: duplicate id: B
left: [{'id': 'A', 'x': 1}, {'id': 'B', 'x': 2}]
right: [{'id': 'B', 'y': 9}]
joined: [{'id': 'B', 'x': 2, 'y': 9}]