Query Language Guide

OpenSearch UI supports four query languages, each suited to different tasks. This guide covers the syntax, strengths, and practical examples for each language, along with guidance on when to use which.

Language comparison

Language	Best For	Where to Use	Syntax Style
PPL	Log analysis, aggregations, data exploration	Observability → Logs, Discover	Pipe-based (like Splunk SPL)
SQL	Familiar relational queries, reporting	SQL Workbench, API	Standard SQL
DQL	Quick filtering in Discover and Dashboards	Search bar (default)	Key-value pairs
Lucene	Full-text search, regex, wildcards	Search bar (toggle from DQL)	Lucene query syntax

PPL (Piped Processing Language)

PPL uses a pipe-based syntax where each command processes the output of the previous one. It's the most powerful language for log analysis and data exploration in OpenSearch UI.

Basic syntax

source = <index-name>
| <command1>
| <command2>
| ...

Filtering and searching

source = web-logs
| where status_code >= 500
| where timestamp > '2025-06-01T00:00:00'
| fields timestamp, status_code, request_path, response_time

Aggregations

source = web-logs
| where status_code >= 400
| stats count() as error_count, avg(response_time) as avg_latency by service_name
| sort - error_count
| head 20

Time-based analysis

source = web-logs
| where timestamp > '2025-06-01' AND timestamp < '2025-06-08'
| stats count() as requests by span(timestamp, 1h) as hour
| sort hour

Dedup and rare commands

# Find unique error messages per service
source = app-logs
| where level = 'ERROR'
| dedup service_name, error_message
| fields service_name, error_message, timestamp
 
# Find rare status codes
source = web-logs
| rare status_code by service_name

String functions

source = app-logs
| where like(message, '%timeout%')
| eval short_msg = substring(message, 1, 100)
| fields timestamp, service_name, short_msg

Joining data (experimental)

source = orders
| join left = o right = c ON o.customer_id = c.id
  customers
| fields o.order_id, c.name, o.total

SQL

OpenSearch supports a subset of SQL for querying indices. It's a good fit if your team is already comfortable with relational database queries.

Basic queries

SELECT service_name, status_code, response_time, timestamp
FROM web-logs
WHERE status_code >= 500
  AND timestamp > '2025-06-01'
ORDER BY timestamp DESC
LIMIT 50

Aggregations

SELECT service_name,
       COUNT(*) as total_requests,
       AVG(response_time) as avg_latency,
       MAX(response_time) as max_latency
FROM web-logs
WHERE timestamp > '2025-06-01'
GROUP BY service_name
HAVING COUNT(*) > 100
ORDER BY avg_latency DESC

Subqueries

SELECT service_name, error_count
FROM (
  SELECT service_name, COUNT(*) as error_count
  FROM web-logs
  WHERE status_code >= 500
  GROUP BY service_name
) AS errors
WHERE error_count > 10
ORDER BY error_count DESC

Using SQL via the API

curl -X POST "https://your-domain/_plugins/_sql" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT service_name, COUNT(*) FROM web-logs GROUP BY service_name"
  }'

SQL limitations in OpenSearch

No INSERT, UPDATE, or DELETE — OpenSearch SQL is read-only
JOIN support is limited to certain join types
Not all SQL functions are available — check the OpenSearch SQL reference for supported functions
Nested field access uses dot notation: request.headers.host

DQL (Dashboards Query Language)

DQL is the default query language in the Discover and Dashboard search bars. It's designed for quick, simple filtering.

Basic syntax

field_name: value

Examples

# Exact match
status_code: 500
 
# Multiple conditions
status_code: 500 and service_name: "payment-service"
 
# OR conditions
status_code: 500 or status_code: 502
 
# Nested fields
request.headers.host: "api.example.com"
 
# Range queries
response_time > 1000
 
# Wildcard
service_name: payment*
 
# NOT
not status_code: 200
 
# Combining operators
(status_code: 500 or status_code: 502) and region: "us-east-1"

DQL vs Lucene toggle

In the search bar, you'll see a DQL button on the left side. Click it to switch to Lucene syntax. The toggle persists across page navigations within the same session.

Lucene query syntax

Lucene provides the most powerful text search capabilities, including regex, proximity searches, and boosting.

Basic queries

# Term search
error
 
# Phrase search
"connection timeout"
 
# Field-specific
status_code:500
 
# Wildcard
service_name:pay*
 
# Regex (wrapped in forward slashes)
message:/error.*timeout/

Range queries

# Numeric range (inclusive)
status_code:[500 TO 599]
 
# Numeric range (exclusive)
response_time:{1000 TO *}
 
# Date range
timestamp:[2025-06-01 TO 2025-06-07]

Boolean operators

# AND (must include both)
status_code:500 AND service_name:"payment-service"
 
# OR
status_code:500 OR status_code:502
 
# NOT
status_code:500 AND NOT region:"eu-west-1"
 
# Grouping
(status_code:500 OR status_code:502) AND service_name:"payment*"

Advanced features

# Proximity search (words within 5 positions of each other)
"connection database"~5
 
# Fuzzy search (handles typos)
servce_name:paymnet~2
 
# Boosting (increase relevance of a term)
error^2 AND warning

Switching languages in the UI

In Discover and Dashboards

Look at the search bar at the top of the page
The current language is shown as a button (e.g., DQL)
Click the button to toggle between DQL and Lucene
For PPL, select the PPL option from the language dropdown (available in Observability workspaces)

In the SQL Workbench

Navigate to OpenSearch Plugins → Query Workbench
The workbench supports both SQL and PPL
Toggle between them using the language selector at the top

Choosing the right language

Scenario	Recommended Language
Exploring logs, finding patterns	PPL
Building dashboard filters	DQL
Complex text search with regex	Lucene
Generating reports for stakeholders	SQL
Time-series aggregations	PPL
Quick field filtering	DQL
Proximity or fuzzy text search	Lucene
Programmatic API queries	SQL or PPL

Tips and best practices

Start with DQL for simple filtering — it's fast and intuitive
Graduate to PPL when you need aggregations, stats, or multi-step transformations
Use SQL when sharing queries with team members who know relational databases
Reserve Lucene for advanced text search scenarios (regex, proximity, fuzzy)
PPL queries can be saved as saved searches in Discover for reuse
SQL results can be exported as CSV or JSON from the Query Workbench

Troubleshooting

PPL query returns no results

Check the index name in the source command — it's case-sensitive
Verify the time range covers your data
Ensure the field names match exactly (use Discover to check available fields)

DQL filter not working as expected

Wrap multi-word values in quotes: service_name: "payment service"
Use and/or in lowercase — DQL is case-insensitive for operators
Check for typos in field names

SQL query fails with "unsupported" error

Not all SQL features are supported — check the function reference
Avoid INSERT, UPDATE, DELETE statements
Use backticks for index names with special characters: `my-index-*`

Lucene regex not matching

Regex patterns must be wrapped in forward slashes: /pattern/
Lucene regex doesn't support all PCRE features (no lookahead/lookbehind)
Anchor patterns are implicit — the regex matches the entire term