How to Use ES|QL in Elasticsearch 8.11: DSL vs SQL Queries Compared

1. Comparison of Reference Implementations

The article presents side‑by‑side examples of Elasticsearch DSL requests and their equivalent ES|QL statements, confirming that they produce identical results.

2. DSL Original Syntax

POST kibana_sample_data_ecommerce/_search

3. ES|QL Retrieval Syntax (SQL‑like)

POST /_query?format=txt
{
  "query": ""
  FROM kibana_sample_data_ecommerce
}

The output of both queries is the same.

4. DSL Search with Sorting

POST kibana_sample_data_ecommerce/_search
{
  "size": 3,
  "query": {
    "range": {
      "products.price": { "gte": 50 }
    }
  },
  "sort": [
    { "products.created_on": { "order": "desc" } }
  ]
}

5. ES|QL with Sorting and Limit

POST /_query?format=json
{
  "query": ""
  FROM kibana_sample_data_ecommerce
  | WHERE products.price > 50
  | LIMIT 3
  | SORT products.created_on DESC
}

These two queries are equivalent.

6. DSL Wildcard Matching

POST kibana_sample_data_ecommerce/_search
{
  "size": 30,
  "query": {
    "wildcard": {
      "products.product_name.keyword": { "value": "Basic*" }
    }
  },
  "sort": [
    { "products.created_on": { "order": "desc" } }
  ]
}

7. ES|QL LIKE Wildcard Matching

POST /_query?format=json
{
  "query": ""
  FROM kibana_sample_data_ecommerce
  | WHERE products.product_name LIKE "Basic*"
  | LIMIT 30
  | SORT products.created_on DESC
}

The DSL and ES|QL forms above are equivalent.

8. DSL Aggregation Example

GET kibana_sample_data_ecommerce/_search
{
  "size": 0,
  "aggs": {
    "data_histogram": {
      "date_histogram": {
        "field": "order_date",
        "fixed_interval": "1d"
      }
    }
  }
}

9. ES|QL Aggregation Example

POST /_query?format=json
{
  "query": ""
  FROM kibana_sample_data_ecommerce
  | KEEP order_date
  | EVAL bucket = AUTO_BUCKET(order_date, 31, "2023-10-01T00:00:00Z", "2023-10-31T23:59:59Z")
  | STATS COUNT(*) BY bucket
}

Both aggregation queries yield the same histogram.

10. ES|QL REST API Usage Tips

Limiting Output Fields

POST /_query?format=txt
{
  "query": "FROM kibana_sample_data_ecommerce | KEEP order_date, currency, customer_first_name, customer_full_name | SORT order_date DESC | LIMIT 5"
}

The result can also be expressed with line‑breaks for readability.

11. Running ES|QL in Kibana

ES|QL queries can be executed directly from the Kibana UI, providing a familiar SQL‑like experience for analysts.

12. What Is ES|QL?

Elasticsearch Query Language (ES|QL) is a powerful, easy‑to‑learn language for filtering, transforming, and analyzing data inside Elasticsearch. It uses a pipeline syntax (the | operator) to chain operations, enabling complex data processing without converting to the traditional Query DSL.

ES|QL runs on a new computation engine optimized for vectorized, cache‑friendly, multi‑threaded execution, delivering higher performance for search, aggregation, and transformation tasks compared to the classic DSL.

Historically, Elasticsearch offered DSL and later Elastic SQL, but neither fully covered all use cases. ES|QL combines the strengths of Elasticsearch and SQL, offering a concise, expressive way to query data.

References

https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-language.html

https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-rest.html