Documentation Index
Fetch the complete documentation index at: /llms.txt
Use this file to discover all available pages before exploring further.
Supported SQL syntax for querying real-time streams in LiteJoin.
| 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 |
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
| 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') |
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)
WHERE clause in join queries. Without it, queries scan the entire topic table, which degrades performance as data accumulates.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)
| 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 |
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
CAST(json_extract(payload, '$.amount') AS REAL)
CAST(json_extract(payload, '$.count') AS INTEGER)