DuckDB One Trick: WITH ORDINALITY — Get Array Index While Unnesting Without Subqueries

Have you ever faced this scenario in data analysis?

• You have a tags column containing arrays like ['python', 'duckdb', 'analytics'], and you need to flatten them into rows while keeping track of which position each tag appeared at
• You’re processing comma-separated strings from legacy systems and need to number each split element
• You’re building a recommendation pipeline where item order within a list matters

The traditional approach? Write a subquery with ROW_NUMBER() OVER (PARTITION BY ...). It works — but it’s verbose, hard to read, and easy to get wrong.

Today, I’m introducing a severely underused SQL feature in DuckDB — WITH ORDINALITY — that gives you array indices for free, cutting your query from 8 lines to 1.

I. The Problem: Tracking Array Position After Flattening

Suppose you have an e-commerce table where each order has multiple line items stored as an array:

CREATE TABLE orders AS
SELECT * FROM (VALUES
    (1, ARRAY[' iPhone', 'case', 'charger']),
    (2, ARRAY[' laptop', 'mouse', 'keyboard', 'pad']),
    (3, ARRAY[' tablet'])
) AS t(order_id, items);

You want to flatten this into individual rows with a position number:

❌ The Traditional Approach (No WITH ORDINALITY)

SELECT 
    o.order_id,
    unnest_item AS item,
    rn AS position
FROM orders o,
LATERAL (
    SELECT 
        unnest(o.items) AS unnest_item,
        ROW_NUMBER() OVER () AS rn
) sub;

That’s 8 lines. And if you need to reference the original order_id inside the subquery, it gets even messier.

II. The Solution: WITH ORDINALITY

WITH ORDINALITY is a standard SQL feature that DuckDB supports natively. When you add it after UNNEST, DuckDB automatically appends an ordinal (position) column starting from 1:

SELECT 
    order_id,
    item,
    position
FROM orders,
UNNEST(items) AS t(item, position) WITH ORDINALITY;

That’s it. Three lines. Zero subqueries. Zero window functions.

Result:

 order_id |   item   | position
----------+----------+----------
        1 |  iPhone  |        1
        1 |   case   |        2
        1 | charger  |        3
        2 |  laptop  |        1
        2 |   mouse  |        2
        2 | keyboard |        3
        2 |    pad   |        4
        3 |  tablet  |        1

III. Quantified Comparison

The performance difference is negligible — DuckDB optimizes both paths similarly. The real win is code simplicity and maintainability.

IV. Practical Use Cases

Use Case 1: Flattening Tag Arrays with Position Tracking

-- E-commerce: extract tag rank for each product
SELECT 
    product_id,
    tag,
    tag_rank
FROM products,
UNNEST(tags) WITH ORDINALITY AS t(tag, tag_rank);

Use Case 2: Processing Comma-Separated Strings

-- Legacy CSV data with comma-separated values
CREATE TABLE legacy_data AS
SELECT * FROM (VALUES
    (1, 'apple,banana,cherry'),
    (2, 'dog,cat,fish'),
    (3, 'red,green,blue,yellow')
) AS t(id, values);

SELECT 
    id,
    element,
    position
FROM legacy_data,
UNNEST(string_split(values, ',')) WITH ORDINALITY AS t(element, position);

Use Case 3: Building Position-Aware Pipelines

-- When position in the array carries business meaning
-- (e.g., primary keyword, secondary keyword, etc.)
SELECT 
    order_id,
    item,
    CASE position
        WHEN 1 THEN 'primary'
        WHEN 2 THEN 'secondary'
        ELSE 'tertiary'
    END AS item_type,
    position
FROM orders,
UNNEST(items) WITH ORDINALITY AS t(item, position);

V. Common Pitfalls

Pitfall 1: FOR ALL vs WITH ORDINALITY

DuckDB also supports UNNEST(...) FOR ALL syntax, but they serve different purposes:

-- WITH ORDINALITY: keeps original columns + adds position
UNNEST(items) AS t(item, position) WITH ORDINALITY

-- FOR ALL: unnests multiple arrays in parallel, no position
UNNEST(items, other_items) FOR ALL AS t(item, other)

Pitfall 2: Ordinal Column Naming

When using the AS t(col1, col2) syntax with WITH ORDINALITY, DuckDB assigns the ordinal column the name you specify as the last alias. Make sure your alias order matches:

-- ✅ Correct: position is last
UNNEST(items) AS t(item, position) WITH ORDINALITY

-- ❌ Wrong: "position" becomes the item, ordinal gets generic name
UNNEST(items) AS t(position, item) WITH ORDINALITY

Pitfall 3: ORDER BY Interference

WITH ORDINALITY assigns positions based on the natural order of the input array, not the output order. If you add ORDER BY after unnesting, the position values don’t change — they reflect the original array order:

-- Positions reflect original array order, NOT sorted order
SELECT order_id, item, position
FROM orders,
UNNEST(items) WITH ORDINALITY AS t(item, position)
ORDER BY position;  -- position stays 1,2,3 based on original array

If you need positions based on a custom sort, apply ROW_NUMBER() after:

SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY item) AS sorted_pos
FROM (
    SELECT order_id, item, position
    FROM orders,
    UNNEST(items) WITH ORDINALITY AS t(item, position)
) base;

VI. Extended Thinking

WITH ORDINALITY isn’t just a convenience — it reflects a deeper principle in DuckDB design: let the database handle what the database knows best.

In production pipelines, this trick shines when:

1. Building recommendation features — item position in a user’s history often correlates with preference strength
2. Processing survey data — multiple-choice answers stored as arrays, where order indicates priority
3. Time-series decomposition — rolling windows stored as arrays where temporal position matters
4. ETL from legacy systems — CSV fields that were historically comma-separated with positional semantics

VII. Comparison with Other Databases

DuckDB’s advantage: consistent SQL syntax across all array operations — whether you’re filtering, transforming, or unnesting, the same patterns apply.

VII. Summary

One trick, zero subqueries. Next time you need to flatten an array while tracking positions, reach for WITH ORDINALITY — it’s the cleanest pattern in DuckDB’s array toolkit.

📖 More DuckDB practical tips → duckdblab.org

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