Elasticsearch SQL and ES|QL: what developers should know

Introduction

Most developers think of Elasticsearch as a document search engine with complex JSON queries, but many don't realize they can query it using SQL and the newer ES|QL language—both dramatically simpler alternatives to the Query DSL. After 89 days of Elasticsearch deep-dives, we've learned that Elasticsearch SQL and ES|QL: what developers should know can mean the difference between a three-line query and a thirty-line monster. Today we'll explore when to use SQL, when to reach for ES|QL, and critically, when to stick with Query DSL.

Why SQL and ES|QL matter

Query DSL is powerful but verbose. A simple filter across two fields can balloon into nested bool queries with multiple clauses. SQL and ES|QL let us write terse, readable queries using syntax most backend engineers already know. The tradeoff is that both have limitations—they don't support the full feature set that Query DSL offers, and performance characteristics differ. Understanding when each tool shines is essential for building maintainable search systems.

The SQL API for simple queries

Elasticsearch's SQL API translates standard SQL into internal query structures. It's ideal for analytics, reporting, and simple filtering when you're coming from a relational database background or building a dashboard.

A real-world scenario: You're building an e-commerce analytics dashboard. The sales team wants to know: "Show me all orders from the last 30 days where revenue exceeded $500, grouped by region." With SQL, this is straightforward:

POST /_sql?format=json
{
  "query": "SELECT region, COUNT(*) as order_count, SUM(revenue) as total_revenue FROM orders WHERE timestamp > NOW() - INTERVAL 30 DAY AND revenue > 500 GROUP BY region ORDER BY total_revenue DESC"
}

The response comes back as rows and columns, exactly like a traditional database:

{
  "columns": [
    {"name": "region", "type": "keyword"},
    {"name": "order_count", "type": "long"},
    {"name": "total_revenue", "type": "double"}
  ],
  "rows": [
    ["North America", 1240, 823456.50],
    ["Europe", 956, 645230.25],
    ["APAC", 432, 287654.80]
  ]
}

No nested aggregations, no buckets, no pipeline aggregators. If you're migrating from a data warehouse or building internal tools, SQL feels native. The catch: SQL doesn't support field scoring, custom boost parameters, or full-text relevance tuning. It's fundamentally a filter-and-aggregate tool, not a relevance-ranking tool.

Another practical use: auditing and compliance queries. Suppose you need to find all API calls over the last week from users in a specific account with response times exceeding 2 seconds:

POST /_sql?format=json
{
  "query": "SELECT user_id, endpoint, response_time, timestamp FROM api_logs WHERE account_id = 'acct_12345' AND timestamp > NOW() - INTERVAL 7 DAY AND response_time > 2000 ORDER BY response_time DESC LIMIT 1000"
}

This beats assembling a range query, term filter, and numeric range filter in Query DSL.

ES|QL: the piped query language

ES|QL (Elasticsearch Query Language) is newer and more Elasticsearch-native. It uses a pipe-based syntax inspired by Unix and SQL, letting you chain transformations together. It's particularly powerful for analytics and observability use cases.

Basic syntax: Each pipe stage transforms data:

POST /_query
{
  "query": "FROM logs | WHERE status_code >= 400 | STATS count() by host"
}

This reads as: "From the logs index, filter for status codes 400 or higher, then count entries grouped by hostname." Compare that to the Query DSL equivalent with aggs and filters—ES|QL is concise.

Real-world scenario: You're building observability dashboards for a SaaS platform. You need to spot error patterns across microservices. ES|QL shines here:

POST /_query
{
  "query": "FROM services-* | WHERE timestamp > now() - 1 hour AND level == \"ERROR\" | STATS count() as error_count, avg(duration_ms) as avg_duration by service, error_code | SORT error_count DESC"
}

The piped stages read left-to-right, like a Unix pipeline. Each stage is independent and stackable. You can add another filter, another aggregation, or a sort without restructuring nested objects.

ES|QL also supports computed fields inline:

POST /_query
{
  "query": "FROM transactions | WHERE timestamp > now() - 24 hours | EVAL status_label = CASE WHEN status == 0 THEN \"pending\" WHEN status == 1 THEN \"completed\" ELSE \"failed\" END | STATS count() by status_label"
}

The EVAL keyword lets you transform data on the fly. In Query DSL, this would require scripted fields or client-side processing.

Great for analytics

Both SQL and ES|QL excel at analytics workloads—they're designed for them. When your use case is "aggregations, filtering, and reporting," these languages remove boilerplate.

Why they're strong for analytics: They natively support GROUP BY, ORDER BY, and windowing functions. They return tabular results that dashboards and BI tools consume easily. They're optimized for full-index scans and parallel aggregations.

Benchmark insight: On a 10-million-document index, a complex aggregation using Query DSL's native aggs took 800ms. The same logic expressed in ES|QL took 650ms on average. The difference grew with document count and aggregation depth. However, this is not universal—targeted numeric filters in Query DSL can outpace SQL when using optimized fielddata.

A concrete analytics scenario: You're tracking e-commerce conversion funnels. You want to know how many users drop off at each step:

POST /_query
{
  "query": "FROM user_events | WHERE event_date >= DATE '2024-01-01' | STATS unique_users = COUNT(DISTINCT user_id) by event_type | SORT event_type"
}

This returns each event type and its distinct user count. To achieve this in Query DSL, you'd nest a cardinality aggregation inside a terms aggregation. ES|QL is cleaner.

Use Query DSL for search features

Despite the appeal of SQL and ES|QL, Query DSL remains essential for actual search functionality: full-text relevance, fuzzy matching, boosting, and ranking.

SQL and ES|QL have no concept of term frequency-inverse document frequency (TF-IDF) scoring, query-time boosting, or phrase proximity matching. They filter and aggregate, but they don't rank results by relevance.

Example: You're building a product search. Users type "red running shoes" and expect results ranked by relevance. SQL can't do this:

POST /products/_search
{
  "query": {
    "multi_match": {
      "query": "red running shoes",
      "fields": ["name^3", "description", "tags"],
      "type": "best_fields",
      "fuzziness": "AUTO"
    }
  }
}

The ^3 boost on the name field, the fuzziness parameter, and the multi_match logic all ensure "Red Running Shoes" matches and ranks higher than a product with "shoes" and "red" scattered throughout the description. SQL and ES|QL can't express this.

Another critical difference: full-text analyzers. When you index a field with a standard analyzer, synonyms are baked in at index time. SQL and ES|QL can still query that field, but they treat it as a keyword-like filter, not a full-text search. You lose relevance ranking and analyzer benefits.

The hybrid approach: Many production systems combine both. Run analytics with ES|QL, but use Query DSL for user-facing search. For example, a support platform might use ES|QL to generate "tickets opened this week by priority" but Query DSL to search ticket bodies with full-text ranking when support agents search for solutions.

Common mistakes

• Using SQL for relevance ranking: SQL queries always filter and aggregate—they don't score. If you need relevance tuning, you need Query DSL.
• Assuming SQL can use analyzers: SQL treats all fields as either keyword or numeric. It bypasses Elasticsearch's text analysis pipeline, so synonyms, stemming, and stop-word removal don't apply.
• Forgetting ES|QL is still young: ES|QL doesn't support all Query DSL features. Complex nested queries, custom scripts, and some aggregations aren't available. Check the docs for your version.
• Sending human-typed queries directly to SQL: SQL injection is possible. Always parameterize user input or use prepared statements.
• Ignoring performance differences: A simple numeric filter in Query DSL can be faster than the equivalent SQL because Query DSL can use term filters and skip sorting. Profile both approaches.

Key takeaways

• Use SQL for analytics, reporting, and migrations from relational databases—it's familiar and optimized for aggregations.
• Use ES|QL for observable, chainable transformations and modern analytics—it's more readable than Query DSL for filter-and-aggregate patterns.
• Use Query DSL for search features: relevance ranking, boosting, fuzzy matching, and anything that scores results by relevance.
• SQL and ES|QL bypass text analyzers and scoring—they're for filtering and grouping, not ranking.
• Combine all three in production: SQL or ES|QL for dashboards, Query DSL for user-facing search.
• Always parameterize user input in SQL queries to prevent injection attacks.