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

1. Introduction

Elasticsearch is a popular search engine known for fast queries and good performance, but its native Query DSL can be hard to remember. Since version 6.3.0, Elasticsearch also supports SQL, allowing users to write familiar MySQL‑style statements.

2. Environment Setup

Download Elasticsearch (version 6.3.0 or later) from the official site and ensure a JDK is installed. Optionally install Kibana for visual inspection. The example uses Elasticsearch 7.15.2.

3. Getting Started

Create an index my_index with mappings for title (text), category (keyword) and price (float):

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

Insert a few sample documents:

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. SQL Queries

Run a simple SELECT to retrieve all rows:

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

Limit results (pagination):

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

Order by a field:

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

Group by a field and count:

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

Aggregate sum of a numeric field:

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

Filter with a WHERE clause:

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

5. Advanced Functions

Insert documents with a create_date field to demonstrate date handling:

POST /my_index/_doc/4
{
  "title": "JAVA编程思想",
  "category": "books",
  "price": 21.54,
  "create_date": "2023-11-18T12:00:00.123"
}
POST /my_index/_doc/5
{
  "title": "Mysql操作手册",
  "category": "books",
  "price": 21.54,
  "create_date": "2023-11-17T07:00:00.123"
}

Format dates:

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

Add hours to a timestamp:

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

String manipulation – extract a substring from category:

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

6. Conclusion

Elasticsearch SQL lets developers use familiar relational‑database syntax to query ES data, simplifying reporting and analytics. Most MySQL queries (SELECT, WHERE, ORDER BY, GROUP BY, LIMIT, aggregation, date and string functions) work directly, reducing the learning curve for newcomers.

Official documentation: https://www.elastic.co/guide/en/elasticsearch/reference/8.9/sql-overview.html