/amazon-opensearch-service

PPL language reference (observability)

The AWS MCP server is recommended for executing these commands but is not required — all steps use standard awscurl / AWS CLI syntax.

Shared PPL reference for the log-analytics and trace-analytics capabilities. Queries follow pipe-delimited syntax: source=<index> | command1 | command2 .... Grammar is sourced from opensearch-project/sql under docs/user/ppl/.

Loaded from: log-analytics-guide.md, trace-analytics-trace-queries.md.

Query discipline (MUST follow)

1. Unknown command or function — you MUST consult the upstream grammar before answering. If a command, function, or parameter is not in this reference, you MUST fetch the raw markdown from opensearch-project/sql (URLs below). You MUST NOT invent PPL syntax and you MUST NOT claim a command does not exist without checking first, because OpenSearch PPL includes many commands (for example graphLookup, explain, append, join) that do not exist in other query systems.
2. You MUST verify emitted queries when a cluster endpoint is reachable. When the AOS domain or AOSS collection endpoint is available, every emitted PPL query MUST be validated with a best-effort cascade before you return it:
   1. Execute it against _plugins/_ppl and capture the row count and any error.
   2. If it succeeds but returns 0 rows, run _plugins/_ppl/_explain to confirm the plan parses and resolves field references, then surface the empty-result observation.
   3. If _plugins/_ppl errors, fix the query (consult the upstream grammar as needed) and re-validate. When no endpoint is reachable, you MUST state explicitly that the query is unverified, because an untested PPL query can silently reference a non-existent field or use version-specific syntax.

Field name escaping

Dotted field names MUST be backtick-quoted, because an unquoted dot is parsed as a path separator and fails: `attributes.gen_ai.operation.name`, `status.code`, `resource.attributes.service.name`.

Field names beginning with a special character (e.g. the @-prefixed `@timestamp`) must also be backtick-quoted, because the leading character is not a valid bare identifier.

API endpoints

Query (AOS uses --service es; AOSS uses --service aoss):

The endpoint URL MUST use HTTPS (not HTTP). awscurl will not upgrade an HTTP URL to HTTPS, so verify $OPENSEARCH_ENDPOINT starts with https:// before executing any command below, because an unencrypted data-plane request exposes the query and results in transit.

Source the SigV4 signing credentials from an IAM role (instance profile, ECS task role, or SSO session) rather than static access keys, because static access keys are a standing exfiltration risk.

awscurl --service es --region $AWS_REGION \
  -X POST "$OPENSEARCH_ENDPOINT/_plugins/_ppl" \
  -H 'Content-Type: application/json' \
  -d '{"query": "source=otel-v1-apm-span-* | stats count() by serviceName"}'

Explain (query-plan debugging):

awscurl --service es --region $AWS_REGION \
  -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"}'

_explain accepts an optional mode (standard / simple / cost / extended). simple, cost, and extended require the v3 engine (plugins.calcite.enabled=true); standard works on v2 and v3. Engine requirements are version-specific — verify whether the v3 engine is available and enabled on the target domain/collection by checking GET _cluster/settings for plugins.calcite.enabled, or consult the upstream opensearch-project/sql docs rather than assuming these constraints are fixed.

Core commands

Time-series commands

Span time units: ms, s, m, h, d, w, M, q, y (bin also accepts us, cs, ds and longhand forms). Timechart rate functions: per_second(), per_minute(), per_hour(), per_day().

Parse / extract commands

Join / lookup / set commands

Cross-index join reliability varies by engine version, so you MUST test the join on the target domain/collection (per the query-discipline cascade above) rather than assuming it works. If it returns 0 rows unexpectedly, fall back to separate queries correlated by traceId.

Transform commands

Function families

• Aggregation: count(), sum(f), avg(f), max(f)/min(f), distinct_count(f), percentile(f, pct), var_samp(f)/stddev_samp(f), earliest(f)/latest(f), values(f).
• Statistical (eval-context, scalar): MAX(x, y, ...), MIN(x, y, ...) — these return a single value across their arguments and do NOT aggregate across rows, because they are scalar; use aggregate max(field)/min(field) inside stats instead.
• Condition: isnull(f)/isnotnull(f), if(cond, t, f), case(c1, v1, ..., else), coalesce(v1, v2, ...), like/in/between.
• Conversion: cast(f AS type), tostring(), toint(), tolong(), tofloat(), todouble() (types: STRING, INT, LONG, FLOAT, DOUBLE, BOOLEAN, DATE, TIMESTAMP).
• Datetime: now(), date_format(date, fmt), date_add(date, INTERVAL n unit), date_sub(date, INTERVAL n unit), datediff(d1, d2), day()/month()/year()/hour()/minute()/second().
• String: concat(), length()/lower()/upper()/trim(), substring(s, start, len), replace(s, from, to), regexp_extract(s, pat, grp), regexp_replace(s, pat, repl).
• Relevance: match(field, query), match_phrase(field, phrase), multi_match([f1, f2], query), query_string([f1, f2], query), wildcard_query(field, pattern).
• Math: abs(), ceil(), floor(), round(val, decimals), sqrt(), pow(), mod(), log(), log10(), exp().
• Collection (multivalue): array(...), array_length(arr), mvjoin(arr, sep), mvfilter(expr), mvindex(arr, start [, end]), mvappend(...).
• JSON (path notation <key>{<idx>}...; {} = all elements): json(value), json_extract(json, path), json_array(...), json_object(...), json_keys(json).
• IP: cidrmatch(ip, cidr), geoip(ip).
• Cryptographic: md5(str), sha1(str), sha2(str, bits) (hex-encoded STRING digests).
• System: typeof(expr).

Operators

Arithmetic: +, -, *, /, %; use parentheses for precedence. Division behavior depends on the cluster setting plugins.ppl.syntax.legacy.preferred: when true integer/integer is truncated; when false operands are promoted to floating point. Do not assume a default — read the effective value on the target domain/collection with GET _cluster/settings?include_defaults=true (inspect defaults.plugins.ppl.syntax.legacy.preferred unless overridden under persistent/transient) before relying on the division semantics. Division by zero returns NULL, not an error. Modulo (%) is integer-only and raises a type error on floats, because the operator has no float overload.

ML commands

ml action=rcf support varies by cluster version — prefer the ad command directly, and if you need ml action=rcf, verify it against the target domain/collection (test the command) or the upstream opensearch-project/sql docs for that version rather than assuming a fixed constraint.

System / inspection commands

Looking up PPL documentation

When a command/function is missing here, a query fails with a syntax error, or the user's cluster version may differ, you MUST fetch the canonical grammar from opensearch-project/sql:

• Commands: https://raw.githubusercontent.com/opensearch-project/sql/main/docs/user/ppl/cmd/<command>.md
• Functions: https://raw.githubusercontent.com/opensearch-project/sql/main/docs/user/ppl/functions/<category>.md (categories: aggregations, collection, condition, conversion, cryptographic, datetime, expressions, ip, json, math, relevance, statistical, string, system)

Prefer the opensearch-project/sql source over opensearch.org documentation pages, because the docs site MAY trail the SQL repository by one or more releases.

Security Considerations

• Authenticate every query with SigV4 (IAM auth), never open access. PPL queries hit the data plane (_plugins/_ppl), so requests MUST be SigV4-signed — AOS uses --service es, AOSS uses --service aoss. Do NOT rely on public/unauthenticated endpoints, because an open data plane exposes all indexed data.
• PPL results can expose sensitive data. A query returns whatever fields the caller's index-level permissions allow. Callers MUST have appropriately scoped access via FGAC (AOS domains) or the collection's data access policy (AOSS), because a broad grant lets a query surface PII or other restricted fields.
• Enable audit logging. Turn on CloudTrail for the management-plane API calls and OpenSearch audit logs for data-plane access so you can attribute who ran which queries, because an unaudited data plane makes misuse undetectable.
• Do not log full query results to CloudWatch if they may contain PII unless the log group is encrypted with a customer-managed KMS key, because query output can carry sensitive document fields into logs.
• Rate-limit, time-bound, and monitor the PPL endpoint. Enforce rate limits on _plugins/_ppl traffic (via a fronting API gateway/proxy or AWS WAF rate-based rules) and set query timeouts so a runaway aggregation or join cannot exhaust cluster resources; alarm on CloudWatch _plugins/_ppl call volume and error rates to catch abuse. This matters most for agentic search, where each query invokes a Bedrock model — an unthrottled path there drives both resource exhaustion and unbounded per-call cost.