Translate SQL into Elasticsearch queries

POST also GET /_sql/translate

Auth

Translate an SQL search into a search API request containing Query DSL. It accepts the same request body parameters as the SQL search API, excluding cursor.

Required privileges

Index privileges read

Request body

fetch_size number · default: 1000: The maximum number of rows (or entries) to return in one response.
filter {} Query · default: none: The Elasticsearch query DSL for additional filtering.
query string required: The SQL query to run.
time_zone string · default: Z: The ISO-8601 time zone ID for the search.; External docs

Response

application/json

aggregations map string to {} Aggregation
size number
_source union oneOf: One of:
fields object[]
query {} Query
sort union oneOf
track_total_hits boolean | number

Examples

Request Console 1 / 8 languages

Console Kibana Dev Tools

POST _sql/translate
{
  "query": "SELECT * FROM library ORDER BY page_count DESC",
  "fetch_size": 10
}

JavaScript Elasticsearch JavaScript Client

const response = await client.sql.translate({
  query: "SELECT * FROM library ORDER BY page_count DESC",
  fetch_size: 10,
});

Python Elasticsearch Python Client

resp = client.sql.translate(
    query="SELECT * FROM library ORDER BY page_count DESC",
    fetch_size=10,
)

curl Shell

curl -X POST "$ELASTICSEARCH_URL/_sql/translate" \
  -H "Authorization: ApiKey $ELASTIC_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT * FROM library ORDER BY page_count DESC",
    "fetch_size": 10
  }'

Java Elasticsearch Java Client

client.sql().translate(t -> t
    .fetchSize(10)
    .query("""
        SELECT * FROM library ORDER BY page_count DESC
        """)
);

C# Elasticsearch .NET Client

var response = await client.Sql
    .TranslateAsync(d1 => d1
        .FetchSize(10)
        .Query("SELECT * FROM library ORDER BY page_count DESC")
    );

PHP Elasticsearch PHP Client

$resp = $client->sql()->translate([
    "body" => [
        "query" => "SELECT * FROM library ORDER BY page_count DESC",
        "fetch_size" => 10,
    ],
]);

Ruby Elasticsearch Ruby Client

response = client.sql.translate(
  body: {
    "query": "SELECT * FROM library ORDER BY page_count DESC",
    "fetch_size": 10
  }
)

Response

{
  "aggregations": {
    "key": {
      "aggregations": {
        "key": {
          "aggregations": {},
          "meta": {},
          "adjacency_matrix": {
            "separator": "string"
          },
          "auto_date_histogram": {
            "buckets": 10,
            "format": "string",
            "offset": "string"
          },
          "avg": {
            "format": "string"
          },
          "avg_bucket": {
            "format": "string",
            "gap_policy": "skip"
          },
          "boxplot": {
            "compression": 0,
            "execution_hint": "default"
          },
          "bucket_script": {
            "format": "string",
            "gap_policy": "skip"
          },
          "bucket_selector": {
            "format": "string",
            "gap_policy": "skip"
          },
          "bucket_sort": {
            "from": 0,
            "gap_policy": "skip",
            "size": 0
          },
          "bucket_count_ks_test": {
            "sampling_method": "string"
          },
          "bucket_correlation": {},
          "cardinality": {
            "precision_threshold": 3000,
            "rehash": false
          },
          "cartesian_bounds": {},
          "cartesian_centroid": {},
          "categorize_text": {
            "max_unique_tokens": 50,
            "max_matched_tokens": 5,
            "similarity_threshold": 50,
            "shard_size": 0,
            "size": 10,
            "min_doc_count": 0,
            "shard_min_doc_count": 0
          },
          "change_point": {
            "format": "string",
            "gap_policy": "skip"
          },
          "children": {},
          "composite": {
            "size": 10
          },
          "cumulative_cardinality": {
            "format": "string",
            "gap_policy": "skip"
          },
          "cumulative_sum": {
            "format": "string",
            "gap_policy": "skip"
          },
          "date_histogram": {
            "format": "string",
            "min_doc_count": 0,
            "keyed": false
          },
          "date_range": {
            "format": "string",
            "keyed": false
          },
          "derivative": {
            "format": "string",
            "gap_policy": "skip"
          },
          "diversified_sampler": {
            "execution_hint": "global_ordinals",
            "max_docs_per_value": 1,
            "shard_size": 100
          },
          "extended_stats": {
            "format": "string",
            "sigma": 0
          },
          "extended_stats_bucket": {
            "format": "string",
            "gap_policy": "skip",
            "sigma": 0
          },
          "frequent_item_sets": {
            "minimum_set_size": 1,
            "minimum_support": 0.1,
            "size": 10
          },
          "filter": {}
        }
      }
    }
  }
}

Generated from the API spec