SQL Reference
LiteJoin uses SQLite’s SQL dialect for join and window queries. Since all stream data is stored in SQLite tables, you have access to the full power of SQLite’s query engine.
Table Schema
Every topic maps to a SQLite table with this schema:
| Column | Type | Description |
|---|
key | TEXT | Primary key — unique identifier for each record |
payload | TEXT | JSON payload string |
timestamp | INTEGER | Unix epoch seconds when the message was ingested |
Querying JSON Payloads
Use SQLite’s json_extract() function to access fields inside the JSON payload:
SELECT
key as order_id,
json_extract(payload, '$.amount') as amount,
json_extract(payload, '$.status') as status,
json_extract(payload, '$.user.name') as user_name
FROM orders
WHERE json_extract(payload, '$.amount') > 100
Common JSON Functions
| Function | Description | Example |
|---|
json_extract(col, path) | Extract a value from JSON | json_extract(payload, '$.name') |
json_type(col, path) | Return the type of a JSON value | json_type(payload, '$.items') |
json_array_length(col, path) | Count elements in a JSON array | json_array_length(payload, '$.items') |
json_each(col) | Expand a JSON array into rows | SELECT * FROM orders, json_each(payload, '$.tags') |
Time Filtering
Use timestamp and strftime() to limit queries to recent data:
-- Last hour
WHERE timestamp > (strftime('%s', 'now') - 3600)
-- Last 5 minutes
WHERE timestamp > (strftime('%s', 'now') - 300)
-- Last 24 hours
WHERE timestamp > (strftime('%s', 'now') - 86400)
Always include a time-bounded WHERE clause in join queries. Without it, queries scan the entire topic table, which degrades performance as data accumulates.
Aggregations
Standard SQL aggregate functions:
SELECT
COUNT(*) as total_orders,
SUM(json_extract(payload, '$.amount')) as total_revenue,
AVG(json_extract(payload, '$.amount')) as avg_order,
MAX(json_extract(payload, '$.amount')) as largest_order,
MIN(json_extract(payload, '$.amount')) as smallest_order
FROM orders
WHERE timestamp > (strftime('%s', 'now') - 3600)
String Functions
| Function | Description |
|---|
LOWER(str) | Convert to lowercase |
UPPER(str) | Convert to uppercase |
LENGTH(str) | String length |
SUBSTR(str, start, len) | Substring |
REPLACE(str, from, to) | Replace occurrences |
LIKE / GLOB | Pattern matching |
INSTR(str, substr) | Find substring position |
GROUP_CONCAT(col, sep) | Concatenate grouped values |
Conditional Logic
SELECT
key,
CASE
WHEN json_extract(payload, '$.amount') > 1000 THEN 'high'
WHEN json_extract(payload, '$.amount') > 100 THEN 'medium'
ELSE 'low'
END as priority
FROM orders
Type Casting
CAST(json_extract(payload, '$.amount') AS REAL)
CAST(json_extract(payload, '$.count') AS INTEGER)
Full SQLite Reference
LiteJoin supports the complete SQLite SQL dialect. For the full reference, see the SQLite documentation.
