| name | ppl-reference |
| description | Comprehensive PPL (Piped Processing Language) reference for OpenSearch with command syntax, functions, and examples for observability queries. |
| allowed-tools | ["Bash","curl"] |
PPL Language Reference
Overview
This is a comprehensive reference for the Piped Processing Language (PPL) used by OpenSearch. PPL queries follow a pipe-delimited syntax starting with source=<index> and chaining commands with |. This reference covers all commands, functions, API endpoints, and usage patterns needed to construct observability queries against trace and log indices.
Grammar sourced from the opensearch-project/sql repository's docs/user/ppl/ directory:
https://github.com/opensearch-project/sql
Connection Defaults
| Variable | Default | Description |
|---|
OPENSEARCH_ENDPOINT | https://localhost:9200 | OpenSearch base URL |
OPENSEARCH_USER | admin | OpenSearch username |
OPENSEARCH_PASSWORD | My_password_123!@# | OpenSearch password |
Field Name Escaping
Field names containing dots must be enclosed in backticks to avoid parsing errors:
`attributes.gen_ai.operation.name`
`attributes.gen_ai.usage.input_tokens`
`status.code`
`events.attributes.exception.type`
`@timestamp`
This is critical for OTel attribute fields which use dotted naming conventions.
API Endpoints
Query Endpoint
Execute a PPL query:
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | stats count() by serviceName"}'
Request body: {"query": "<ppl_query>"}
Explain Endpoint
Retrieve the query execution plan (useful for debugging and profiling):
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl/_explain" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | where `status.code` = 2 | stats count() by serviceName"}'
Commands
Core Query Commands
search / source
Start a query by specifying the data source index pattern.
Syntax: search source=<index-pattern> or source=<index-pattern>
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | head 10"}'
where
Filter results based on a condition.
Syntax: where <condition>
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | where `status.code` = 2 | head 10"}'
Supports: =, !=, <, >, <=, >=, AND, OR, NOT, LIKE, IN, BETWEEN, IS NULL, IS NOT NULL.
fields
Select specific fields to return.
Syntax: fields [+|-] <field-list>
Use + to include or - to exclude fields.
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | fields traceId, spanId, serviceName, durationInNanos | head 10"}'
stats
Aggregate data using statistical functions.
Syntax: stats <aggregation>... [by <field-list>]
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | stats count() as span_count, avg(durationInNanos) as avg_duration by serviceName"}'
Supports: count(), sum(), avg(), max(), min(), var_samp(), var_pop(), stddev_samp(), stddev_pop(), distinct_count(), percentile(), earliest(), latest(), list(), values(), first(), last().
sort
Order results by one or more fields.
Syntax: sort [+|-] <field> [, ...]
Use + for ascending (default), - for descending.
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | sort - durationInNanos | head 10"}'
head
Limit the number of results returned.
Syntax: head [N] (default N=10)
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | head 5"}'
eval
Compute new fields from expressions.
Syntax: eval <new-field> = <expression> [, ...]
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | eval duration_ms = durationInNanos / 1000000 | fields traceId, serviceName, duration_ms | sort - duration_ms | head 10"}'
dedup
Remove duplicate results based on field values.
Syntax: dedup [N] <field-list> [keepempty=<bool>] [consecutive=<bool>]
Caveat: dedup may throw a ClassCastException on fields with mixed types (e.g., a field that contains both strings and numbers across documents). Ensure the dedup field has a consistent type.
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | dedup serviceName | fields serviceName"}'
rename
Rename one or more fields.
Syntax: rename <old-field> AS <new-field> [, ...]
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | rename serviceName as service, durationInNanos as duration | fields traceId, service, duration | head 10"}'
top
Find the most frequent values for a field.
Syntax: top [N] <field> [by <group-field>]
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | top 5 serviceName"}'
rare
Find the least frequent values for a field.
Syntax: rare <field> [by <group-field>]
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | rare `attributes.gen_ai.operation.name`"}'
table
Display results in tabular format (alias for fields in some contexts).
Syntax: table <field-list>
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | where `status.code` = 2 | table traceId, spanId, serviceName, name | head 10"}'
Time-Series Commands
timechart
Aggregate data into time buckets for time-series visualization.
Syntax: timechart span=<interval> <aggregation>... [by <field>]
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | timechart span=5m count() as span_count by serviceName"}'
Rate functions for timechart: per_second(), per_minute(), per_hour(), per_day() — compute rate of an aggregation per time unit.
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | timechart span=1h per_minute(count()) as spans_per_min by serviceName"}'
chart
General charting command for aggregation over arbitrary fields.
Syntax: chart <aggregation>... by <field> [span(<field>, <interval>)]
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | chart avg(durationInNanos) by serviceName"}'
bin
Bucket numeric or date values into intervals.
Syntax: eval <new-field> = bin(<field>, <interval>) or used within stats ... by span(<field>, <interval>)
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | stats count() by span(durationInNanos, 1000000000)"}'
trendline
Calculate moving averages over sorted data.
Syntax: trendline [sort <field>] sma(<period>, <field>) [as <alias>]
SMA = Simple Moving Average.
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | trendline sort startTime sma(10, durationInNanos) as avg_duration | fields startTime, durationInNanos, avg_duration | head 50"}'
streamstats
Compute running (cumulative) statistics over ordered results.
Syntax: streamstats <aggregation>... [by <field>]
Caveat: streamstats processes all matching rows in memory. On large indices, this will fail with "insufficient resources" errors. Always add | head N before streamstats to limit data volume.
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | sort startTime | streamstats count() as running_count, sum(`attributes.gen_ai.usage.input_tokens`) as cumulative_tokens | fields startTime, running_count, cumulative_tokens | head 50"}'
eventstats
Add aggregation results as new fields to each row without collapsing rows (unlike stats).
Syntax: eventstats <aggregation>... [by <field>]
Caveat: eventstats processes all matching rows in memory. On large indices, this will fail with "insufficient resources" errors. Always add | head N before eventstats to limit data volume.
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | eventstats avg(durationInNanos) as avg_svc_duration by serviceName | eval deviation = durationInNanos - avg_svc_duration | fields traceId, serviceName, durationInNanos, avg_svc_duration, deviation | sort - deviation | head 20"}'
Parse/Extract Commands
parse
Extract fields from text using a regular expression with named groups.
Syntax: parse <field> '<regex-with-named-groups>'
Caveat: parse may silently drop extracted fields on some OpenSearch versions. If extracted fields are missing from results, use grok or rex as more reliable alternatives.
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=logs-otel-v1-* | parse body '\''(?P<level>\\w+): (?P<msg>.+)'\'' | fields level, msg | head 10"}'
grok
Extract fields using Grok patterns (predefined regex patterns).
Syntax: grok <field> '<grok-pattern>'
Caveat: grok processes all matching rows in memory. On large indices, this will fail with "insufficient resources" errors. Always add | head N before grok to limit data volume.
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=logs-otel-v1-* | grok body '\''%{LOGLEVEL:level} %{GREEDYDATA:message}'\'' | fields level, message | head 10"}'
rex
Extract fields using named capture groups (similar to parse but with Splunk-compatible syntax).
Syntax: rex field=<field> '<regex-with-named-groups>'
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=logs-otel-v1-* | rex field=body '\''(?<statuscode>\\d{3})'\'' | fields statuscode, body | head 10"}'
regex
Filter results using a regular expression match on a field.
Syntax: <field> = regex '<pattern>' (used within where)
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=logs-otel-v1-* | where body like '\''%error%'\'' | fields traceId, body, severityText | head 10"}'
patterns
Auto-discover log patterns by clustering similar log messages.
Syntax: patterns <field> [pattern='<regex>'] [new_field=<name>]
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=logs-otel-v1-* | patterns body | fields body, patterns_field | head 20"}'
spath
Extract values from structured data (JSON, XML) using path expressions.
Syntax: spath input=<field> [path=<path>] [output=<field>]
Note: Verify the target field exists in your index before using spath. Run describe <index-pattern> first to confirm the field name. The example below uses a representative field; adjust to match your actual schema.
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | where isnotnull(`attributes.gen_ai.tool.name`) | spath input=`attributes.gen_ai.tool.name` | head 10"}'
Join/Lookup Commands
join
Join results from two indices.
Syntax: join left=<alias> right=<alias> ON <condition> <right-source> or join <right-source> on <field>
Types: inner, left, right, cross.
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | join left=s right=l ON s.traceId = l.traceId logs-otel-v1-* | fields s.spanId, s.name, l.severityText, l.body | head 10"}'
lookup
Enrich results by looking up values from another index.
Syntax: lookup <lookup-index> <match-field> [AS <alias>] [OUTPUT <field-list>]
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | lookup otel-v2-apm-service-map serviceName AS `sourceNode` | fields serviceName, `targetNode`, durationInNanos | head 10"}'
Note: The service map index (otel-v2-apm-service-map) uses nested fields sourceNode and targetNode, not serviceName. Match accordingly when joining or looking up against this index.
graphlookup
Perform graph traversal lookups for hierarchical or connected data.
Syntax: graphlookup <index> connectFromField=<field> connectToField=<field> [maxDepth=<N>] [as <alias>]
Caveat: graphlookup has limited support in OpenSearch 3.x PPL and may not work as expected. Test carefully against your OpenSearch version before relying on this command in production queries.
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v2-apm-service-map | graphlookup otel-v2-apm-service-map connectFromField=`destination.domain` connectToField=serviceName maxDepth=3 as dependencies | head 10"}'
subquery
Use a nested query as a data source or filter.
Syntax: where <field> IN [ source=<index> | ... | fields <field> ]
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | where traceId IN [ source=otel-v1-apm-span-* | where `status.code` = 2 | fields traceId ] | fields traceId, spanId, serviceName, name | head 20"}'
append
Append results from another query to the current result set.
Syntax: append [ source=<index> | ... ]
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | stats count() as cnt by serviceName | append [ source=logs-otel-v1-* | stats count() as cnt by `resource.attributes.service.name` ] | head 20"}'
appendcol
Append columns from another query to the current result set.
Syntax: appendcol [ <commands> ]
Caveat: source= is not valid inside appendcol [] subqueries. The subquery inside appendcol operates on the current result set, not a new index. Use append followed by reshaping if you need to bring in data from another index.
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | stats count() as span_count | appendcol [ source=logs-otel-v1-* | stats count() as log_count ]"}'
appendpipe
Append the results of a sub-pipeline to the current results.
Syntax: appendpipe [ <commands> ]
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | stats count() as cnt by serviceName | appendpipe [ stats sum(cnt) as total ]"}'
Transform Commands
fillnull
Replace null values with a specified value.
Syntax: fillnull [with <value>] [<field-list>]
Caveat: Backtick-quoted field names are not supported in the fillnull field list. Use eval to rename dotted fields to simple names before applying fillnull, or apply fillnull without a field list to fill all null fields.
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | fillnull with 0 `attributes.gen_ai.usage.input_tokens`, `attributes.gen_ai.usage.output_tokens` | fields traceId, `attributes.gen_ai.usage.input_tokens`, `attributes.gen_ai.usage.output_tokens` | head 10"}'
flatten
Flatten nested fields into top-level fields.
Syntax: flatten <field>
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | flatten events | head 10"}'
expand
Expand multi-value or array fields into separate rows.
Syntax: expand <field>
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | expand events | fields traceId, spanId, events | head 20"}'
transpose
Pivot rows into columns.
Syntax: transpose [<N>] [include_null=<bool>]
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | stats count() as cnt by serviceName | transpose"}'
convert
Convert field types (e.g., string to number).
Syntax: convert <function>(<field>) [as <alias>]
Functions: auto(), num(), ip(), ctime(), dur2sec(), mktime(), mstime(), rmcomma(), rmunit(), memk(), none().
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | eval duration_str = CAST(durationInNanos AS STRING) | convert num(duration_str) as duration_num | fields traceId, duration_num | head 10"}'
replace
Replace values in a field using the replace() string function inside eval.
Syntax: eval <field> = replace(<field>, '<old>', '<new>')
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=logs-otel-v1-* | eval severityText = replace(severityText, '\''ERROR'\'', '\''ERR'\'') | fields severityText, body | head 10"}'
reverse
Reverse the order of results.
Syntax: reverse
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | sort startTime | head 20 | reverse"}'
Multi-Value Commands
mvexpand
Expand a multi-value field into separate rows (one row per value).
Syntax: mvexpand <field>
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | mvexpand events | fields traceId, spanId, events | head 20"}'
mvcombine
Combine multiple rows with the same key into a single row with a multi-value field.
Syntax: mvcombine <field>
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | fields traceId, serviceName | mvcombine serviceName | head 10"}'
nomv
Convert a multi-value field to a single-value field (takes the first value or joins with a delimiter).
Syntax: nomv <field>
Caveat: nomv only works on string arrays, not nested object arrays. If the field contains nested objects (e.g., events with sub-fields), nomv will fail or produce unexpected results. Use flatten or expand for nested object arrays instead.
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | nomv events | fields traceId, events | head 10"}'
Aggregation/Totals Commands
addcoltotals
Add a summary row at the bottom with column totals.
Syntax: addcoltotals [<field-list>]
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | stats count() as cnt by serviceName | addcoltotals"}'
addtotals
Add a new field to each row containing the sum of specified numeric fields.
Syntax: addtotals [row=<bool>] [col=<bool>] [<field-list>]
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | stats sum(`attributes.gen_ai.usage.input_tokens`) as input_tok, sum(`attributes.gen_ai.usage.output_tokens`) as output_tok by serviceName | addtotals"}'
ML Commands
ad
Anomaly detection — identify anomalous values in numeric fields using built-in ML algorithms.
Syntax: ad [time_field=<field>] [number_of_trees=<N>] [shingle_size=<N>] [time_zone=<tz>]
Note: The ad command does not take a positional field argument. It auto-detects the input field(s) from the preceding stats or eval output in the pipeline.
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | where durationInNanos > 0 | ad time_field=startTime number_of_trees=100 time_zone=\"UTC\" | head 50"}'
kmeans
Cluster data points using the k-means algorithm.
Syntax: kmeans [centroids=<N>] [iterations=<N>] [distance_type=<type>]
Note: The kmeans command does not take positional field arguments. It operates on all numeric fields from the preceding pipeline output. Use fields or eval before kmeans to control which numeric fields are used for clustering.
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | where durationInNanos > 0 | fields traceId, serviceName, durationInNanos | kmeans centroids=3 | fields traceId, serviceName, durationInNanos, ClusterID | head 30"}'
ml
General ML command for running machine learning algorithms on query results.
Syntax: ml action=<algorithm> [parameters...]
Supported algorithms include: kmeans, ad (anomaly detection).
Note: ml action=rcf is not a valid action in OpenSearch 3.x PPL. Random Cut Forest anomaly detection is accessed via the ad command directly (see the ad section above), not through ml action=rcf.
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | where durationInNanos > 0 | ml action=kmeans centroids=3 | head 50"}'
System Commands
describe
Inspect the index mapping and field types for an index.
Syntax: describe <index-pattern>
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "describe otel-v1-apm-span-*"}'
explain
Show the query execution plan (used via the _explain API endpoint rather than as an inline command).
Syntax: Use the /_plugins/_ppl/_explain endpoint with the query body.
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl/_explain" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | where `status.code` = 2 | stats count() by serviceName"}'
showdatasources
List all configured data sources available for PPL queries.
Syntax: show datasources
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "show datasources"}'
multisearch
Execute multiple PPL queries in a single request. Each query is independent.
Syntax: Use the /_plugins/_ppl endpoint with multiple queries separated by newlines (NDJSON format), or execute sequentially.
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | stats count() as total_spans"}'
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=logs-otel-v1-* | stats count() as total_logs"}'
Display Commands
fieldformat
Format the display of a field's values without changing the underlying data.
Syntax: fieldformat <field> = <format-expression>
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | eval duration_ms = durationInNanos / 1000000 | fieldformat duration_ms = CONCAT(CAST(duration_ms AS STRING), '\'' ms'\'') | fields traceId, serviceName, duration_ms | head 10"}'
Span Expression Syntax
The span() function buckets numeric or datetime values into intervals. Used with stats, timechart, and chart.
Syntax: span(<field>, <interval>)
Supported Time Units
| Unit | Description | Example |
|---|
ms | Milliseconds | span(startTime, 500ms) |
s | Seconds | span(startTime, 30s) |
m | Minutes | span(startTime, 5m) |
h | Hours | span(startTime, 1h) |
d | Days | span(startTime, 1d) |
w | Weeks | span(startTime, 1w) |
M | Months | span(startTime, 1M) |
q | Quarters | span(startTime, 1q) |
y | Years | span(startTime, 1y) |
Numeric Spans
For numeric fields, the interval is a plain number:
stats count() by span(durationInNanos, 1000000000)
Example
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | stats count() as span_count, avg(durationInNanos) as avg_duration by span(startTime, 1h)"}'
Timechart Rate Functions
Rate functions normalize aggregation values to a per-time-unit rate within timechart:
| Function | Description |
|---|
per_second(<agg>) | Aggregation value per second |
per_minute(<agg>) | Aggregation value per minute |
per_hour(<agg>) | Aggregation value per hour |
per_day(<agg>) | Aggregation value per day |
Example
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | timechart span=5m per_second(count()) as requests_per_sec"}'
Functions
Aggregation Functions
Used with stats, eventstats, streamstats, timechart, and chart commands.
| Function | Syntax | Description |
|---|
COUNT | count() | Count of events |
SUM | sum(field) | Sum of numeric values |
AVG | avg(field) | Arithmetic mean |
MAX | max(field) | Maximum value |
MIN | min(field) | Minimum value |
VAR_SAMP | var_samp(field) | Sample variance |
VAR_POP | var_pop(field) | Population variance |
STDDEV_SAMP | stddev_samp(field) | Sample standard deviation |
STDDEV_POP | stddev_pop(field) | Population standard deviation |
DISTINCT_COUNT | distinct_count(field) | Count of distinct values |
PERCENTILE | percentile(field, pct) | Value at the given percentile |
EARLIEST | earliest(field) | Earliest (first chronological) value |
LATEST | latest(field) | Latest (most recent) value |
LIST | list(field) | All values as a list |
VALUES | values(field) | Distinct values as a list |
FIRST | first(field) | First value encountered |
LAST | last(field) |
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | stats count() as total, avg(durationInNanos) as avg_ns, percentile(durationInNanos, 95) as p95_ns, distinct_count(serviceName) as services"}'
Collection Functions
Functions for working with multi-value fields and arrays.
| Function | Syntax | Description |
|---|
ARRAY | array(val1, val2, ...) | Create an array from values |
SPLIT | split(field, delimiter) | Split a string into an array |
MVJOIN | mvjoin(field, delimiter) | Join multi-value field into a string |
MVCOUNT | mvcount(field) | Count of values in a multi-value field |
MVINDEX | mvindex(field, index) | Get value at index from multi-value field |
MVFIRST | mvfirst(field) | First value of a multi-value field |
MVLAST | mvlast(field) | Last value of a multi-value field |
MVAPPEND | mvappend(field1, field2) | Append two multi-value fields |
MVDEDUP | mvdedup(field) | Remove duplicates from multi-value field |
MVSORT | mvsort(field) | Sort values in a multi-value field |
MVZIP | mvzip(field1, field2, delim) | Zip two multi-value fields together |
MVRANGE | mvrange(start, end, step) | Generate a range of numeric values |
MVFILTER | mvfilter(expression) | Filter values in a multi-value field |
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | eval tokens = array(`attributes.gen_ai.usage.input_tokens`, `attributes.gen_ai.usage.output_tokens`) | fields traceId, tokens | head 10"}'
Condition Functions
Functions for conditional logic and null handling.
| Function | Syntax | Description |
|---|
ISNULL | isnull(field) | Returns true if field is null |
ISNOTNULL | isnotnull(field) | Returns true if field is not null |
IF | if(cond, true_val, false_val) | Conditional expression |
IFNULL | ifnull(field, default) | Return default if field is null |
NULLIF | nullif(val1, val2) | Return null if val1 equals val2 |
CASE | case(cond1, val1, cond2, val2, ..., else_val) | Multi-branch conditional |
COALESCE | coalesce(val1, val2, ...) | First non-null value |
LIKE | field LIKE 'pattern' | Wildcard pattern match (% and _) |
IN | field IN (val1, val2, ...) | Check membership in a set |
BETWEEN | field BETWEEN val1 AND val2 | Range check (inclusive) |
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | eval status_label = case(`status.code` = 0, '\''UNSET'\'', `status.code` = 1, '\''OK'\'', `status.code` = 2, '\''ERROR'\'') | stats count() by status_label"}'
Conversion Functions
Functions for type casting and conversion.
| Function | Syntax | Description |
|---|
CAST | cast(field AS type) | Cast to a specified type (STRING, INT, LONG, FLOAT, DOUBLE, BOOLEAN, DATE, TIMESTAMP) |
TOSTRING | tostring(field) | Convert to string |
TONUMBER | tonumber(field) | Convert to number |
TOINT | toint(field) | Convert to integer |
TOLONG | tolong(field) | Convert to long |
TOFLOAT | tofloat(field) | Convert to float |
TODOUBLE | todouble(field) | Convert to double |
TOBOOLEAN | toboolean(field) | Convert to boolean |
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | eval duration_ms = CAST(durationInNanos AS DOUBLE) / 1000000.0 | fields traceId, serviceName, duration_ms | sort - duration_ms | head 10"}'
Cryptographic Functions
Functions for computing hash digests.
| Function | Syntax | Description |
|---|
MD5 | md5(field) | MD5 hash of the value |
SHA1 | sha1(field) | SHA-1 hash of the value |
SHA2 | sha2(field, numBits) | SHA-2 hash (numBits: 224, 256, 384, 512) |
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | eval trace_hash = md5(traceId) | fields traceId, trace_hash | head 5"}'
Datetime Functions
Functions for date and time manipulation.
| Function | Syntax | Description |
|---|
NOW | now() | Current timestamp |
CURDATE | curdate() | Current date |
CURTIME | curtime() | Current time |
DATE_FORMAT | date_format(date, fmt) | Format a date (%Y-%m-%d %H:%i:%s) |
DATE_ADD | date_add(date, INTERVAL n unit) | Add interval to date |
DATE_SUB | date_sub(date, INTERVAL n unit) | Subtract interval from date |
DATEDIFF | datediff(date1, date2) | Difference in days between two dates |
DAY | day(date) | Day of month (1–31) |
MONTH | month(date) | Month (1–12) |
YEAR | year(date) | Year |
HOUR | hour(time) | Hour (0–23) |
MINUTE | minute(time) | Minute (0–59) |
SECOND | second(time) | Second (0–59) |
DAYOFWEEK | dayofweek(date) | Day of week (1=Sun, 7=Sat) |
DAYOFYEAR | dayofyear(date) | Day of year (1–366) |
WEEK | week(date) | Week number of the year |
UNIX_TIMESTAMP | unix_timestamp(date) |
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | where startTime > DATE_SUB(NOW(), INTERVAL 1 HOUR) | stats count() as recent_spans by serviceName"}'
Expressions
Operators for arithmetic, comparison, and logical expressions used in eval, where, and other commands.
Arithmetic Operators
| Operator | Description | Example |
|---|
+ | Addition | eval total = input_tokens + output_tokens |
- | Subtraction | eval gap = endTime - startTime |
* | Multiplication | eval cost = tokens * price_per_token |
/ | Division | eval duration_ms = durationInNanos / 1000000 |
Comparison Operators
| Operator | Description |
|---|
= | Equal to |
!= or <> | Not equal to |
< | Less than |
> | Greater than |
<= | Less than or equal to |
>= | Greater than or equal to |
Logical Operators
| Operator | Description |
|---|
AND | Logical AND |
OR | Logical OR |
NOT | Logical NOT |
XOR | Logical exclusive OR |
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | eval duration_ms = durationInNanos / 1000000, total_tokens = `attributes.gen_ai.usage.input_tokens` + `attributes.gen_ai.usage.output_tokens` | where duration_ms > 1000 AND total_tokens > 0 | fields traceId, serviceName, duration_ms, total_tokens | head 10"}'
IP Functions
Functions for IP address operations.
| Function | Syntax | Description |
|---|
CIDRMATCH | cidrmatch(ip_field, 'cidr') | Check if IP is within a CIDR range |
GEOIP | geoip(ip_field) | Geo-locate an IP address (returns country, region, city, lat/lon) |
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | where isnotnull(`attributes.net.peer.ip`) | where cidrmatch(`attributes.net.peer.ip`, '\''10.0.0.0/8'\'') | fields traceId, `attributes.net.peer.ip`, serviceName | head 10"}'
JSON Functions
Functions for working with JSON data.
| Function | Syntax | Description |
|---|
JSON_EXTRACT | json_extract(field, path) | Extract value at JSON path |
JSON_KEYS | json_keys(field) | Get all keys from a JSON object |
JSON_VALID | json_valid(field) | Check if value is valid JSON |
JSON_ARRAY | json_array(val1, val2, ...) | Create a JSON array |
JSON_OBJECT | json_object(key1, val1, ...) | Create a JSON object |
JSON_ARRAY_LENGTH | json_array_length(field) | Length of a JSON array |
JSON_EXTRACT_PATH_TEXT | json_extract_path_text(field, path) | Extract value as text from JSON path |
TO_JSON_STRING | to_json_string(field) | Convert value to JSON string |
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | where json_valid(`attributes.gen_ai.tool.call.arguments`) | eval tool_args = json_extract(`attributes.gen_ai.tool.call.arguments`, '\''$'\'') | fields traceId, `attributes.gen_ai.tool.name`, tool_args | head 10"}'
Math Functions
Functions for mathematical operations.
| Function | Syntax | Description |
|---|
ABS | abs(val) | Absolute value |
CEIL | ceil(val) | Round up to nearest integer |
FLOOR | floor(val) | Round down to nearest integer |
ROUND | round(val [, decimals]) | Round to N decimal places |
SQRT | sqrt(val) | Square root |
POW | pow(base, exp) | Exponentiation |
MOD | mod(a, b) | Modulo (remainder) |
LOG | log(val) | Natural logarithm |
LOG2 | log2(val) | Base-2 logarithm |
LOG10 | log10(val) | Base-10 logarithm |
LN | ln(val) | Natural logarithm (alias for LOG) |
EXP | exp(val) | e raised to the power of val |
SIGN | sign(val) | Sign of value (-1, 0, 1) |
TRUNCATE | truncate(val, decimals) | Truncate to N decimal places |
PI | pi() | Value of π |
E | e() | Value of Euler's number |
RAND | rand([seed]) | Random float between 0 and 1 |
ACOS |
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | eval duration_ms = round(durationInNanos / 1000000.0, 2) | where duration_ms > 0 | fields traceId, serviceName, duration_ms | sort - duration_ms | head 10"}'
Relevance Functions
Full-text search functions for relevance-based querying.
| Function | Syntax | Description |
|---|
MATCH | match(field, query) | Full-text match on a single field |
MATCH_PHRASE | match_phrase(field, phrase) | Exact phrase match |
MATCH_BOOL_PREFIX | match_bool_prefix(field, query) | Boolean prefix match |
MATCH_PHRASE_PREFIX | match_phrase_prefix(field, prefix) | Phrase prefix match |
MULTI_MATCH | multi_match([field1, field2], query) | Match across multiple fields |
QUERY_STRING | query_string([field1, field2], query) | Lucene query string syntax |
SIMPLE_QUERY_STRING | simple_query_string([field1, field2], query) | Simplified query string |
HIGHLIGHT | highlight(field) | Return highlighted matching fragments |
SCORE | score(relevance_func) | Return relevance score |
SCOREQUERY | scorequery(relevance_func) | Filter by relevance score |
MATCH_QUERY | match_query(field, query) | Alias for MATCH |
WILDCARD_QUERY | wildcard_query(field, pattern) | Wildcard pattern match (* and ?) |
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=logs-otel-v1-* | where match(body, '\''timeout error'\'') | fields traceId, severityText, body | head 10"}'
Statistical Functions
Functions for computing statistical correlations and covariances.
| Function | Syntax | Description |
|---|
COVAR_POP | covar_pop(field1, field2) | Population covariance |
COVAR_SAMP | covar_samp(field1, field2) | Sample covariance |
Note: corr() is not a recognized stats aggregation function in OpenSearch 3.x PPL. To approximate Pearson correlation, use eval with manual calculation or compute covariance and standard deviations separately. The covar_samp and covar_pop functions are supported.
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | where `attributes.gen_ai.usage.input_tokens` > 0 | stats covar_samp(`attributes.gen_ai.usage.input_tokens`, durationInNanos) as token_duration_covar"}'
String Functions
Functions for string manipulation.
| Function | Syntax | Description |
|---|
CONCAT | concat(str1, str2, ...) | Concatenate strings |
LENGTH | length(str) | String length in bytes |
LOWER | lower(str) | Convert to lowercase |
UPPER | upper(str) | Convert to uppercase |
TRIM | trim(str) | Remove leading/trailing whitespace |
LTRIM | ltrim(str) | Remove leading whitespace |
RTRIM | rtrim(str) | Remove trailing whitespace |
SUBSTRING | substring(str, start [, len]) | Extract substring |
LEFT | left(str, len) | Leftmost N characters |
RIGHT | right(str, len) | Rightmost N characters |
REPLACE | replace(str, from, to) | Replace occurrences |
REVERSE | reverse(str) | Reverse a string |
LOCATE | locate(substr, str [, pos]) | Position of substring |
POSITION | position(substr IN str) | Position of substring |
ASCII | ascii(str) | ASCII code of first character |
CHAR_LENGTH | char_length(str) | Character count |
CHARACTER_LENGTH | character_length(str) |
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=logs-otel-v1-* | eval body_lower = lower(body) | where body_lower like '\''%exception%'\'' | eval short_body = left(body, 200) | fields traceId, severityText, short_body | head 10"}'
System Functions
| Function | Syntax | Description |
|---|
TYPEOF | typeof(field) | Returns the data type of a field value |
curl -sk -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" \
-X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
-H 'Content-Type: application/json' \
-d '{"query": "source=otel-v1-apm-span-* | eval type_of_duration = typeof(durationInNanos) | fields traceId, durationInNanos, type_of_duration | head 5"}'
Grammar Source
This PPL reference is sourced from the opensearch-project/sql repository's docs/user/ppl/ directory.
Repository: https://github.com/opensearch-project/sql
The PPL grammar is maintained as part of the OpenSearch SQL plugin. For the latest syntax additions and changes, consult the repository documentation directly.
References
- PPL Language Reference — Official PPL syntax documentation. Fetch this if queries fail due to OpenSearch version differences or new syntax.
