AI_EXTRACT

Extract named fields from text as a compact JSON object via a registered chat model.

Category: aiReturns: STRINGDialect: DeltaForgeDeltaForge extension

Syntax

AI_EXTRACT(model_name, text, field1 [, field2 ...])
AI_EXTRACT(text USE MODEL model_name, field1 [, field2 ...])

Description

## Overview Task sugar over AI_GENERATE: instructs the model to return only a compact JSON object whose keys are exactly the given field names (null for missing fields), composed after the model's SYSTEM_PROMPT. A markdown code fence a model wraps around the JSON despite the instruction is stripped automatically, so the returned string is raw JSON. Pair with the JSON operators to project individual values, or persist the raw object and flatten downstream. The USE MODEL sugar form is accepted and rewritten to the leading-model-argument form.

Parameters

Name	Type	Description
`model_name`		The registry name of a MODEL_TYPE = CHAT model. Must be constant within a query.
`text`		The source text. NULL produces NULL without calling the provider.
`field1..N`		One or more field names (string constants). The response is a JSON object with exactly these keys; a field absent from the text comes back as null.

Examples

SELECT email_id,
       AI_EXTRACT('gpt5', body, 'invoice_no', 'amount', 'due_date') AS fields
FROM finance.bronze.invoice_emails;

-- Combine with JSON accessors to project the extracted values
SELECT email_id,
       AI_EXTRACT('gpt5', body, 'invoice_no', 'amount')::JSON ->> 'invoice_no' AS invoice_no
FROM finance.bronze.invoice_emails;

Pitfalls

Field arguments must be constants: they shape the shared task instruction.
The return value is a STRING containing JSON, not a typed struct; cast/parse before structured use, and expect occasional non-JSON output from smaller models (validate before trusting).