> ## Documentation Index
> Fetch the complete documentation index at: https://docs.litejoin.io/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL Reference

> Supported SQL syntax for querying real-time streams in LiteJoin.

# 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:

```sql theme={null}
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:

```sql theme={null}
-- 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)
```

<Warning>
  Always include a time-bounded `WHERE` clause in join queries. Without it, queries scan the entire topic table, which degrades performance as data accumulates.
</Warning>

## Aggregations

Standard SQL aggregate functions:

```sql theme={null}
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

```sql theme={null}
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

```sql theme={null}
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](https://www.sqlite.org/lang.html).