Shape / rows · All operations

Split a list into rows

One row to many: unnest, explode, split to rows.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
more rows, each traceable to one input row
Level
several clauses of SQL, or a window

The DuckDB step

Split a delimited column to rows

It reads the orders table the proofs share. Create it first:

SQL
CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL
CREATE TEMP TABLE result AS
SELECT order_id, unnest(string_split(tags, '|')) AS tag FROM orders WHERE tags IS NOT NULL;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT count(*) = 13 AND count(*) FILTER (WHERE order_id = 10) = 3 FROM result)) THEN 'as expected' ELSE error('the tags did not expand to 13 rows with three for order 10') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the tags did not expand to 13 rows with three for order 10”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Keep rows whose list is empty

Unnest while keeping a row with a null element where the list is null or empty.

Flatten nested records into rows

Expand nested tables, record sets, XML or graph values into rows and columns of their own.

Other ways to make more rows

Insert rows for gaps, chunk text or map one row onto several.

Turn each list element into a row

Emit one row per element of a list, array or JSON array column, repeating the other columns.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Alteryx Designer (1)
  • Generate Rowsread 2026-09-27

    Create new rows per record from a loop expression: number, date or transaction sequences

Apache DataFusion (4)
  • UNNESTread 2026-09-27

    Expand a list or array into one row per element, in FROM or the SELECT list

  • unnestread 2026-09-27

    Expand an array or map into rows

  • unnest_columnsread 2026-09-27

    DataFrame method: Expand list or struct columns into rows and new columns

  • unnest_outerread 2026-09-27

    Outer unnest: keep a row when the input list is NULL or empty

Apache Hop (3)
  • Clone rowread 2026-09-27

    Emit N copies after each row, optionally flagging which rows are clones

  • Neo4j split graphread 2026-09-27

    Split a graph value into separate rows for its nodes and relationships

  • Split fields to rowsread 2026-09-27

    Split a delimited field into one new row per value

Apache NiFi (3)
  • ForkRecordread 2026-09-27

    Fork a record into several records from array fields named by RecordPath

  • SplitJsonread 2026-09-27

    Split a JSON array chosen by JsonPath into one FlowFile per element

  • SplitXmlread 2026-09-27

    Split an XML document into one FlowFile per child or descendant of the root element

Apache SeaTunnel (2)
  • LATERAL VIEW EXPLODEread 2026-09-27

    Expand an array column into one row per element inside the SQL transform

  • TextChunkread 2026-09-27

    Next docs, not in 2.3.13: split a long text field into chunks, one output row per chunk

AWS Glue (2)
Azure Data Factory (1)
  • Flattenread 2026-09-27

    Unroll array values inside hierarchical structures into individual rows

DuckDB (2)
  • Unnesting (clause)read 2026-09-27

    Turn a list into rows, or a struct into columns

  • unnestread 2026-09-27

    unnest table function

Duckle (3)
  • Explode / Unnestread 2026-09-25

    unnest a list column into rows

  • Normalizeread 2026-09-25

    explode a delimited or array column into rows

  • Zip Arrays to Tableread 2026-09-25

    zip a headings list and row arrays into one row per record with real columns

EasyMorph (3)
Ibis (2)
  • TableUnnestread 2026-09-27

    Expand an array column into rows alongside the other columns, optionally with offsets

  • Unnestread 2026-09-27

    Expand an array column into one row per element

KNIME Analytics Platform (1)
  • Ungroupread 2026-09-27

    Explode a collection column into one row per element, repeating the other columns

OpenRefine (1)
Pentaho Data Integration (2)
  • Clone rowread 2026-09-27

    Emit each row as many times as required

  • Split field to rowsread 2026-09-27

    Split a delimited string field into one row per term

Polars (2)
Power Query M (3)
  • Table.ExpandListColumnread 2026-09-27

    Emit one row per item of a list-valued column, copying the other columns

  • Table.ExpandTableColumnread 2026-09-27

    Spread a nested-table column into columns and rows of the outer table

  • Table.Repeatread 2026-09-27

    Repeat the whole set of rows a given number of times

RapidMiner Studio (1)
  • Fill Data Gapsread 2026-09-27

    Insert rows of nulls where the ID sequence has gaps

Talend (4)
  • tDuplicateRowread 2026-09-27

    Generate duplicate copies of input rows, flagging the original of each group, as test data

  • tNormalizeread 2026-09-27

    Split a column of delimited items into one row per item

  • tParseRecordSetread 2026-09-27

    Parse a recordset held in a column, from a table or query, into individual records

  • tSplitRowread 2026-09-27

    Split one input row into several output rows

Trifacta (4)
VisiData (1)
  • unfurl-colread 2026-09-23

    Open sheet with one row per element of current list or dict column, other columns repeated

Background

In DuckDB, unnesting turns a list into a table column: each list element creates a row, and each element becomes a column value.[1] In pandas, DataFrame.explode transforms each element of a list-like to a row, and in PySpark, explode returns a new row for each element of an array.[2][3] In DuckDB, unnesting a list duplicates the input row, so the other values in the SELECT are repeated beside each element.[1] In PostgreSQL, the unnest function expands an array into a set of rows, reading the elements out in storage order.[4]

When to use it

First normal form (1NF), defined by Edgar F. Codd, holds that each field is atomic, containing a single value rather than a set of values or a nested table.[5] Normalising a table to 1NF extracts attributes with nonsimple domains to separate relations, each gaining a foreign key referencing the primary key of the relation that held it.[5] Splitting a list into rows is the query-time version of that step: once each element has a row of its own, it can be filtered, counted, grouped or joined like any other value. (Meridian's view)

In DuckDB, unnesting an empty list or a NULL value unnests no elements and generates no output rows.[1] In pandas, DataFrame.explode instead turns an empty list-like into NaN for that row, and returns a scalar unchanged.[2] In PySpark, explode_outer produces a null where the array or map is null or empty, unlike explode.[6] In tidyr, unnest_longer drops the entire row when its element has size 0, such as NULL or an empty vector, unless keep_empty = TRUE is set.[7] Before splitting, decide whether a row with an empty list should vanish or stay with a missing value, and count the rows before and after to check that it did. (Meridian's view)

In DuckDB, lists unnested in the same SELECT clause are unnested side by side, and the shorter list is padded with NULL values.[1] In PostgreSQL, unnest with several arrays pads the shorter ones with NULLs, and that form is only allowed in a query's FROM clause.[4] In pandas, exploding several columns at once needs their list-likes on the same row to have matching length, and raises ValueError when they do not.[2] Split two lists in one step only when their elements belong together by position; split unrelated lists one at a time. (Meridian's view) Sum or count the repeated columns before the split rather than after it. (Meridian's view)

In pandas, the ordering of rows in the output is non-deterministic when exploding sets.[2] In DuckDB, unnest yields only the element values, and generate_subscripts can be combined with it to keep track of each entry's position in the list.[1] In PostgreSQL, WITH ORDINALITY adds a bigint column numbering the rows of a function's result from 1, a generalisation of the SQL-standard UNNEST ... WITH ORDINALITY.[8] Keep each element's position whenever the order means something, such as a ranking or a sequence of steps. (Meridian's view)

The method

In DuckDB, unnesting decomposes a value of a composite type into its components: each element of a list creates a row, and each member of a struct creates a column.[1] In DuckDB, unnest can be called in the SELECT clause as if it were a scalar function, and on a list it may also appear in the FROM clause as a table function.[1] In DuckDB, unnest unpacks only the outermost components by default, and its recursive and max_depth parameters carry the unnesting into lists within lists.[1]

In PostgreSQL, a function called in the FROM clause may take its arguments from columns of the FROM items before it, with or without the LATERAL key word.[8] In PostgreSQL, a LATERAL item is evaluated for each row that supplies its columns, and the rows it returns are joined with the rows they were computed from.[8] Every split to rows can be read as that kind of per-row join, pairing each row with the elements computed from it, which is why the other columns repeat. (Meridian's view)

Sources

  1. [1]Unnesting – DuckDBdocumentation
  2. [2]pandas.DataFrame.explode — pandas 3.1.1 documentationdocumentation
  3. [3]pyspark.sql.functions.explode — PySpark 4.3.0 documentationdocumentation
  4. [4]PostgreSQL: Documentation: 18: 9.19. Array Functions and Operatorsdocumentation
  5. [5]First normal form - Wikipediasecondary source
  6. [6]pyspark.sql.functions.explode_outer — PySpark 4.3.0 documentationdocumentation
  7. [7]Unnest a list-column into rows — unnest_longer • tidyrdocumentation
  8. [8]PostgreSQL: Documentation: 18: 7.2. Table Expressionsdocumentation

Used in

3 Protocols use this operation: