# PPL: pipe query language

PPL expresses a query as a sequence of stages separated by `|`. Start with `FROM`, then search, filter, transform,
aggregate or join the rows. Each stage receives the result of the previous stage.

Send PPL in the `query` field of `POST /v1/query`. It works with one collection or several. For the response format,
permissions and request limits, see [Multi-collection queries](multi-collection-queries.md).

## Your first pipeline

Count failed login events by user across application and audit logs:

```bash
curl -X POST https://api.warpsearch.io/v1/query \
  -H "Authorization: Bearer $WARPSEARCH_API_KEY" \
  -H "Content-Type: application/json" \
  --data @- <<'EOF_REQUEST'
{
  "query": "FROM app_logs, audit_logs | SEARCH term(action_s, $action) | SEARCH term(outcome_s, $outcome) | STATS count() AS failures BY user_s | SORT failures DESC | LIMIT 20",
  "params": {"action": "login", "outcome": "failure"}
}
EOF_REQUEST
```

`FROM` combines the inputs, `SEARCH` selects matching events, `STATS` counts by user, and `SORT` and `LIMIT` select the
largest groups. The collection and field names must exist in your own dataset; the examples use typed field names
such as `user_s`, `status_i` and `message_t`. Schema-defined names work too.

PPL has its own supported syntax. It does not accept Splunk SPL, Elasticsearch ES|QL or OpenSearch PPL verbatim.
It is not selected with `"language": "ppl"` on the ordinary search endpoint.

## Search before processing

Use `SEARCH` for indexed filtering immediately after `FROM`. Repeated SEARCH stages require all conditions to match:

```text
FROM app_logs
| SEARCH term(service_s, "auth")
| SEARCH match(message_t, "access denied")
| WHERE status_i >= 400
| KEEP id, service_s, message_t, status_i
| SORT status_i DESC
| LIMIT 20
```

- `term(field, value)` matches an indexed term; use it on keyword or ID fields for exact values.
- `match(field, value)` uses the field's text analyzer.
- `WHERE` evaluates a scalar predicate over retrieved values. `WHERE message_t = "access denied"` compares the
  returned value; it is not an analyzed full-text query.

SEARCH currently accepts `term` and `match` with literal or parameter values. Keep these stages before WHERE, EVAL,
KEEP or any other row operation. They reduce the input scanned by the pipeline. WHERE does not reduce the initial
input-row limit, even if it removes most rows afterward.

## Expressions and values

Use double quotes for strings and backticks for identifiers containing punctuation:

```text
FROM app_logs
| WHERE `@timestamp` IS NOT NULL AND status_i >= $minimum
| KEEP id, `@timestamp`, status_i
| LIMIT 20
```

Supply `minimum` in the request's `params` object. Parameters hold scalar JSON values: strings, numbers, booleans and
null. They cannot stand in for collection names, field names or whole stages. Values are bound without interpreting
them as query syntax.

Supported expressions include `+`, `-`, `*`, `/`, comparisons `=`, `!=`, `<`, `<=`, `>`, `>=`, boolean
`AND`, `OR`, `NOT`, parentheses and `IS NULL` / `IS NOT NULL`. Use `true`, `false` and `null` for those literals.

Missing fields and explicit null have the same meaning. Comparisons with null are unknown, so `field = null` does
not select nulls; use `field IS NULL`. WHERE keeps only true. Array values are not implicitly compared, grouped or
joined as separate rows; use `any_eq` for membership or [EXPAND](#expand-arrays) first.

Integer comparisons, counts and integer sums preserve int64 values. Integer overflow fails the query. Use explicit
conversion functions when inputs need a different type.

## Choose and transform fields

Use `KEEP` for explicit output columns and `EVAL` to calculate values:

```text
FROM app_logs
| EVAL service = lower(service_s), duration_seconds = duration_ms_i / 1000.0
| WHERE duration_seconds > 2
| KEEP id, service, duration_seconds
| SORT duration_seconds DESC
| LIMIT 20
```

Assignments in one EVAL stage run left to right, so later assignments can use earlier ones. KEEP accepts expressions
with aliases, such as `KEEP id, lower(service_s) AS service`. `DROP` removes named columns;
`RENAME old_name AS new_name` changes a name. Overlapping rename sources and targets are rejected.

PPL reads fields referenced by your query. There is no implicit `SELECT *`; list the desired output with KEEP.
Sort keys and intermediate expressions may otherwise contribute columns you did not intend to display.

## Aggregate events

Every aggregate needs an explicit alias:

```text
FROM app_logs
| SEARCH term(service_s, "checkout")
| STATS count() AS requests, count_distinct(user_s) AS users,
    avg(duration_ms_i) AS avg_ms, max(duration_ms_i) AS slowest_ms BY host_s
| SORT requests DESC
| LIMIT 20
```

| Aggregate | Meaning |
|---|---|
| `count()` | Number of rows. |
| `count(field)` | Number of non-null values. |
| `count_distinct(field)` | Exact number of distinct non-null scalar values. |
| `sum(field)` | Sum of non-null numeric values. |
| `avg(field)` | Average of non-null numeric values. |
| `min(field)`, `max(field)` | Smallest or largest non-null scalar value. |

`BY` accepts multiple fields or expressions, including an alias for a calculated grouping key. Null forms its own
group. With no BY and no matching rows, count returns 0 and sum, avg, min and max return null.

Filter aggregate results by placing WHERE after STATS:

```text
FROM app_logs
| SEARCH term(outcome_s, "failure")
| STATS count() AS failures BY user_s
| WHERE failures >= 5
| SORT failures DESC
| LIMIT 20
```

A LIMIT after STATS limits the number of groups returned; it never turns the aggregate into an estimate over a
truncated input. A LIMIT before STATS deliberately changes which rows contribute.

### Fixed time buckets

Convert timestamps to epoch milliseconds and group into five-minute buckets:

```text
FROM app_logs
| SEARCH term(service_s, "auth")
| STATS count() AS events BY bin(to_timestamp(timestamp_dt), 300000) AS bucket
| SORT bucket
| LIMIT 20
```

`to_timestamp` accepts fixed ISO timestamp strings or epoch milliseconds. `bin(value, width)` rounds down to the
start of a fixed numeric bucket. Widths are numbers, so use `300000`, not `5m`. Calendar buckets, timezone-aware
rounding, relative `NOW` expressions and rolling windows are not supported.

## Join and union

Join a collection of login events to an asset inventory:

```text
FROM app_logs AS e
| SEARCH term(e.action_s, "login")
| JOIN left [FROM assets AS a] ON e.host_s = a.host_s
| KEEP e.id AS event_id, e.host_s AS host, a.owner_s AS owner
| LIMIT 20
```

| Join | Result |
|---|---|
| `inner` | A row for every matching pair. |
| `left` | Matching pairs plus unmatched left rows with null right values. |
| `semi` | Each left row that has at least one match, without right columns. |
| `anti` | Left rows with no match, without right columns. |

When you give a source an alias, qualify its fields in every stage, including SEARCH.
ON accepts equality keys, joined with AND for a composite key. Null keys never match. Duplicate keys preserve all
matching pairs for inner and left joins. Qualify or rename fields to avoid output name collisions. Only equality
joins are supported; range and temporal joins are not.

For example, use an anti join to find hosts absent from the inventory:

```text
FROM app_logs AS e
| JOIN anti [FROM assets AS a] ON e.host_s = a.host_s
| KEEP e.id AS event_id, e.host_s AS unknown_host
| LIMIT 20
```

Use `UNION ALL` to append another pipeline's rows. This also lets you align different field names:

```text
FROM app_logs
| KEEP id, host_s AS host
| UNION ALL [FROM network_logs | KEEP id, hostname_s AS host]
| SORT host, id
| LIMIT 20
```

UNION ALL keeps duplicates. `FROM app_logs, audit_logs` is the shorter form when all inputs use the same fields and
processing steps. Sources inside joins and unions count toward the request's source and work limits.

## Parse JSON in log messages

Extract named, typed values from a field containing JSON text:

```text
FROM app_logs
| PARSE_JSON message_s AS p
    FIELDS (user string PATH "$.user", bytes int64 PATH "$.transfer.bytes")
    ON_ERROR keep
| WHERE p._error IS NULL
| STATS sum(p.bytes) AS bytes BY p.user
| SORT bytes DESC
| LIMIT 20
```

Declare each output's name, type and path. Types are `string`, `int64`, `double` and `bool`; paths support dotted
object members, not array indexes. `ON_ERROR fail` is the default. `ON_ERROR keep` retains a malformed row with null
extracted fields and `p._error` set to `"parse_json"`. Successful rows have a null error field.

## Expand arrays

EXPAND creates a separate row for each element of an array:

```text
FROM app_logs
| EXPAND tags_ss AS tag
| STATS count() AS events BY tag
| SORT events DESC
| LIMIT 20
```

This changes what count measures: it now counts expanded rows, including repeated values. For a membership filter
that retains one row per event, use `WHERE any_eq(tags_ss, "security")` instead. Expansion can exceed the work-row
budget even when the source fits it.

## Sort and page

`SORT` accepts several expressions, each with optional `ASC` or `DESC` and `NULLS FIRST` or `NULLS LAST`.
The default is ascending with nulls last. `LIMIT 20 OFFSET 20` skips the first 20 rows at that point in the pipeline.

Use a deterministic sort with a unique tie-breaker when paging. Across collections, an ID alone might not be unique;
add a source column as shown in [Multi-collection queries](multi-collection-queries.md#track-the-source-of-each-row).
Separate requests do not share a snapshot, so concurrent writes can still change page boundaries.

## Scalar functions

| Functions | Usage |
|---|---|
| `coalesce(a, b, ...)` | First non-null value. |
| `is_null(value)`, `is_not_null(value)` | Null checks. |
| `abs(number)` | Absolute value. |
| `lower(text)`, `upper(text)` | ASCII case conversion. |
| `length(value)` | UTF-8 byte length of a string, or number of array elements. |
| `contains(text, part)`, `starts_with(text, prefix)`, `ends_with(text, suffix)` | String predicates. |
| `concat(a, b)` | Concatenate strings. |
| `to_string(value)` | Convert a scalar to text. |
| `to_double(value)`, `try_double(value)` | Numeric conversion; `try_double` returns null on conversion failure. |
| `to_timestamp(value)` | Fixed timestamp to epoch milliseconds. |
| `bin(value, width)` | Start of a fixed numeric bucket. |
| `any_eq(array, value)` | Test whether an array contains an equal value. |

## Supported scope

PPL supports the stages shown here: FROM, SEARCH, WHERE, EVAL, KEEP, DROP, RENAME, STATS, SORT, LIMIT, JOIN, UNION ALL,
EXPAND and PARSE_JSON. It does not yet provide regex extraction, SIEM sequence detection, window functions, scheduled
alerts, streaming subscriptions, approximate distinct counts or arbitrary SQL subqueries.

Queries must fit the [input, intermediate and output limits](multi-collection-queries.md#limits-and-large-inputs).
A successful result is complete for the specified pipeline; exceeding a work limit fails the query rather than
silently sampling events. Start with selective indexed SEARCH stages, explicit fields and a final LIMIT.