--- title: DuckDB description: Load the extension into DuckDB 2.0.0 and ask a question about every message a query returns, with thinkthen_decide and nine more table functions. goal: The reader loads the DuckDB extension, asks a first question of a table, sees not sure and a failure as DuckDB prints them, and finds the query for each of the ten functions. reader: An analyst or developer using DuckDB who wants answers as columns kind: reference verified: 2026-11-03 --- # DuckDB The DuckDB extension adds the ten functions as table functions; each one asks a question about every message a query returns and gives the answer as a column, such as `true` or `false` from decide. ```sql LOAD './thinkthen.duckdb_extension'; CREATE TABLE messages (msg_id INTEGER, body VARCHAR); INSERT INTO messages VALUES (1, 'Please refund my order.'), (2, 'Where is my parcel?'), (3, 'I was charged twice for one invoice.'), (4, 'I was charged twice and my parcel is late again.'), (5, 'Morgan works for Example Group. Please refund my order.'), (6, 'Morgan works for Example Group.'); SELECT msg_id, answer AS is_refund, status FROM thinkthen_decide( (SELECT msg_id, body FROM messages WHERE msg_id <= 3), 'Does the customer request a refund?', record := 'body'); ``` Output ```text ┌────────┬───────────┬──────────────────┐ │ msg_id │ is_refund │ status │ │ int32 │ boolean │ thinkthen_status │ ├────────┼───────────┼──────────────────┤ │ 1 │ true │ answered │ │ 2 │ false │ answered │ │ 3 │ true │ answered │ └────────┴───────────┴──────────────────┘ ``` Messages 1 and 3 ask for a refund, and message 2 asks about a parcel. The rest of this page runs in the same session, with the same `messages` table. ## Load the extension and ask Download the DuckDB archive for your platform from the [0.2.0 release](https://github.com/botassembly/thinkthen/releases/tag/v0.2.0), check it as [Installation](/install/) shows, and extract it into a folder of its own. Start DuckDB 2.0.0 in that folder: ```sh mkdir thinkthen-duckdb tar -xzf thinkthen-duckdb-*.tar.gz -C thinkthen-duckdb cd thinkthen-duckdb duckdb -unsigned ``` The extension is not signed, so `-unsigned` lets DuckDB load it. Load only a file you checked. DuckDB loads an extension only into the version it was built for, and its community repository does not carry this one, so `INSTALL thinkthen FROM community` finds nothing. DuckDB needs your key in its environment, as [Configuration](/configuration/) shows. In Python, open the connection with `duckdb.connect(config={'allow_unsigned_extensions': 'true'})` and run the same SQL. Your own columns come back beside the answer. If one shares a name with a result column, such as `id` or `status`, rename it in the input query, as `id AS ticket_id`. Each run of a query asks again and is billed again, so save answers you need twice with `CREATE TABLE refund_answers AS SELECT ...` and query that table. ## When decide is not sure Set a low bar and a high bar with `low` and `high`. decide says no below the low bar, yes at or above the high bar, and not sure in between: ```sql SELECT msg_id, answer AS is_refund, status FROM thinkthen_decide( (SELECT msg_id, body FROM messages WHERE msg_id <= 3), 'Does the customer request a refund?', record := 'body', low := 0.2, high := 0.8); ``` Output ```text ┌────────┬───────────┬──────────────────┐ │ msg_id │ is_refund │ status │ │ int32 │ boolean │ thinkthen_status │ ├────────┼───────────┼──────────────────┤ │ 1 │ true │ answered │ │ 2 │ false │ answered │ │ 3 │ NULL │ unsure │ └────────┴───────────┴──────────────────┘ ``` A double charge might be a refund request or might not, so message 3 comes back `unsure`, with a NULL answer. Send it to a person. Test answers with `IS TRUE` and `IS FALSE`, which are both false for NULL, so not sure never counts as a no. ## When a message fails A message that can't be judged comes back with `status` `failed`, a NULL answer and an `error_code`, and the query keeps going. Here message 7 has no text, so it fails as `record.missing`, and no question is sent for it: ```sql SELECT msg_id, answer AS is_refund, status, error_code FROM thinkthen_decide( (SELECT msg_id, body FROM messages WHERE msg_id = 1 UNION ALL SELECT 7, NULL), 'Does the customer request a refund?', record := 'body'); ``` Output ```text ┌────────┬───────────┬──────────────────┬──────────────────────┐ │ msg_id │ is_refund │ status │ error_code │ │ int32 │ boolean │ thinkthen_status │ thinkthen_error_code │ ├────────┼───────────┼──────────────────┼──────────────────────┤ │ 1 │ true │ answered │ NULL │ │ 7 │ NULL │ failed │ record.missing │ └────────┴───────────┴──────────────────┴──────────────────────┘ ``` A mistake in the call itself, such as leaving out `choices`, stops the query before anything is sent. DuckDB raises an `Invalid Input Error` whose message starts with a code such as `usage.question`. When every message fails for the same outside reason, such as a refused request, the query raises that error instead. [Troubleshooting](/reference/troubleshooting/) lists every code. ## The ten functions Each query runs in the session the first example opened. Open a function to see its query and what DuckDB prints. ### decide
Yes or no for each message ```sql SELECT msg_id, answer AS is_refund, probability FROM thinkthen_decide( (SELECT msg_id, body FROM messages WHERE msg_id <= 3), 'Does the customer request a refund?', record := 'body'); ``` Output ```text ┌────────┬───────────┬─────────────┐ │ msg_id │ is_refund │ probability │ │ int32 │ boolean │ double │ ├────────┼───────────┼─────────────┤ │ 1 │ true │ 0.99 │ │ 2 │ false │ 0.03 │ │ 3 │ true │ 0.7 │ └────────┴───────────┴─────────────┘ ``` `answer` is a `BOOLEAN`. `probability` is the chance of yes, also when the answer is no. It is high for message 1, low for message 2, and in between for message 3, the double charge. `yes_means` and `no_means` say what yes and no mean for your question. [decide](/functions/decide/) shows the command.
### choose
One of your choices for each message ```sql SELECT msg_id, answer AS topic, status FROM thinkthen_choose( (SELECT msg_id, body FROM messages WHERE msg_id = 1), 'What is the message about?', choices := ['billing', 'shipping', 'other'], record := 'body'); ``` Output ```text ┌────────┬─────────┬──────────────────┐ │ msg_id │ topic │ status │ │ int32 │ varchar │ thinkthen_status │ ├────────┼─────────┼──────────────────┤ │ 1 │ billing │ answered │ └────────┴─────────┴──────────────────┘ ``` Message 1, a refund request, is about billing. `answer` is a `VARCHAR`, and `detail.labels` gives every choice its probability. With `threshold`, or `low` and `high`, choose is not sure when the best choice falls short of the bar. [choose](/functions/choose/) shows the command.
### tag
Every label that fits each message ```sql SELECT msg_id, answer AS topics, status FROM thinkthen_tag( (SELECT msg_id, body FROM messages WHERE msg_id = 4), 'Which topics appear?', labels := ['billing', 'shipping'], record := 'body'); ``` Output ```text ┌────────┬─────────────────────┬──────────────────┐ │ msg_id │ topics │ status │ │ int32 │ varchar[] │ thinkthen_status │ ├────────┼─────────────────────┼──────────────────┤ │ 4 │ [billing, shipping] │ answered │ └────────┴─────────────────────┴──────────────────┘ ``` Message 4 is about billing and shipping at once. `answer` is a `VARCHAR[]`, which is empty when no label fits. `threshold` sets the bar a label must reach. [tag](/functions/tag/) shows the command.
### score
A number from 0 to 1 for each message ```sql SELECT msg_id, answer AS urgency, status FROM thinkthen_score( (SELECT msg_id, body FROM messages WHERE msg_id <= 3), 'How urgent is the request?', levels := ['routine', 'urgent'], record := 'body'); ``` Output ```text ┌────────┬─────────┬──────────────────┐ │ msg_id │ urgency │ status │ │ int32 │ double │ thinkthen_status │ ├────────┼─────────┼──────────────────┤ │ 1 │ 0.05 │ answered │ │ 2 │ 0.14 │ answered │ │ 3 │ 0.45 │ answered │ └────────┴─────────┴──────────────────┘ ``` The double charge, message 3, is the most urgent of the three. `answer` is a `DOUBLE`: 0 means the first level, and 1 the last. List the levels lowest first. [score](/functions/score/) shows the command.
### filter
Only the messages that answer yes ```sql SELECT msg_id, body FROM thinkthen_filter( (SELECT msg_id, body FROM messages WHERE msg_id <= 3), 'Does the customer request a refund?', record := 'body'); ``` Output ```text ┌────────┬──────────────────────────────────────┐ │ msg_id │ body │ │ int32 │ varchar │ ├────────┼──────────────────────────────────────┤ │ 1 │ Please refund my order. │ │ 3 │ I was charged twice for one invoice. │ └────────┴──────────────────────────────────────┘ ``` Messages 1 and 3 ask for a refund, so filter keeps them. The no and not-sure messages are left out. If a message fails, filter raises one error after it has judged them all. Add `failures := 'row'` to get the failed messages back too, with `status` `failed`, so you keep the answers you paid for. To keep every message with its answer, use decide. [filter](/functions/filter/) shows the command.
### rank
Every message in order, highest first ```sql SELECT answer AS urgency_rank, msg_id, body FROM thinkthen_rank( (SELECT msg_id, body FROM messages WHERE msg_id <= 3), 'How urgent is the request?', levels := ['routine', 'urgent'], record := 'body') ORDER BY urgency_rank; ``` Output ```text ┌──────────────┬────────┬──────────────────────────────────────┐ │ urgency_rank │ msg_id │ body │ │ int64 │ int32 │ varchar │ ├──────────────┼────────┼──────────────────────────────────────┤ │ 1 │ 3 │ I was charged twice for one invoice. │ │ 2 │ 2 │ Where is my parcel? │ │ 3 │ 1 │ Please refund my order. │ └──────────────┴────────┴──────────────────────────────────────┘ ``` The double charge ranks first, the parcel second and the refund request last. `answer` is a `BIGINT` rank from 1, the most urgent first. `top := 2` keeps the first two. Failed messages come after the ranked ones, and `top` never drops them. [rank](/functions/rank/) shows the command.
### find
The line that answers in each message ```sql SELECT msg_id, answer.text AS requested_action, status FROM thinkthen_find( (SELECT msg_id, body FROM messages WHERE msg_id = 5), 'Which line states the requested action?', record := 'body'); ``` Output ```text ┌────────┬─────────────────────────┬──────────────────┐ │ msg_id │ requested_action │ status │ │ int32 │ varchar │ thinkthen_status │ ├────────┼─────────────────────────┼──────────────────┤ │ 5 │ Please refund my order. │ answered │ └────────┴─────────────────────────┴──────────────────┘ ``` The second line of message 5 states what the customer wants. `answer` is a `STRUCT` with the line's `text`, `start_offset` and `end_offset`. Offsets count characters from 0, and the end is not part of the line. When no line answers, `answer` is NULL and `status` is `answered`. [find](/functions/find/) shows the command.
### annotate
Several questions about each message at once ```sql SELECT msg_id, answer.refund AS is_refund, answer.topic AS topic, answer.urgency AS urgency FROM thinkthen_annotate( (SELECT msg_id, body FROM messages WHERE msg_id <= 3), record := 'body', toolbox := '{ "refund": {"function": "decide", "question": "Does the customer request a refund?"}, "topic": {"function": "choose", "question": "What is the message about?", "choices": ["billing", "shipping", "other"]}, "urgency": {"function": "score", "question": "How urgent is the request?", "levels": ["routine", "urgent"]} }'::JSON); ``` Output ```text ┌────────┬───────────┬──────────┬─────────┐ │ msg_id │ is_refund │ topic │ urgency │ │ int32 │ boolean │ varchar │ double │ ├────────┼───────────┼──────────┼─────────┤ │ 1 │ true │ billing │ 0.05 │ │ 2 │ false │ shipping │ 0.14 │ │ 3 │ true │ billing │ 0.45 │ └────────┴───────────┴──────────┴─────────┘ ``` Each message gets all three answers from one query. `toolbox` names each question, and rank can't be one of them. `answer` is a `STRUCT` with one field per name. A field is NULL when that question was not sure or failed, and `detail` holds each question's full outcome. [annotate](/functions/annotate/) shows the command.
### recognize
The names of the kinds you ask for ```sql SELECT msg_id, found.text AS name, found.kind FROM ( SELECT msg_id, unnest(answer) AS found FROM thinkthen_recognize( (SELECT msg_id, body FROM messages WHERE msg_id = 6), kinds := ['person', 'organization'], record := 'body')); ``` Output ```text ┌────────┬───────────────┬──────────────┐ │ msg_id │ name │ kind │ │ int32 │ varchar │ varchar │ ├────────┼───────────────┼──────────────┤ │ 6 │ Morgan │ person │ │ 6 │ Example Group │ organization │ └────────┴───────────────┴──────────────┘ ``` Message 6 names a person, Morgan, and an organization, Example Group. `answer` is a list of names, each with `text`, `kind`, `start_offset`, `end_offset` and `probability`. `unnest` lists each name on its own. [recognize](/functions/recognize/) shows the command.
### relate
How the names in each message connect ```sql SELECT msg_id, link.source.text AS person, link.relation, link.target.text AS organization FROM ( SELECT msg_id, unnest(answer) AS link FROM thinkthen_relate( (SELECT msg_id, body FROM messages WHERE msg_id = 5), rules := [{'relation': 'works_for', 'source': 'person', 'target': 'organization'}], record := 'body')); ``` Output ```text ┌────────┬─────────┬───────────┬───────────────┐ │ msg_id │ person │ relation │ organization │ │ int32 │ varchar │ varchar │ varchar │ ├────────┼─────────┼───────────┼───────────────┤ │ 5 │ Morgan │ works_for │ Example Group │ └────────┴─────────┴───────────┴───────────────┘ ``` Morgan works for Example Group, so relate links the two names. Each rule has `relation`, `source` and `target`, plus optional `reads` and `either`. `answer` is a list of relations, each with `relation`, `source`, `target`, `either` and `probability`. [relate](/functions/relate/) shows the command.
## Every option and setting The [DuckDB guide](https://github.com/botassembly/thinkthen/blob/v0.2.0/docs/surfaces/duckdb.md) lists every option, result column and setting, such as `field := '/text'`, which judges one field of a JSON message. The [extension's source](https://github.com/botassembly/thinkthen/tree/v0.2.0/databases/duckdb) shows how each call works. Inside DuckDB, `duckdb_functions()` describes each function with an example.