The Pain: Pairing Parallel Arrays is Tedious

Imagine you have product data where part names and prices are stored in two separate parallel arrays:

CREATE TABLE products AS SELECT * FROM (VALUES
    (1, 'Laptop',    ['CPU','RAM','SSD'],    [4000, 2000, 1500]),
    (2, 'Phone',     ['Screen','Battery'],   [3000, 1500]),
    (3, 'Tablet',    ['Screen','CPU','Battery'], [2000, 3000, 1000])
) AS t(id, name, parts, prices);

Now you need to pair each part with its corresponding price. What’s the traditional approach?

# Traditional Python approach
for part, price in zip(row['parts'], row['prices']):
    print(f"{part}: {price}")

In pure SQL, many people try double UNNEST with a JOIN, but index-aligned pairing of parallel arrays is a well-known pitfall — two arrays of different lengths produce a Cartesian product instead of element-wise pairing.

One Trick: LIST_ZIP

DuckDB’s built-in LIST_ZIP function solves this elegantly:

SELECT id, name, list_zip(parts, prices) AS priced_parts
FROM products;

Result:

┌─────┬─────────┬────────────────────────────────────────────────┐
│ id  │  name   │                  priced_parts                  │
├─────┼─────────┼────────────────────────────────────────────────┤
│ 1   │ Laptop  │ [(CPU, 4000), (RAM, 2000), (SSD, 1500)]        │
│ 2   │ Phone   │ [(Screen, 3000), (Battery, 1500)]              │
│ 3   │ Tablet  │ [(Screen, 2000), (CPU, 3000), (Battery, 1000)] │
└─────┴─────────┴────────────────────────────────────────────────┘

Elements pair automatically. Arrays of different lengths are truncated to the shorter one. One line of SQL, done.

Quantified Impact

Core benefit: 15 lines of SQL compressed into 1, development time from 30 minutes down to 2.

Advanced Usage

1. Flatten to Individual Rows

Combine with UNNEST to expand paired results into separate rows:

SELECT p.id, p.name, z[1] AS part, z[2] AS price
FROM products p,
UNNEST(list_zip(p.parts, p.prices)) AS t(z);

┌─────┬─────────┬──────────┬───────┐
│ id  │  name   │  part    │ price │
├─────┼─────────┼──────────┼───────┤
│ 1   │ Laptop  │ CPU      │  4000 │
│ 1   │ Laptop  │ RAM      │  2000 │
│ 1   │ Laptop  │ SSD      │  1500 │
│ 2   │ Phone   │ Screen   │  3000 │
│ 2   │ Phone   │ Battery  │  1500 │
│ 3   │ Tablet  │ Screen   │  2000 │
│ 3   │ Tablet  │ CPU      │  3000 │
│ 3   │ Tablet  │ Battery  │  1000 │
└─────┴─────────┴──────────┴───────┘

2. Custom Struct Field Names

Use LIST_TRANSFORM with STRUCT_PACK to generate named structs:

SELECT id, name,
    list_transform(
        list_zip(parts, prices),
        lambda x: struct_pack(part => x[1], price => x[2])
    ) AS items
FROM products;

┌─────┬─────────┬─────────────────────────────────────────────────────┐
│ id  │  name   │                      items                          │
├─────┼─────────┼─────────────────────────────────────────────────────┤
│ 1   │ Laptop  │ [{part: CPU, price: 4000}, ...]                     │
│ 2   │ Phone   │ [{part: Screen, price: 3000}, ...]                  │
│ 3   │ Tablet  │ [{part: Screen, price: 2000}, ...]                  │
└─────┴─────────┴─────────────────────────────────────────────────────┘

3. Handle Mismatched Lengths

By default, LIST_ZIP truncates to the shorter array. Pass false to pad with NULL:

-- Default: truncate to shortest
SELECT list_zip(['a','b','c'], [1, 2]) AS truncated;
-- Result: [(a, 1), (b, 2)]

-- Pad longer array with NULLs
SELECT list_zip(['a','b','c'], [1, 2], false) AS extended;
-- Result: [(a, 1), (b, 2), (c, NULL)]

4. In Python

import duckdb

con = duckdb.connect(":memory:")
con.execute("""
    CREATE TABLE products AS SELECT * FROM (VALUES
        (1, 'Laptop',    ['CPU','RAM','SSD'],    [4000, 2000, 1500]),
        (2, 'Phone',     ['Screen','Battery'],   [3000, 1500]),
        (3, 'Tablet',    ['Screen','CPU','Battery'], [2000, 3000, 1000])
    ) AS t(id, name, parts, prices)
""")

# One query to merge pairs
df = con.execute("""
    SELECT id, name, list_zip(parts, prices) AS priced_parts
    FROM products
""").df()

print(df['priced_parts'].iloc[0])
# [(CPU, 4000), (RAM, 2000), (SSD, 1500)]

Common Pitfalls

Pitfall 1: Don’t Double-UNNEST and JOIN

Many people write this:

-- ❌ Wrong: produces Cartesian product, not element-wise pairing
SELECT a.id, unnest_a.val AS part, unnest_b.val AS price
FROM products a,
UNNEST(a.parts) AS unnest_a,
UNNEST(a.prices) AS unnest_b;

This generates a 3×2=6 row (Laptop) and 2×2=4 row (Phone) Cartesian product instead of the correct 3+2=5 paired rows.

Pitfall 2: Struct Field Access

LIST_ZIP returns an array of unnamed structs. You must access elements with numeric indices [1], [2] — not dot notation like z.part. If you need named fields, combine with LIST_TRANSFORM + STRUCT_PACK.

Extended Thinking

LIST_ZIP reflects DuckDB’s first-class support for nested data types. In data analysis, parallel arrays are a very common pattern — from API-returned JSON, sensor-collected time series, to tag-and-score outputs from recommendation systems.

Typical use cases for LIST_ZIP:

1. Tag-score pairing — item IDs and scores from a recommendation system
2. Time-series alignment — timestamp arrays paired with measurement values
3. Key-value construction — pairing attribute names with attribute values
4. Data cleaning — converting parallel arrays into well-formed JSON structures

Comparison with Other Tools

DuckDB’s advantage: complete the pairing in SQL without exporting data to Python.

📖 More DuckDB practical tips → duckdblab.org

💡 Found this useful? Subscribe to DuckDB Lab for a new practical DuckDB tip every Wednesday!