OriginChain docs
examples · sql

SQL examples — copy-paste JSON.

← All examples

Nineteen copy-paste JSON requests for SQL on OriginChain. Each example shows the full curl body and the JSON response the engine returns. POST against /v1/tenants/:tenant/sql. For the full surface and refused shapes, see /docs/sql.

SELECT basics

SELECT * — read every column

Return every column for every row in the table. Use sparingly on wide tables; LIMIT is encouraged.

Request
curl -X POST "$ENGINE/v1/tenants/$T/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d @- <<'JSON'
{
  "sql": "SELECT * FROM shop.customers LIMIT 3"
}
JSON
Response
{
  "rows": [
    { "id": "c_1", "email": "alice@example.com", "region": "IN", "tier": "gold" },
    { "id": "c_2", "email": "bob@example.com",   "region": "US", "tier": "silver" },
    { "id": "c_3", "email": "carol@example.com", "region": "DE", "tier": "gold" }
  ],
  "count": 3,
  "elapsed_ms": 3
}

SELECT projections — pick specific columns

Project only the columns you need. The engine reads only those bytes from the row payload.

Request
curl -X POST "$ENGINE/v1/tenants/$T/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d @- <<'JSON'
{
  "sql": "SELECT id, email FROM shop.customers LIMIT 5"
}
JSON
Response
{
  "rows": [
    { "id": "c_1", "email": "alice@example.com" },
    { "id": "c_2", "email": "bob@example.com" },
    { "id": "c_3", "email": "carol@example.com" },
    { "id": "c_4", "email": "dan@example.com" },
    { "id": "c_5", "email": "eve@example.com" }
  ],
  "count": 5,
  "elapsed_ms": 2
}

LIMIT and OFFSET — pagination

Page through results with LIMIT and OFFSET. For large offsets prefer keyset pagination on a sortable id.

Request
curl -X POST "$ENGINE/v1/tenants/$T/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d @- <<'JSON'
{
  "sql": "SELECT id, email FROM shop.customers LIMIT 10 OFFSET 20"
}
JSON
Response
{
  "rows": [
    { "id": "c_21", "email": "user21@example.com" },
    { "id": "c_22", "email": "user22@example.com" },
    { "id": "c_23", "email": "user23@example.com" }
  ],
  "count": 3,
  "elapsed_ms": 4
}

WHERE clauses

WHERE — equality predicate

Filter rows by an exact match. The planner will use a secondary index when one is declared on the column.

Request
curl -X POST "$ENGINE/v1/tenants/$T/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d @- <<'JSON'
{
  "sql": "SELECT id, email, region FROM shop.customers WHERE region = 'IN' LIMIT 10"
}
JSON
Response
{
  "rows": [
    { "id": "c_1",  "email": "alice@example.com", "region": "IN" },
    { "id": "c_19", "email": "ravi@example.com",  "region": "IN" }
  ],
  "count": 2,
  "elapsed_ms": 4
}

WHERE — comparison operators

Range predicates with =, !=, <, >, <=, >= combined under AND. AND-only WHERE is the supported surface.

Request
curl -X POST "$ENGINE/v1/tenants/$T/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d @- <<'JSON'
{
  "sql": "SELECT id, amount FROM shop.orders WHERE amount > 100 AND amount <= 500 AND status != 'refunded' LIMIT 20"
}
JSON
Response
{
  "rows": [
    { "id": "o_481", "amount": 129.00 },
    { "id": "o_482", "amount": 412.50 },
    { "id": "o_487", "amount": 248.10 }
  ],
  "count": 3,
  "elapsed_ms": 6
}

WHERE col IN (...)

Match against a small set of values. The translator folds IN into a disjunction over equalities.

Request
curl -X POST "$ENGINE/v1/tenants/$T/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d @- <<'JSON'
{
  "sql": "SELECT id, status FROM shop.orders WHERE status IN ('pending', 'paid', 'shipped') LIMIT 50"
}
JSON
Response
{
  "rows": [
    { "id": "o_001", "status": "pending" },
    { "id": "o_002", "status": "paid" },
    { "id": "o_003", "status": "shipped" }
  ],
  "count": 3,
  "elapsed_ms": 5
}

JOINs

INNER JOIN — two tables

Hash-join two tables on a single equality. The planner picks the smaller side as the build side.

Request
curl -X POST "$ENGINE/v1/tenants/$T/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d @- <<'JSON'
{
  "sql": "SELECT o.id AS order_id, o.amount, c.email FROM shop.orders o INNER JOIN shop.customers c ON o.customer_id = c.id WHERE o.status = 'paid' LIMIT 25"
}
JSON
Response
{
  "rows": [
    { "order_id": "o_201", "amount":  49.90, "email": "alice@example.com" },
    { "order_id": "o_202", "amount": 129.00, "email": "bob@example.com" }
  ],
  "count": 2,
  "elapsed_ms": 7
}

INNER JOIN — three tables (left-deep)

Up to five tables in a left-deep tree. The leftmost two join, then the result joins against the next.

Request
curl -X POST "$ENGINE/v1/tenants/$T/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d @- <<'JSON'
{
  "sql": "SELECT o.id, c.email, r.region_name 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 LIMIT 10"
}
JSON
Response
{
  "rows": [
    { "id": "o_001", "email": "alice@example.com", "region_name": "India" },
    { "id": "o_002", "email": "bob@example.com",   "region_name": "United States" }
  ],
  "count": 2,
  "elapsed_ms": 9
}

LEFT OUTER JOIN — keep unmatched left rows

Every row from the left side is kept; unmatched right columns are null-filled. OUTER JOIN execution is fully tested.

Request
curl -X POST "$ENGINE/v1/tenants/$T/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d @- <<'JSON'
{
  "sql": "SELECT c.id, c.email, o.amount FROM shop.customers c LEFT OUTER JOIN shop.orders o ON c.id = o.customer_id LIMIT 5"
}
JSON
Response
{
  "rows": [
    { "id": "c_1", "email": "alice@example.com", "amount":  49.90 },
    { "id": "c_2", "email": "bob@example.com",   "amount": 129.00 },
    { "id": "c_3", "email": "carol@example.com", "amount": null }
  ],
  "count": 3,
  "elapsed_ms": 6
}

RIGHT OUTER JOIN

Mirror of LEFT JOIN. Every row from the right side is kept, unmatched left columns are null-filled.

Request
curl -X POST "$ENGINE/v1/tenants/$T/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d @- <<'JSON'
{
  "sql": "SELECT c.email, o.id FROM shop.customers c RIGHT OUTER JOIN shop.orders o ON c.id = o.customer_id LIMIT 5"
}
JSON
Response
{
  "rows": [
    { "email": "alice@example.com", "id": "o_001" },
    { "email": null,                 "id": "o_002" }
  ],
  "count": 2,
  "elapsed_ms": 6
}

FULL OUTER JOIN

Keep unmatched rows from both sides. Useful for set-difference reports across two tables.

Request
curl -X POST "$ENGINE/v1/tenants/$T/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d @- <<'JSON'
{
  "sql": "SELECT c.id AS customer_id, o.id AS order_id FROM shop.customers c FULL OUTER JOIN shop.orders o ON c.id = o.customer_id LIMIT 5"
}
JSON
Response
{
  "rows": [
    { "customer_id": "c_1",  "order_id": "o_001" },
    { "customer_id": "c_99", "order_id": null     },
    { "customer_id": null,    "order_id": "o_777" }
  ],
  "count": 3,
  "elapsed_ms": 8
}

Anti-join via LEFT JOIN ... IS NULL

Rows on the left with no matching row on the right. The canonical anti-join pattern in supported SQL.

Request
curl -X POST "$ENGINE/v1/tenants/$T/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d @- <<'JSON'
{
  "sql": "SELECT c.id, c.email FROM shop.customers c LEFT OUTER JOIN shop.orders o ON c.id = o.customer_id WHERE o.id IS NULL LIMIT 25"
}
JSON
Response
{
  "rows": [
    { "id": "c_77", "email": "lurker@example.com" },
    { "id": "c_91", "email": "newuser@example.com" }
  ],
  "count": 2,
  "elapsed_ms": 7
}

Self-join with aliasing

Alias the same table twice. Common for hierarchical reports — manager-and-report, parent-and-child.

Request
curl -X POST "$ENGINE/v1/tenants/$T/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d @- <<'JSON'
{
  "sql": "SELECT e.id AS report_id, e.name AS report_name, m.name AS manager_name FROM hr.employees e INNER JOIN hr.employees m ON e.manager_id = m.id LIMIT 10"
}
JSON
Response
{
  "rows": [
    { "report_id": "e_12", "report_name": "Priya",  "manager_name": "Raj"   },
    { "report_id": "e_13", "report_name": "Sandeep", "manager_name": "Raj"   }
  ],
  "count": 2,
  "elapsed_ms": 5
}

Aggregates, GROUP BY, HAVING

COUNT(*) — total rows

Aggregate without a GROUP BY returns one row.

Request
curl -X POST "$ENGINE/v1/tenants/$T/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d @- <<'JSON'
{
  "sql": "SELECT COUNT(*) AS n FROM shop.orders"
}
JSON
Response
{
  "rows": [
    { "n": 12834 }
  ],
  "count": 1,
  "elapsed_ms": 11
}

GROUP BY with SUM, AVG, MIN, MAX

Multiple aggregates in one pass. The hash aggregator buckets on the group key and folds each aggregate in place.

Request
curl -X POST "$ENGINE/v1/tenants/$T/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d @- <<'JSON'
{
  "sql": "SELECT status, COUNT(*) AS n, SUM(amount) AS total, AVG(amount) AS avg_amt, MIN(amount) AS min_amt, MAX(amount) AS max_amt FROM shop.orders GROUP BY status"
}
JSON
Response
{
  "rows": [
    { "status": "paid",    "n": 8201, "total": 482190.50, "avg_amt": 58.80, "min_amt":  4.99, "max_amt": 1299.00 },
    { "status": "pending", "n": 1402, "total":  74910.00, "avg_amt": 53.43, "min_amt":  9.90, "max_amt":  799.00 }
  ],
  "count": 2,
  "elapsed_ms": 18
}

HAVING — filter on aggregates

HAVING applies after GROUP BY. Express predicates on COUNT, SUM, AVG, MIN, MAX here.

Request
curl -X POST "$ENGINE/v1/tenants/$T/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d @- <<'JSON'
{
  "sql": "SELECT status, COUNT(*) AS n FROM shop.orders GROUP BY status HAVING COUNT(*) > 1000"
}
JSON
Response
{
  "rows": [
    { "status": "paid",    "n": 8201 },
    { "status": "pending", "n": 1402 }
  ],
  "count": 2,
  "elapsed_ms": 16
}

Mutations (INSERT, DELETE)

INSERT — single row

One row per VALUES tuple. Translates to /v1/rows/:t — one WAL frame, atomic with all index, FTS, vector, and relation maintenance.

Request
curl -X POST "$ENGINE/v1/tenants/$T/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d @- <<'JSON'
{
  "sql": "INSERT INTO shop.orders (id, customer_id, amount, status) VALUES ('o_9001', 'c_42', 49.90, 'pending')"
}
JSON
Response
{
  "inserted": 1,
  "lsn": 41827193,
  "elapsed_ms": 4
}

INSERT — multi-row VALUES

Several rows in one frame. Atomicity holds across the entire batch — all rows fsync together or none do.

Request
curl -X POST "$ENGINE/v1/tenants/$T/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d @- <<'JSON'
{
  "sql": "INSERT INTO shop.orders (id, customer_id, amount, status) VALUES ('o_9101', 'c_11', 19.00, 'pending'), ('o_9102', 'c_12', 89.00, 'pending'), ('o_9103', 'c_13', 12.50, 'pending')"
}
JSON
Response
{
  "inserted": 3,
  "lsn": 41827212,
  "elapsed_ms": 5
}

DELETE — equality on primary key

DELETE requires equality on the manifest's declared primary key. No accidental table-wide deletes.

Request
curl -X POST "$ENGINE/v1/tenants/$T/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d @- <<'JSON'
{
  "sql": "DELETE FROM shop.orders WHERE id = 'o_9001'"
}
JSON
Response
{
  "deleted": 1,
  "lsn": 41827224,
  "elapsed_ms": 3
}