Chunk records into multiple rows

Applies to: Embedded ❌, Enterprise ✅ Every other curation task on this page keeps the row count the same or smaller: profiling, sampling, joining, deduplicating. Chunking goes the other way — one source row becomes many derived rows, each carrying its parent’s columns along with it. Use it to split a document into passages for embedding, a video into clips, or an image into tiles.

Define a chunking Function

A Function that returns a list produces more than one value per input row. A chunker is a Function whose output is a list — of structs, of strings, of anything — registered like any other Function. See Create a Function for CPU, GPU and remote-service Functions, and scalar versus batch calling, if you have not registered one before. A function that splits text into fixed-size passages turns a documents table into one row per passage. Registration details, environment and dependency declarations, and how a version stays immutable once rows are filled from it, are the same for a chunker as for any other Function — register it from Python, or the same function from SQL with CREATE FUNCTION: Source: documents

doc_id	title	text
1	”Intro to AI"	"Machine learning is a method of…” (long text)

import pyarrow as pa
from lancedb.functions import function

@function(
    output_schema=pa.list_(
        pa.struct([
            pa.field("chunk_index", pa.int64(), nullable=False),
            pa.field("chunk_text", pa.utf8(), nullable=False),
        ])
    ),
)
def split_into_passages(text: str) -> list[dict]:
    size = 200
    return [
        {"chunk_index": i, "chunk_text": text[i : i + size]}
        for i in range(0, len(text), size)
    ]

version = db.create_function_async(split_into_passages).wait()

CREATE FUNCTION split_into_passages(text utf8)
RETURNS list<struct<chunk_index: int64, chunk_text: utf8>>
LANGUAGE python
AS '
def split_into_passages(text):
    size = 200
    return [
        {"chunk_index": i, "chunk_text": text[i:i+size]}
        for i in range(0, len(text), size)
    ]
'

Explode it in a query

Wrap the function call in UNNEST in the SELECT list, with an alias: it yields one row per element of the list the function returns, with every other selected column repeated. Reference the exploded struct’s fields through that alias in a query over this one:

WITH exploded AS (
    SELECT doc_id, title, UNNEST(split_into_passages(text)) AS p
    FROM documents
)
SELECT doc_id, title, p.chunk_index, p.chunk_text
FROM exploded

A document with five 200-character passages becomes five output rows, each carrying doc_id and title from its parent. This is the fastest way to check a chunker before committing to a materialized view: run it over a few rows and inspect the output shape.

Video into frames

Chunking a blob column is the same shape, with the chunker producing timestamps rather than text spans, and a second Function pulling the actual frame for each one. videos holds raw clips in a Blob v2 column, with each row’s duration already known: Source: videos

video_id	video	duration_seconds
”clip-1”	(Blob v2 bytes)	12.0

A chunker that samples one timestamp per second needs no blob access at all — it only looks at duration_seconds:

@function(
    output_schema=pa.list_(
        pa.struct([
            pa.field("frame_index", pa.int64(), nullable=False),
            pa.field("ts", pa.float64(), nullable=False),
        ])
    ),
)
def sample_frame_timestamps(duration_seconds: float) -> list[dict]:
    every = 1.0  # one frame per second
    count = int(duration_seconds // every)
    return [{"frame_index": i, "ts": i * every} for i in range(count)]

version = db.create_function_async(sample_frame_timestamps).wait()

A second Function reads the blob — incrementally, through a BlobReader, rather than loading the whole clip — and returns one JPEG per call:

from datetime import timedelta
from lancedb import BlobReader

@function(
    name="extract_frame",
    gpu=True,
    pip=["av==13.1.0", "pillow==11.0.0"],
    input_schema=pa.schema([
        lancedb.blob("video"),
        pa.field("ts", pa.float64(), nullable=False),
    ]),
    output_schema=lancedb.blob("jpeg", nullable=False),
)
def extract_frame(video: BlobReader, ts: float) -> bytes:
    ...  # decode the frame at ts, encode as JPEG, return its bytes

extract_version = db.create_function_async(extract_frame).wait(timeout=timedelta(minutes=15))

Compose both in one query: explode videos into one row per sampled timestamp, then call extract_frame on each row to produce its image:

WITH sampled AS (
    SELECT video_id, video, UNNEST(sample_frame_timestamps(duration_seconds)) AS f
    FROM videos
)
SELECT video_id, f.frame_index, f.ts, extract_frame(video, f.ts) AS jpeg
FROM sampled

A 12-second clip becomes 12 output rows, each with its own extracted JPEG. A Function in FROM position yields one row per list element the same way regardless of what the list holds; whether the second step produces a blob, a plain value, or another struct does not change the explosion.

Persist it as a materialized view

Wrap either query in CREATE MATERIALIZED VIEW to keep a real table that stays current as the source changes, instead of recomputing the explosion on every query:

CREATE MATERIALIZED VIEW chunks AS
WITH exploded AS (
    SELECT doc_id, title, UNNEST(split_into_passages(text)) AS p
    FROM documents
)
SELECT doc_id, title, p.chunk_index, p.chunk_text
FROM exploded

Derived: chunks (1:N expansion)

doc_id	title	chunk_index	chunk_text
1	”Intro to AI”	0	”Machine learning is a method…“
1	”Intro to AI”	1	”…of teaching computers…”

The view refreshes like any other materialized view: new source rows are chunked and inserted, deleted source rows drop their children, and an updated row’s old children are replaced by freshly chunked ones on the next refresh. A single UNNEST(...) — whether unnesting a Function’s list output directly, as above, or an existing list column — is the only lateral expansion a materialized view can carry, and it can’t be combined with GROUP BY, so keep aggregation or further curation in a second step over the chunked table.

Chunk, then curate

A freshly chunked table is a normal table: dedupe it to collapse near-identical chunks, sample it for a quick look, or profile it to check how many chunks each source row produced. Continue with choosing a curation task.

Introduction

Getting Started

Concepts

Data Ingestion

Search

Data Curation

Feature Engineering

Training

Management

Maintenance

Security

Deployment

SQL Reference

Chunk records into multiple rows

Define a chunking Function

Explode it in a query

Video into frames

Persist it as a materialized view

Chunk, then curate

​Define a chunking Function

​Explode it in a query

​Video into frames

​Persist it as a materialized view

​Chunk, then curate

Define a chunking Function

Explode it in a query

Video into frames

Persist it as a materialized view

Chunk, then curate