surface↓extraction↓smart_json
Extractionscalar · returns varchar

SMART_JSON

Extract a value from JSON using a plain-English path description

Per-row — runs once for each row.

extractionllmjsonpipeline-composable

Syntax

SMART_JSON({{ data }}, {{ path_description }})

Arguments

nametypedescription
dataJSONThe JSON value to extract from
path_descriptionVARCHARNatural language description of what to extract (e.g., 'customer name', 'order total')

About

Pull a value out of a JSON column by describing in plain English what you want — no need to remember exact key names or hand-write `->` / `->>` paths. Say "customer name" or "order total" and the operator figures out the right extraction for your JSON shape. Built for the case where a column of JSON shares a structure (API responses, webhook payloads, log blobs, nested config). The first time the operator sees a new structure it calls the model to generate the correct extraction expression, then caches that expression keyed on the structure. Every subsequent row with the same shape runs at native SQL speed, no model call. For a one-off dig into a single JSON blob, the built-in DuckDB JSON functions (json_extract, ->, ->>) are faster. Reach for SMART_JSON when you want the extraction to "just work" across a column and when the JSON shape is consistent enough to benefit from structural caching.

Examples

Extracts a named field from valid JSON using a natural-language path

SELECT
  smart_json ('{"name":"John","age":30}'::JSON, 'name')

Nearby rabbit holes

same domain
scalar

CRAWL_BATCH

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

extractionexternal-apiurl
scalar

EXTRACT

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

extractionnlispecialist-zootext
scalar

EXTRACT_LLM

LLM-backed extraction (escape hatch for EXTRACTS)

extractionllmllm-escape-hatchtext
scalar

EXTRACT_STRUCTURED

Extract structured fields from text per a user-supplied schema

extractionllmtext
aggregate

MERGE_TIMELINES

Merge multiple timelines into unified chronological sequence

extractionllmjson
scalar

PARSE

Extract information from text using natural-language instructions

extractionllmtext
Climb back to The Looking Glass