surface↓extraction↓unnest_smart

Extractionscalar · returns varchar

UNNEST_SMART

Expand a compound value into separate elements (handles mixed delimiters)

Per-row — runs once for each row.

extractionllmtext

Syntax

UNNEST_SMART({{ value }})

UNNEST_SMART({{ value }}, '{{ mode }}')

Arguments

name	type	description
value	VARCHAR	—
mode(optional)	VARCHAR	Mode: auto, as_rows, as_columns, detect_delimiter

About

Expand a single value into a list of separate elements — handles both actual arrays/JSON and string representations like `"apple, orange; banana | cherry"`. Useful for columns where list-like data was stored as a single string, especially with inconsistent delimiters. Works well as a preparatory step before UNNEST, aggregations, or joins. Pass an optional `mode` to steer behavior (`'auto'` by default, `'strict'` to only split on explicit delimiters, `'lenient'` to accept natural-language joins).

Examples

JSON array unnested

SELECT
  unnest_smart ('[1, 2, 3]')

Nearby rabbit holes

same domain

CRAWL_BATCH

Crawl a website and extract structured data from each page (via Firecrawl)

extractionexternal-apiurl

EXTRACT

Extract specific information from unstructured text (zero-shot NER)

extractionnlispecialist-zootext

EXTRACT_LLM

LLM-backed extraction (escape hatch for EXTRACTS)

extractionllmllm-escape-hatchtext

EXTRACT_STRUCTURED

Extract structured fields from text per a user-supplied schema

extractionllmtext

MERGE_TIMELINES

Merge multiple timelines into unified chronological sequence

extractionllmjson

PARSE

Extract information from text using natural-language instructions

extractionllmtext

Climb back to The Looking Glass

surface↓extraction↓unnest_smart

Extractionscalar · returns varchar

UNNEST_SMART

Expand a compound value into separate elements (handles mixed delimiters)

Per-row — runs once for each row.

extractionllmtext

Syntax

UNNEST_SMART({{ value }})

UNNEST_SMART({{ value }}, '{{ mode }}')

Arguments

name	type	description
value	VARCHAR	—
mode(optional)	VARCHAR	Mode: auto, as_rows, as_columns, detect_delimiter

About

Expand a single value into a list of separate elements — handles both actual arrays/JSON and string representations like `"apple, orange; banana | cherry"`. Useful for columns where list-like data was stored as a single string, especially with inconsistent delimiters. Works well as a preparatory step before UNNEST, aggregations, or joins. Pass an optional `mode` to steer behavior (`'auto'` by default, `'strict'` to only split on explicit delimiters, `'lenient'` to accept natural-language joins).

Examples

JSON array unnested

SELECT
  unnest_smart ('[1, 2, 3]')

Nearby rabbit holes

same domain

CRAWL_BATCH

Crawl a website and extract structured data from each page (via Firecrawl)

extractionexternal-apiurl

EXTRACT

Extract specific information from unstructured text (zero-shot NER)

extractionnlispecialist-zootext

EXTRACT_LLM

LLM-backed extraction (escape hatch for EXTRACTS)

extractionllmllm-escape-hatchtext

EXTRACT_STRUCTURED

Extract structured fields from text per a user-supplied schema

extractionllmtext

MERGE_TIMELINES

Merge multiple timelines into unified chronological sequence

extractionllmjson

PARSE

Extract information from text using natural-language instructions

extractionllmtext

Climb back to The Looking Glass