How to Join Two Files by a Shared Key

September 2026 · 5 min read · Try it in your browser →

Orders in one file, customers in another. Inventory counts in one export, product names in another. Merging them usually means opening Python or loading both into SQLite. Here's the one-command version.

The Transmute CLI (free)

The join operation merges a second row set into your data on a shared key — like a left SQL join. Given cart.json:

[
  { "sku": "A1", "qty": 2 },
  { "sku": "B2", "qty": 1 }
]

and warehouse stock arriving inline (or from a second file via shell substitution):

$ npx github:mahope/transmute cart.json \
    --pipe '[{"op":"join","on":"sku","keep":"left","prefix":"stock_","with":[{"sku":"A1","warehouse":"EU","stock":42},{"sku":"B2","warehouse":"US","stock":7}]}]' \
    --output json
[
 {
  "sku": "A1",
  "qty": 2,
  "stock_warehouse": "EU",
  "stock_stock": 42
 },
 {
  "sku": "B2",
  "qty": 1,
  "stock_warehouse": "US",
  "stock_stock": 7
 }
]

What the options mean:

Chain it like anything else — enrich, then filter and count in one pass:

$ npx github:mahope/transmute cart.json \
    --pipe '[{"op":"join","on":"sku","keep":"left","with":[...]},{"op":"filter","expr":"item.stock_stock > 0"},{"op":"count"}]' \
    --output json

Compared to the alternatives

• SQLite: sqlite3 :memory: '.import cart.csv c' ... is powerful but it's five steps of schema wrangling for what is conceptually one lookup.
• Python/pandas: df.merge() is the right tool at scale; for files under a few MB, startup cost exceeds the whole job.
• jq: possible with --slurpfile, but the expression syntax for joins is famously hard to get right from memory.

Gotchas

• Duplicate keys in the right side: only the last row per key wins — dedupe first if that matters.
• Type coercion: numeric-looking keys in CSV become numbers on the left but matching is by string, so joins still line up.