# Classify text with prompt_jev > Turn a free-text column into a typed, groupable column using the prompt_jev function. Use this guide to turn a column of free text — job descriptions, support messages, closure notes, reviews, transcripts — into a column you can group, filter, and sort on. You're done when the text column has a typed companion column stored in a table. ## Before you start - A MotherDuck organization on the Lite or Business plan, in any [region](/about-motherduck/cloud-regions/). In `eu-central-1` and `eu-west-1`, an Admin must turn it on first. - A table with a `VARCHAR` column holding the text. - Write access to a database where you can store the results. The examples run against the [job postings dataset](/getting-started/sample-data-queries/job-postings/): 200,000 postings for data roles, each with the full description text. Load a slice of it into your own database to follow along: #### Load 200 job postings Database: `my_db` ```sql CREATE OR REPLACE TABLE my_db.job_postings AS SELECT * FROM 'https://us.data.motherduck.com/job_postings/parquet/year=2026/month=03/jobs.parquet' ORDER BY listed_date DESC, job_id LIMIT 200; ``` Two hundred rows is enough to work with and small enough that a full pass costs little. Every measured number on this page comes from that slice. ## Step 1: Pick the question type `prompt_jev` answers one of three question shapes. Pick the one that matches the decision you need. | **You need** | **Type** | **Returns** | |---|---|---| | One label out of a fixed list | `choice` | The winning label, per-label probabilities, and a confidence | | A rating on an ordered scale | `score` | A weighted position on the scale, per-level probabilities, and a confidence | | Whether a statement is true | `noul` | A probability between 0 and 1 | "Which role family is this?" is a `choice`. "How senior is this role?" is a `score`, because the levels have an order. "Does this posting state a salary range?" is a `noul`. When zero or more labels can apply at once, use MotherDuck's `multiple_choice` shorthand. "Which programming languages does this role require?" can return several labels, as long as you list the languages yourself. If you can't enumerate the possible answers, this isn't the right function. Use [`prompt`](/sql-reference/motherduck-sql-reference/ai-functions/prompt/) with a `struct` schema instead. To put several of these questions to the same row in one call, see [Ask many questions in one pass](#ask-many-questions-in-one-pass). ## Step 2: Write the instructions and criteria Write the instructions as a question about one row, not as a task description for the model. Keep criteria short, mutually exclusive, and phrased in the same register. #### One question, disjoint options Database: `my_db` ```sql -- Good: one question, disjoint options SELECT prompt_jev( description, 'Which kind of data role does this job posting describe?', choice := ['analytics', 'data_engineering', 'data_science', 'machine_learning', 'other'] ) FROM my_db.job_postings LIMIT 20; ``` Rules the binder enforces, so you'll see these as errors before any row runs: - `choice` and `score` need at least two labels, and they must be unique. - `choice`, `score`, `noul`, and `multiple_choice` are mutually exclusive. Pick one. - `instructions`, `choice`, `score`, `noul`, and `multiple_choice` must be constants. They can't reference a column. For `score`, order the levels from lowest to highest. The order defines the scale, and the returned `score` is a weighted position on it. #### Score a scale Database: `my_db` ```sql SELECT prompt_jev( description, 'How senior is the role this posting describes?', score := ['intern', 'junior', 'mid', 'senior', 'staff or above'] ) FROM my_db.job_postings LIMIT 20; ``` ## Step 3: Test on a sample Run against a small slice first and read the answers before spending a full pass over the table. Twenty rows is enough to catch instructions that are ambiguous or criteria that overlap. #### Sample and read the answers Database: `my_db` ```sql SELECT title, prompt_jev( description, 'Which kind of data role does this job posting describe?', choice := ['analytics', 'data_engineering', 'data_science', 'machine_learning', 'other'] ) AS role_family FROM my_db.job_postings USING SAMPLE 20 ROWS; ``` Look at `role_family.confidence` across the sample. A run where most rows come back below roughly 0.6 usually means the criteria overlap or a needed option is missing — add an `other` or `unclear` option and run again. Job postings are a good illustration: analytics engineering roles split their probability between `analytics` and `data_engineering` until one of the two labels carries a description that claims them. ## Step 4: Combine several fields into the input `prompt_jev` takes one text value per row. When the decision depends on more than one column, concatenate them with labels so the model can tell the parts apart. #### Combine several fields into the input Database: `my_db` ```sql SELECT prompt_jev( 'Title: ' || title || E' ' || 'Location: ' || location || E' ' || 'Description: ' || description, 'Does this role require working from an office at least part of the week?' ) AS onsite_required FROM my_db.job_postings LIMIT 20; ``` Leave out columns the question doesn't depend on. They add tokens and dilute the signal. A question about the work rarely needs the posting date, and a date in the input invites the model to reason about it. ## Step 5: Materialize the results The function runs once per row per query, so store the answers rather than recomputing them. #### Materialize the results Database: `my_db` ```sql CREATE TABLE my_db.posting_role_family AS SELECT job_id, title, prompt_jev( description, 'Which kind of data role does this job posting describe?', choice := ['analytics', 'data_engineering', 'data_science', 'machine_learning', 'other'] ) AS role_family FROM my_db.job_postings; ``` Keying the results table on `job_id` rather than adding a column to the source means a new question, threshold, or model produces a new table without rebuilding the postings. To add the column to an existing table instead, declare the exact return type: #### Add the column to an existing table Database: `my_db` ```sql ALTER TABLE my_db.job_postings ADD COLUMN role_family STRUCT( choice VARCHAR, probabilities STRUCT(value VARCHAR, probability DOUBLE)[], confidence DOUBLE ); UPDATE my_db.job_postings SET role_family = prompt_jev( description, 'Which kind of data role does this job posting describe?', choice := ['analytics', 'data_engineering', 'data_science', 'machine_learning', 'other'] ) WHERE role_family IS NULL AND description IS NOT NULL; ``` ## Step 6: Verify and backfill A row returns `NULL` when its input was `NULL` or when the request failed after retries. The query itself doesn't fail, so check for gaps: #### Check for gaps Database: `my_db` ```sql SELECT count(*) AS missing FROM my_db.job_postings WHERE role_family IS NULL AND description IS NOT NULL; ``` Rerun the `UPDATE` from step 5 to fill them in. The `WHERE role_family IS NULL` clause means only the missing rows are sent. ## Step 7: Query the results The classification is a plain column now. #### Query the results Database: `my_db` ```sql SELECT role_family.choice AS role_family, count(*) AS postings, round(avg(role_family.confidence), 2) AS mean_confidence FROM my_db.job_postings GROUP BY role_family ORDER BY postings DESC; ``` That run took about three seconds over 200 postings. `analytics` taking half the corpus is the title filter showing through, not a claim about the job market. For a `score` question, sort or bucket on the numeric value: #### Sort or bucket on a score Database: `my_db` ```sql SELECT date_trunc('month', listed_date) AS month, round(avg(seniority.score), 2) AS mean_seniority FROM my_db.job_postings GROUP BY month ORDER BY month; ``` ## Select several labels Use `multiple_choice` when zero or more labels can apply at once. Each candidate becomes its own independent `noul` question, so the probabilities don't compete the way a `choice` distribution does: every label can score high, and none has to. #### Select several programming languages Database: `my_db` ```sql WITH classified AS MATERIALIZED ( SELECT job_id, prompt_jev( description, 'Which programming languages does this role require?', multiple_choice := ['SQL', 'Python', 'R', 'Java', 'Go', 'Rust'] ) AS result FROM my_db.job_postings ) SELECT job_id, result.choices, result.probabilities FROM classified; ``` `result.choices` holds the labels that scored above 0.5, and is empty when none did. The full `result.probabilities` list lets you apply a different cutoff: ```sql [p.value FOR p IN result.probabilities IF p.probability > 0.75] ``` `multiple_choice` is MotherDuck shorthand rather than a Jev question type, so you can't use it as a `type` inside `questions`. Two things push you to write the `noul` questions yourself instead: - A label needs its own phrasing. `Go` matches the verb and `C` matches C++ and C#, and a bare label gives you nowhere to say so beyond its description. - You want to mix types, such as a `score` for seniority next to the language labels. Both land you in [Ask many questions in one pass](#ask-many-questions-in-one-pass), which is the same mechanism with the questions spelled out. ## Ask many questions in one pass One call can answer a whole list of questions about the same row. Pass `questions` instead of `instructions`, with one named entry per question. Each entry carries its own `type`, which can be `choice`, `score`, or `noul`, and its own `instructions`. Twenty `noul` questions, one per language, turn a description into twenty probabilities. Written out this way, each language gets instructions that rule out the labels it would otherwise swallow: #### Ask twenty questions in one call Database: `my_db` ```sql CREATE OR REPLACE TABLE my_db.posting_languages AS SELECT job_id, prompt_jev(description, questions := { sql: {type: 'noul', instructions: 'Does this posting ask for SQL?'}, python: {type: 'noul', instructions: 'Does this posting ask for Python?'}, r: {type: 'noul', instructions: 'Does this posting ask for R, the language?'}, scala: {type: 'noul', instructions: 'Does this posting ask for Scala?'}, java: {type: 'noul', instructions: 'Does this posting ask for Java, not JavaScript?'}, go: {type: 'noul', instructions: 'Does this posting ask for Go, not the verb?'}, rust: {type: 'noul', instructions: 'Does this posting ask for Rust?'}, c: {type: 'noul', instructions: 'Does this posting ask for C, not C++ or C#?'}, cpp: {type: 'noul', instructions: 'Does this posting ask for C++?'}, csharp: {type: 'noul', instructions: 'Does this posting ask for C#?'}, javascript: {type: 'noul', instructions: 'Does this posting ask for JavaScript?'}, typescript: {type: 'noul', instructions: 'Does this posting ask for TypeScript?'}, julia: {type: 'noul', instructions: 'Does this posting ask for Julia, the language?'}, kotlin: {type: 'noul', instructions: 'Does this posting ask for Kotlin?'}, ruby: {type: 'noul', instructions: 'Does this posting ask for Ruby?'}, php: {type: 'noul', instructions: 'Does this posting ask for PHP?'}, swift: {type: 'noul', instructions: 'Does this posting ask for Swift?'}, matlab: {type: 'noul', instructions: 'Does this posting ask for MATLAB?'}, bash: {type: 'noul', instructions: 'Does this posting ask for Bash scripting?'}, perl: {type: 'noul', instructions: 'Does this posting ask for Perl?'} }) AS languages FROM my_db.job_postings; ``` The answer is a `STRUCT` with one `DOUBLE` field per question, named after the key you gave it, so `languages.rust` is the probability that the posting asks for Rust. Say what a question excludes whenever two answers read alike. `C` matches C++, C#, and "C-level" until the question rules them out, and `Go` matches the verb. Twenty questions in one call send the description once. Twenty separate `noul` queries send it twenty times, and usage is metered on input tokens. A wide question set runs slower than a single question does, but MotherDuck still batches several input rows into each request. The default batch size is 32 rows, and it shrinks when twenty questions across that many rows would make the request too large. Cast the struct to a `MAP` to read the answers as rows instead of twenty columns, so adding a language later doesn't change the shape of every query downstream: #### Rank the languages Database: `my_db` ```sql SELECT e.key AS language, count(*) FILTER (e.value >= 0.5) AS postings, round(avg(e.value), 2) AS mean_probability FROM my_db.posting_languages, unnest(map_entries(languages::MAP(VARCHAR, DOUBLE))) AS t(e) GROUP BY language ORDER BY postings DESC; ``` ## Troubleshooting **`prompt_jev requires at least two criteria for type "choice"`** — add a second option, or switch to `noul` if the question is a yes/no. **`prompt_jev "choice" parameter must be a constant value`** — the label list references a column. Move the values into the query text, or run one query per label set. **`prompt_jev "choice", "score", and "noul" cannot be combined`** — drop the extra argument. Pick one question type per call, or [ask many questions in one pass](#ask-many-questions-in-one-pass). **`AI functions are not available for your organization.`** — the organization is on the Free plan, or an Admin has asked [support](/troubleshooting/support/) to disable `prompt_jev`. See the [TypeSafe integration page](/integrations/data-science-ai/typesafe/). **Many rows come back `NULL`** — the query hit the AI function timeout. Split the table into batches with `LIMIT` and `OFFSET`, or filter down to the rows that still need an answer and rerun. ## Related tasks - [Triage classifications by confidence](/key-tasks/ai-and-motherduck/triage-classifications-by-confidence/) — decide which rows to trust and which to escalate - [Job postings dataset](/getting-started/sample-data-queries/job-postings/) — the data these examples run against - [`prompt_jev` SQL reference](/sql-reference/motherduck-sql-reference/ai-functions/prompt-jev/) - [`prompt` SQL reference](/sql-reference/motherduck-sql-reference/ai-functions/prompt/) — for generated text and open-ended extraction --- ## 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=%2Fkey-tasks%2Fai-and-motherduck%2Fclassify-text-with-prompt-jev%2F&page_title=Classify%20text%20with%20prompt_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.