# 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. :::info[Preview] `prompt_jev` is in [preview](/about-motherduck/feature-stages/). The function name, parameters, and return types may change. ::: ## Before you start - A MotherDuck organization on the Lite or Business plan. - 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`. 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`, and `noul` are mutually exclusive. Pick one. - `instructions`, `choice`, `score`, and `noul` 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; ``` ## 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` and `instructions`. Twenty `noul` questions, one per language, turn a description into twenty probabilities: #### 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. What you give up is throughput. `batch_size` only applies to single-question calls, so a `questions` call sends one request per row instead of packing 32 rows into one. Over a large table a wide question set runs slower than a single question does. 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 AI functions disabled. 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.