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.

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

┌────────┬───────────┬──────────────────┐
│ 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, check it as Installation shows, and extract it into a folder of its own. Start DuckDB 2.0.0 in that folder:

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 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:

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

┌────────┬───────────┬──────────────────┐
│ 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:

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

┌────────┬───────────┬──────────────────┬──────────────────────┐
│ 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 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

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

┌────────┬───────────┬─────────────┐
│ 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 shows the command.

choose

One of your choices for each message

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

┌────────┬─────────┬──────────────────┐
│ 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 shows the command.

tag

Every label that fits each message

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

┌────────┬─────────────────────┬──────────────────┐
│ 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 shows the command.

score

A number from 0 to 1 for each message

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

┌────────┬─────────┬──────────────────┐
│ 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 shows the command.

filter

Only the messages that answer yes

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

┌────────┬──────────────────────────────────────┐
│ 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 shows the command.

rank

Every message in order, highest first

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

┌──────────────┬────────┬──────────────────────────────────────┐
│ 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 shows the command.

find

The line that answers in each message

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

┌────────┬─────────────────────────┬──────────────────┐
│ 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 shows the command.

annotate

Several questions about each message at once

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

┌────────┬───────────┬──────────┬─────────┐
│ 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 shows the command.

recognize

The names of the kinds you ask for

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

┌────────┬───────────────┬──────────────┐
│ 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 shows the command.

relate

How the names in each message connect

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

┌────────┬─────────┬───────────┬───────────────┐
│ 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 shows the command.

Every option and setting

The DuckDB guide lists every option, result column and setting, such as field := '/text', which judges one field of a JSON message. The extension's source shows how each call works. Inside DuckDB, duckdb_functions() describes each function with an example.