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.

Introduction

Getting Started

Sources

Sinks

Building Pipelines

Operations

SQL Reference

SQL Reference

Table Schema

Querying JSON Payloads

Common JSON Functions

Time Filtering

Aggregations

String Functions

Conditional Logic

Type Casting

Full SQLite Reference

​SQL Reference

​Table Schema

​Querying JSON Payloads

​Common JSON Functions

​Time Filtering

​Aggregations

​String Functions

​Conditional Logic

​Type Casting

​Full SQLite Reference

SQL Reference

Table Schema

Querying JSON Payloads

Common JSON Functions

Time Filtering

Aggregations

String Functions

Conditional Logic

Type Casting

Full SQLite Reference