examples · natural language

Natural language examples — copy-paste JSON.

Eight copy-paste JSON examples for natural language. Every response carries the compiled SQL alongside the rows, so the planner is never a black box. Repeat questions hit the plan cache and return at p99 under 50 ms warm. See /docs/ask for the full reference.

Basic ask

ask — simple count question

An English sentence becomes a Plan tree, then SQL, then rows. The response carries the compiled SQL so the planner is never a black box.

Request

curl -X POST "$ENGINE/v1/tenants/$T/ask" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d @- <<'JSON'
{
  "question": "how many customers do I have?"
}
JSON

Response

{
  "sql": "SELECT COUNT(*) AS n FROM shop.customers",
  "rows": [
    { "n": 4218 }
  ],
  "count": 1,
  "plan_cache": "miss",
  "elapsed_ms": 412
}

ask — with constraint

Time and ranking constraints translate into WHERE plus ORDER-by-aggregate. The planner picks the join order; the response shows the final SQL.

Request

curl -X POST "$ENGINE/v1/tenants/$T/ask" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d @- <<'JSON'
{
  "question": "top 10 customers by total order value last month"
}
JSON

Response

{
  "sql": "SELECT c.id, c.email, SUM(o.amount) AS total FROM shop.customers c INNER JOIN shop.orders o ON c.id = o.customer_id WHERE o.created_at >= '2026-04-01' AND o.created_at < '2026-05-01' GROUP BY c.id, c.email HAVING SUM(o.amount) > 0 LIMIT 10",
  "rows": [
    { "id": "c_42",  "email": "alice@example.com", "total": 4210.00 },
    { "id": "c_88",  "email": "bob@example.com",   "total": 3199.50 },
    { "id": "c_117", "email": "carol@example.com", "total": 2841.00 }
  ],
  "count": 3,
  "plan_cache": "miss",
  "elapsed_ms": 587
}

Parameterized + dates

ask — with date parameters baked in

Concrete dates in the question land as literals in the compiled SQL. The plan cache keys on the question text plus shape, so different dates compile fresh.

Request

curl -X POST "$ENGINE/v1/tenants/$T/ask" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d @- <<'JSON'
{
  "question": "orders placed between 2026-04-15 and 2026-04-30 with amount over 100"
}
JSON

Response

{
  "sql": "SELECT id, customer_id, amount, created_at FROM shop.orders WHERE created_at >= '2026-04-15' AND created_at <= '2026-04-30' AND amount > 100 LIMIT 100",
  "rows": [
    { "id": "o_5012", "customer_id": "c_42", "amount": 129.00, "created_at": "2026-04-17T10:22:01Z" },
    { "id": "o_5104", "customer_id": "c_88", "amount": 412.50, "created_at": "2026-04-22T14:41:30Z" }
  ],
  "count": 2,
  "plan_cache": "miss",
  "elapsed_ms": 481
}

ask — filter by a named entity

Names in the question resolve against the catalog. The planner picks the right column without you naming it.

Request

curl -X POST "$ENGINE/v1/tenants/$T/ask" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d @- <<'JSON'
{
  "question": "list all orders from alice@example.com"
}
JSON

Response

{
  "sql": "SELECT o.id, o.amount, o.status, o.created_at FROM shop.orders o INNER JOIN shop.customers c ON o.customer_id = c.id WHERE c.email = 'alice@example.com' LIMIT 100",
  "rows": [
    { "id": "o_201", "amount":  49.90, "status": "paid", "created_at": "2026-04-18T08:11:02Z" },
    { "id": "o_318", "amount": 129.00, "status": "paid", "created_at": "2026-04-25T16:32:48Z" }
  ],
  "count": 2,
  "plan_cache": "miss",
  "elapsed_ms": 528
}

Plan caching

ask — plan cache hit on repeat

Repeat questions hit the cached plan. p99 < 50 ms warm-cache. The plan_cache field flips from miss to hit on the second call.

Request

curl -X POST "$ENGINE/v1/tenants/$T/ask" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d @- <<'JSON'
{
  "question": "how many customers do I have?"
}
JSON

Response

{
  "sql": "SELECT COUNT(*) AS n FROM shop.customers",
  "rows": [
    { "n": 4218 }
  ],
  "count": 1,
  "plan_cache": "hit",
  "elapsed_ms": 12
}

Compiled SQL inspection

ask — response includes the compiled SQL

Every ask response carries the exact SQL that ran. Use it to verify intent, copy it into a saved query, or paste it into /v1/sql for repeat runs.

Request

curl -X POST "$ENGINE/v1/tenants/$T/ask" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d @- <<'JSON'
{
  "question": "what's the average order amount per region?"
}
JSON

Response

{
  "sql": "SELECT r.region_name, AVG(o.amount) AS avg_amount FROM shop.orders o INNER JOIN shop.customers c ON o.customer_id = c.id INNER JOIN shop.regions r ON c.region_id = r.id GROUP BY r.region_name",
  "rows": [
    { "region_name": "India",         "avg_amount": 58.41 },
    { "region_name": "United States", "avg_amount": 102.18 },
    { "region_name": "Germany",       "avg_amount":  71.90 }
  ],
  "count": 3,
  "plan_cache": "miss",
  "elapsed_ms": 504
}

ask — three-table join inferred

When the answer requires reaching into related tables, the planner adds the joins. The compiled SQL surfaces the chosen join order.

Request

curl -X POST "$ENGINE/v1/tenants/$T/ask" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d @- <<'JSON'
{
  "question": "show order count per region for paid orders"
}
JSON

Response

{
  "sql": "SELECT r.region_name, COUNT(*) AS n FROM shop.orders o INNER JOIN shop.customers c ON o.customer_id = c.id INNER JOIN shop.regions r ON c.region_id = r.id WHERE o.status = 'paid' GROUP BY r.region_name",
  "rows": [
    { "region_name": "India",         "n": 1241 },
    { "region_name": "United States", "n": 4180 },
    { "region_name": "Germany",       "n":  890 }
  ],
  "count": 3,
  "plan_cache": "miss",
  "elapsed_ms": 612
}

ask — ambiguous question is rejected, not guessed

If the planner cannot resolve the question against the schema with confidence, it returns a 422 with a hint instead of fabricating SQL.

Request

curl -X POST "$ENGINE/v1/tenants/$T/ask" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d @- <<'JSON'
{
  "question": "show me the good ones"
}
JSON

Response

{
  "error": "ambiguous_question",
  "message": "Unable to resolve 'good ones' against any column or table. Name the entity (e.g. 'customers', 'orders') and the criterion (e.g. 'top 10 by amount').",
  "status": 422
}