Master Elasticsearch SQL: From Basic Queries to Advanced DSL Translations

Elasticsearch is a distributed full‑text search engine that also supports SQL queries via the X‑Pack SQL feature, allowing users familiar with relational syntax to query Elasticsearch indices.

Installation

If you haven't installed Elasticsearch and Kibana, refer to the earlier "Elastic: Beginner's Guide" article for setup instructions.

Preparing Data

Open Kibana, click “Load a data set and a Kibana dashboard”, and add the kibana_sample_data_flights index.

SQL Operations

Describe index schema

POST /_sql
{
  "query": "DESCRIBE kibana_sample_data_flights"
}

The response lists columns, types, and mappings such as AvgTicketPrice (REAL), Cancelled (BOOLEAN), Dest (VARCHAR), etc.

Simple SELECT

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

Result shows a single flight number.

WHERE and ORDER BY

POST /_sql?format=txt
{
  "query": "SELECT OriginCityName, DestCityName FROM flights WHERE FlightTimeHour > 5 AND OriginCountry='US' ORDER BY FlightTimeHour DESC LIMIT 10"
}

Returns the longest 10 U.S. flights over 5 hours.

Functions & Expressions

POST /_sql?format=txt
{
  "query": "SELECT MONTH_OF_YEAR(timestamp), OriginCityName, DestCityName FROM flights WHERE FlightTimeHour > 1 AND MONTH_OF_YEAR(timestamp) > 2 ORDER BY FlightTimeHour DESC LIMIT 10"
}

Demonstrates date functions and aggregation.

Math Example

sql> SELECT ((1 + 3) * 1.5 / (7 - 6)) * 2 AS random;

Shows server‑side arithmetic without an equivalent DSL query.

Converting SQL to DSL

Use the /_sql/translate endpoint to obtain the equivalent Elasticsearch DSL for any SQL statement.

POST /_sql/translate
{
  "query": "SELECT OriginCityName, DestCityName FROM flights WHERE FlightTimeHour > 5 AND OriginCountry='US' ORDER BY FlightTimeHour DESC LIMIT 10"
}

The translated DSL includes bool queries, range and term filters, source includes, and sorting.

Complex queries with functions are translated into painless scripts for filtering and sorting, as shown in the lengthy example that selects timestamps, flight numbers, distances, speeds, and day‑of‑week with multiple script clauses.

Reindexing and Alias Management

To change a field type (e.g., FlightTimeHour from keyword to float), create a new index with the desired mapping, reindex the data, and update the alias:

PUT flight1
{
  "mappings": {
    "properties": {
      "FlightTimeHour": { "type": "float" },
      ...
    }
  }
}

POST _reindex
{
  "source": { "index": "flights" },
  "dest": { "index": "flight1" }
}

POST _aliases
{
  "actions": [
    { "add": { "index": "flight1", "alias": "flights" } },
    { "remove": { "index": "kibana_sample_data_flights", "alias": "flights" } }
  ]
}

Complex Query Example

Find the two fastest flights on Monday‑Wednesday mornings (9‑11 am) with distance > 500 km, rounding distance and speed, and ordering by speed then distance:

sql> SELECT timestamp, FlightNum, OriginCityName, DestCityName, ROUND(DistanceMiles) AS distance, ROUND(DistanceMiles/FlightTimeHour) AS speed, DAY_OF_WEEK(timestamp) AS day_of_week FROM flights WHERE DAY_OF_WEEK(timestamp) >= 0 AND DAY_OF_WEEK(timestamp) <= 2 AND HOUR_OF_DAY(timestamp) >=9 AND HOUR_OF_DAY(timestamp) <= 10 ORDER BY speed DESC, distance DESC LIMIT 2;

The result includes timestamps, flight numbers, cities, rounded distance, speed, and day of week.

Conclusion

Elasticsearch SQL provides a convenient way to query Elasticsearch data without writing DSL, supports functions, and can translate queries to optimized DSL. For best performance, consider indexing frequently queried fields (e.g., day of week, hour of day, speed) to avoid script‑based filters.