How to Query Elasticsearch with SQL: A Step‑by‑Step Guide

1. Introduction

Elasticsearch is a widely used search engine known for fast queries, but its native Query DSL can be difficult to memorize. Starting with version 6.3.0, Elasticsearch provides an SQL endpoint that lets developers write familiar SQL statements to query data.

2. Environment Setup

Download Elasticsearch 6.3.0 or later from the official site and ensure a compatible JDK is installed. Start the cluster, and optionally install Kibana for visual inspection.

3. Index Creation and Sample Data

Create an index my_index with three fields: title (text), category (keyword) and price (float). Then index a few example documents.

PUT /my_index
{
  "mappings": {
    "properties": {
      "title": {"type": "text"},
      "category": {"type": "keyword"},
      "price": {"type": "float"}
    }
  }
}

POST /my_index/_doc/1
{ "title": "ES学习手册", "category": "books", "price": 29.99 }

POST /my_index/_doc/2
{ "title": "on my way", "category": "music", "price": 13.57 }

POST /my_index/_doc/3
{ "title": "Kibana中文笔记", "category": "books", "price": 21.54 }

4. Traditional Query DSL Example

The following DSL query combines must, should, and filter clauses and adds an aggregation to compute the average price per category.

GET /my_index/_search
{
  "query": {
    "bool": {
      "must": [
        {"match": {"title": "search"}},
        {"bool": {"should": [{"term": {"category": "books"}}, {"term": {"category": "music"}}]}}
      ],
      "filter": {"range": {"price": {"gte": 20, "lte": 100}}}
    }
  },
  "aggs": {
    "avg_price_per_category": {
      "terms": {"field": "category", "size": 10},
      "aggs": {"avg_price": {"avg": {"field": "price"}}}
    }
  }
}

5. Equivalent SQL Queries

The same logic can be expressed with simple SQL statements. Below are several examples that illustrate common operations.

Basic SELECT with filters

SELECT title, category, price
FROM my_index
WHERE title = 'search' AND (category = 'books' OR category = 'music')
  AND price >= 20 AND price <= 100
GROUP BY category
ORDER BY AVG(price) DESC
LIMIT 10

Pagination (LIMIT)

POST /_sql?format=txt
{ "query": "SELECT * FROM my_index LIMIT 1" }

Sorting (ORDER BY)

POST /_sql?format=txt
{ "query": "SELECT * FROM my_index ORDER BY price DESC" }

Grouping (GROUP BY)

POST /_sql?format=txt
{ "query": "SELECT category, COUNT(1) FROM my_index GROUP BY category" }

Aggregation (SUM)

POST /_sql?format=txt
{ "query": "SELECT SUM(price) FROM my_index" }

Exact match (WHERE)

POST /_sql?format=txt
{ "query": "SELECT * FROM my_index WHERE price = '13.57'" }

Date formatting

POST /_sql?format=txt
{ "query": "SELECT title, DATETIME_FORMAT(create_date, 'YYYY-MM-dd') AS date FROM my_index WHERE category='books'" }

Date arithmetic

POST /_sql?format=txt
{ "query": "SELECT date_add('hour', 8, create_date) AS date FROM my_index WHERE category='books'" }

String manipulation (SUBSTRING)

POST /_sql?format=txt
{ "query": "SELECT SUBSTRING(category, 1, 3) AS SubstringValue FROM my_index" }

6. Observations

SQL queries return results in a tabular, CSV‑like format, which many developers find more readable than raw JSON documents. The SQL layer supports most operations that are possible with the native DSL, making it convenient for reporting and ad‑hoc analysis.

7. Conclusion

Elasticsearch SQL offers a simple, readable, and powerful way to query indexed data, especially for users already familiar with relational databases. It lowers the learning curve, improves maintainability, and works well for typical reporting scenarios such as filtering, sorting, grouping, aggregation, and date handling.