# PROMPT_JEV > Classify, score, and test text in SQL with the prompt_jev function and calibrated probabilities. ## prompt_jev function The `prompt_jev` function sends a text value and a question to a [TypeSafe](/integrations/data-science-ai/typesafe/) Jev model and returns a typed decision: a probability, a choice from a fixed list, or a position on an ordered scale. Every answer carries calibrated probabilities, so you can act on the decision and on how certain the model is about it. `prompt_jev` answers closed questions. It never returns free text. Use [`prompt`](/sql-reference/motherduck-sql-reference/ai-functions/prompt/) when you need generated text, open-ended extraction, or a schema the model fills in itself. :::info[Preview] `prompt_jev` is in [preview](/about-motherduck/feature-stages/). The function name, parameters, and return types may change. ::: ## Syntax ```sql prompt_jev(input, instructions) prompt_jev(input, instructions, noul := criteria) prompt_jev(input, instructions, choice := criteria) prompt_jev(input, instructions, score := criteria) prompt_jev(input, questions := questions) ``` `input` and `instructions` are positional. `choice`, `score`, `noul`, `questions`, and `batch_size` must be named: ```sql SELECT prompt_jev( message, 'Which team should handle this?', choice := ['billing', 'technical', 'sales'] ) FROM support_messages; ``` `choice`, `score`, and `noul` are mutually exclusive, and none of them can be combined with `questions`. Omitting all of `choice`, `score`, and `noul` runs a `noul` question. ### Parameters | **Parameter** | **Type** | **Required** | **Description** | |---|---|---|---| | `input` | `VARCHAR` | Yes | The text to evaluate. This is the only argument that can vary per row. Must be the first argument. | | `instructions` | `VARCHAR` | Conditional | The question the model answers. A non-empty constant. Required unless `questions` is supplied. | | `noul` | `VARCHAR[]` or `STRUCT(label VARCHAR, description VARCHAR)[]` | No | Runs a yes/no question. The default when `choice`, `score`, and `noul` are all omitted. If supplied, must contain exactly the labels `true` and `false`. | | `choice` | `VARCHAR[]` or `STRUCT(label VARCHAR, description VARCHAR)[]` | Conditional | Between 2 and 255 unique, non-empty labels to pick from. | | `score` | `VARCHAR[]` or `STRUCT(label VARCHAR, description VARCHAR)[]` | Conditional | Between 2 and 10 unique, non-empty levels. Array position defines the order, lowest to highest. | | `questions` | `STRUCT` or `JSON` | Conditional | Runs several questions against the same `input` in one request. See [Multiple questions](#multiple-questions). Not combined with `instructions`, `choice`, `score`, or `noul`. | | `batch_size` | `INTEGER` | No | How many non-`NULL` input rows to send per request, from 1 to 64. Defaults to 32. Only valid in single-question mode. See [Automatic batching](#automatic-batching). | Every argument except `input` must be a constant for the query. MotherDuck validates them and resolves the return type while binding the query, before the first row is read. Labels must be unique and non-empty. Descriptions may be `NULL` or a non-empty string: ```sql -- Labels only ['low', 'medium', 'high'] -- Labels with optional descriptions [ {label: 'low', description: 'Can wait several days'}, {label: 'medium', description: NULL}, {label: 'high', description: 'Requires immediate action'} ] ``` Descriptions are input metadata that helps the model tell similar labels apart. They don't change the returned `value`, which is always the label. ### Question types Each question type has its own return type. #### noul Answers "is this true?" and returns a `DOUBLE` between 0 and 1: the calibrated probability that the statement in `instructions` holds for `input`. ```sql SELECT prompt_jev( 'The payment has failed for three days and nobody has replied.', 'Does this describe an urgent problem?' ) AS urgency; ``` ```text 0.93 ``` Pass `noul` with explicit labels only to attach descriptions to `true` and `false`; the labels-only form `noul := ['true', 'false']` is equivalent to omitting `noul` entirely: ```sql SELECT prompt_jev( message, 'Does this request a refund?', noul := [ {label: 'true', description: 'The customer asks for money back'}, {label: 'false', description: 'No refund is requested'} ] ) AS refund_probability; ``` #### choice Answers "which of these options?" and returns a `STRUCT`: ```text STRUCT( choice VARCHAR, probabilities STRUCT(value VARCHAR, probability DOUBLE)[], confidence DOUBLE ) ``` - `choice` is one of the labels you passed in `choice`. - `probabilities` has one entry per label, in the order you supplied them. - `confidence` is between 0 and 1 and describes how certain the model is about the winning option. ```sql SELECT prompt_jev( 'I was charged twice for last month and the invoice does not match.', 'Which team should handle this?', choice := ['billing', 'technical', 'sales'] ) AS routing; ``` ```text {'choice': billing, 'probabilities': [{'value': billing, 'probability': 0.94}, {'value': technical, 'probability': 0.04}, {'value': sales, 'probability': 0.02}], 'confidence': 0.91} ``` Descriptions help the model disambiguate labels that read as similar: ```sql SELECT prompt_jev( message, 'Which team should handle this?', choice := [ {label: 'billing', description: 'Payments, invoices, and refunds'}, {label: 'technical', description: 'Errors, outages, and integrations'}, {label: 'sales', description: 'Pricing, trials, and upgrades'} ] ) AS routing FROM support_messages; ``` #### score Answers "which level?" against an ordered rubric and returns a `STRUCT`: ```text STRUCT( score DOUBLE, probabilities STRUCT(index UINTEGER, value VARCHAR, probability DOUBLE)[], confidence DOUBLE ) ``` - `score` is a weighted position on the scale, between `0` and `length(score) - 1`. A rubric of `['low', 'medium', 'high']` returns a value between 0 and 2, and a value of `1.4` sits between `medium` and `high`. - `probabilities` has one entry per level, ordered by `index`, with `index` the zero-based position of the level and `value` its label. - `confidence` is between 0 and 1. Order the levels from lowest to highest. The array position defines the scale. ```sql SELECT prompt_jev( 'The product is unusable and we are considering cancelling.', 'Rate the severity of this message.', score := ['low', 'medium', 'high'] ) AS severity; ``` ```text {'score': 1.82, 'probabilities': [{'index': 0, 'value': low, 'probability': 0.02}, {'index': 1, 'value': medium, 'probability': 0.14}, {'index': 2, 'value': high, 'probability': 0.84}], 'confidence': 0.88} ``` ### Which type to use TypeSafe groups decisions into six task categories. Each maps onto one of the three question types. | **Task** | **Type** | **Example** | |---|---|---| | Classification | `choice` | Route a ticket to a team, label a topic, pick a risk category | | Detection | `noul` | Flag spam, fraud, or a prompt injection attempt | | Scoring | `score` | Rate severity, content quality, or customer frustration | | Ranking | `score` or `noul` | Order candidates by semantic fit using the returned number | | Verification | `noul` | Check a citation, a policy rule, or an answer for a known failure mode | | Structured extraction | `choice` or `score` per field | One [`questions`](#multiple-questions) call per row, where the possible values are known up front | For extraction where the values are not known up front, use [`prompt`](/sql-reference/motherduck-sql-reference/ai-functions/prompt/) with a `struct` or `json_schema` instead. ## Multiple questions Ask several questions about the same `input` in a single request with `questions`. Every question needs a non-empty `instructions` string, a `type` of `noul`, `choice`, or `score`, and valid `criteria` when its type requires them: ```sql SELECT prompt_jev( message, questions := { refund: { type: 'noul', instructions: 'Does this request a refund?' }, category: { type: 'choice', instructions: 'Classify the primary subject.', criteria: ['billing', 'technical', 'other'] }, urgency: { type: 'score', instructions: 'How urgent is this?', criteria: ['low', 'medium', 'high'] } } ) AS answer FROM support_messages; ``` The result is a named `STRUCT` with one field per question, each in its question type's normal return shape: ```sql SELECT answer.refund, answer.category.choice, answer.category.confidence, answer.urgency.score FROM classified; ``` `batch_size` is only available in single-question mode; `questions` calls send one request per input row. That request carries `input` once for the whole set, so asking twenty things at once costs far fewer input tokens than twenty single-question queries over the same table, at the price of losing row batching. ### JSON escape hatch Pass `questions` as a `JSON` string to reach parts of the TypeSafe API that the native `STRUCT` form doesn't cover, such as structured or omitted `instructions`, or criteria described with arbitrary nested JSON: ```sql SELECT prompt_jev( message, questions := '{ "category": { "type": "choice", "instructions": { "question": "Classify the primary subject", "focus": ["primary intent", "requested action"] }, "criteria": { "billing": { "description": "Charges and invoices", "examples": ["duplicate charge", "missing invoice"] }, "technical": "Bugs and integrations" } } }'::JSON ) AS answer FROM support_messages; ``` MotherDuck inspects the constant JSON only to determine the SQL return type; the question configuration itself is passed to TypeSafe for validation. For `score` criteria described as arbitrary JSON, `probabilities` comes back as `JSON` rather than the native `STRUCT(index, value, probability)[]` shape. Native `score` criteria — a plain array of labels or label/description structs — always return `probabilities` in the native shape with labels as `VARCHAR`. A TypeSafe validation failure, including an HTTP 422 response, fails the query as an error rather than returning `NULL` for the row. ## Automatic batching Native single-question calls automatically batch input rows into fewer requests. The default is 32 rows per request. The optional `batch_size` argument can be used to adjust the batch size: ```sql SELECT prompt_jev( message, 'Does this request a refund?', batch_size := 16 ) FROM messages; ``` `batch_size` must be between 1 and 64; MotherDuck may still send a smaller batch when a request would otherwise exceed TypeSafe's request-size limit. Larger batches improve throughput and lower cost. Batching can introduce extra variance from sharing a context window across multiple rows, though the effect is typically small. For strict per-row isolation, set `batch_size := 1`. ## Example usage ### Build an analytical dimension from free text Classification turns a text column into a column you can group by. Declare the CTE `AS MATERIALIZED`: without it, DuckDB may re-evaluate the CTE, and call the model again, for every field you read from the result. ```sql WITH classified AS MATERIALIZED ( SELECT prompt_jev( resolution, 'What outcome does this closure note document?', choice := ['fixed', 'no_issue_found', 'access_failed', 'referred', 'unclear'] ) AS result FROM complaints ) SELECT result.choice AS outcome, count(*) AS complaints FROM classified GROUP BY outcome ORDER BY complaints DESC; ``` | **outcome** | **complaints** | |---|---| | fixed | 420 | | no_issue_found | 280 | | access_failed | 160 | | referred | 90 | | unclear | 50 | ### Filter rows with a probability A `noul` question returns a plain `DOUBLE`, so it works in a `WHERE` or `QUALIFY` clause. Materialize the score first so the model runs once per row rather than once per predicate evaluation. ```sql CREATE TABLE refund_requests AS SELECT message_id, body, prompt_jev(body, 'Does this message request a refund?') AS refund_probability FROM support_messages; SELECT message_id, body FROM refund_requests WHERE refund_probability > 0.8; ``` ### Read the probability distribution Unnest `probabilities` when you need the full distribution rather than the winning answer. ```sql WITH routed AS MATERIALIZED ( SELECT prompt_jev( body, 'Which team should handle this?', choice := ['billing', 'technical', 'sales'] ) AS routing FROM support_messages ) SELECT p.value AS team, avg(p.probability) AS mean_probability FROM routed, unnest(routing.probabilities) AS t(p) GROUP BY team ORDER BY mean_probability DESC; ``` ### Store results in a table Write results to a table so later queries read the stored value instead of calling the model again. ```sql ALTER TABLE support_messages ADD COLUMN severity STRUCT( score DOUBLE, probabilities STRUCT(index UINTEGER, value VARCHAR, probability DOUBLE)[], confidence DOUBLE ); UPDATE support_messages SET severity = prompt_jev( body, 'Rate the severity of this message.', score := ['low', 'medium', 'high'] ) WHERE severity IS NULL; ``` ## Composing the input `input` is a single text value. To evaluate several fields together, concatenate them into one string with labels that tell the model what each part is. ```sql SELECT prompt_jev( 'Subject: ' || subject || E'\n' || 'Plan: ' || plan_name || E'\n' || 'Message: ' || body, 'Does this customer describe a billing problem?' ) AS billing_problem FROM support_messages; ``` Keep everything in `input` relevant to the question. Unrelated text dilutes the signal. ## Error handling Argument problems fail the query while it's being bound, before any row is processed: | **Error** | **Cause** | |---|---| | `prompt_jev requires instructions` | `instructions` was omitted and `questions` was not supplied | | `prompt_jev "choice", "score", and "noul" cannot be combined` | More than one of `choice`, `score`, or `noul` was supplied | | `prompt_jev requires at least two criteria for type "choice"` | Fewer than two labels for `choice` | | `prompt_jev requires at least two criteria for type "score"` | Fewer than two levels for `score` | | `prompt_jev Noul criteria require exactly the labels "true" and "false"` | `noul` was supplied with labels other than `true`/`false` | | `prompt_jev criteria labels must be unique` | `choice`, `score`, or `noul` contains a duplicate label | | `prompt_jev "instructions" parameter must be a constant value` | An argument other than `input` references a column | | `AI functions are not available for your organization.` | The organization is on the Free plan or has AI functions disabled | Per-row failures do not fail the query. A row returns `NULL` when: - `input` is `NULL`. No request is sent for that row. - The request still fails after retries, or the query hits the AI function timeout. A `questions` call with a TypeSafe validation failure (including HTTP 422) is the one exception: it fails the query rather than returning `NULL`. See [JSON escape hatch](#json-escape-hatch). Check for `NULL` to find rows that did not get an answer, and fill them in with a second pass: ```sql SELECT count(*) FROM support_messages WHERE severity IS NULL AND body IS NOT NULL; ``` Retryable responses (HTTP 429, 500, 502, 503, 504, and 529) are retried up to four times. MotherDuck honors a `Retry-After` header when the API sends one, and otherwise backs off exponentially starting at two seconds. A single request times out after 30 seconds. ## Performance considerations - **Throughput**: a Duckling keeps up to 32 TypeSafe requests in flight. Measured throughput is around 73 rows per second at that concurrency. - **Batching**: single-question calls batch multiple rows into one request by default. See [Automatic batching](#automatic-batching). - **Constant input**: a query whose `input` is a literal sends one request and reuses the answer. - **One call per row**: `SELECT prompt_jev(body, '...') FROM t` calls the model once for every row the query evaluates. Use `LIMIT` while you iterate on the instructions. - **Caching**: results are not cached between queries. Store them in a table when you need them more than once. ## Notes These capabilities are provided by MotherDuck's integration with TypeSafe. Text you pass as `input`, along with your `instructions` and criteria, is sent to the TypeSafe API for processing. See the [TypeSafe integration page](/integrations/data-science-ai/typesafe/) for what MotherDuck sends and how to turn the function off for your organization. `prompt_jev` runs against the `jev-latest` model alias. There is no model parameter. Usage is metered on input tokens; output tokens are not charged. One AI Unit covers 19 million input tokens. Consumption counts against the same Advanced AI function budget as `prompt` and `embedding`. See [AI function pricing](/about-motherduck/billing/pricing#ai-function-pricing). Illustrative throughput per AI Unit, assuming 40-character instructions and four 10-character labels for `choice` and `score`: | **Question** | **Input length** | **Rows per AI Unit** | |---|---|---| | `noul` | 50 characters | ~345,000 | | `choice` | 50 characters | ~260,000 | | `choice`, `batch_size := 1` | 50 characters | ~60,000 | | `choice` | 1,000 characters | ~60,000 | | `score` | 1,000 characters | ~60,000 | [Batching](#automatic-batching) matters most for short inputs: at 50 characters, the default batch size yields roughly four times the rows per AI Unit of `batch_size := 1`. If you need higher usage limits or have specific requirements, see the [support page](/troubleshooting/support/). --- ## Docs feedback MotherDuck accepts optional user-submitted feedback about this page at `GET https://motherduck.com/docs/api/feedback/agent`. For agents and automated tools, feedback submission should be user-confirmed before sending. URL-encode query parameter values and send a GET request: ```text GET https://motherduck.com/docs/api/feedback/agent?page_path=%2Fsql-reference%2Fmotherduck-sql-reference%2Fai-functions%2Fprompt-jev%2F&page_title=PROMPT_JEV&text= ``` Optionally append `&source=` such as `claude.ai` or `chatgpt`. `page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": ""}`, `400` for malformed query parameters, and `429` when rate-limited.