--- type: api title: Translate SQL into Elasticsearch queries description: **All methods and paths for this operation:** url: https://www.elastic.co/elastic/docs-builder/docs/4641/api/doc/elasticsearch/v9/operation/operation-sql-translate resource: https://www.elastic.co/elastic/docs-builder/docs/4641/api/doc/elasticsearch/v9/operation/operation-sql-translate products: - Elasticsearch --- # Translate SQL into Elasticsearch queries Authentication: required Availability: Generally available since 6.3 ## Paths - `POST` (also `GET`) `/_sql/translate` ## Description 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 authorization - Index privileges: `read` ## Request body `application/json` - `fetch_size` (number · default: 1000) — optional The maximum number of rows (or entries) to return in one response. - `filter` ({} Query · default: none) — optional The Elasticsearch query DSL for additional filtering. See [Query](https://www.elastic.co/elastic/docs-builder/docs/4641/api/doc/elasticsearch/v9/types/_types-query_dsl-querycontainer) - `query` (string) — required The SQL query to run. - `time_zone` (string · default: Z) — optional The ISO-8601 time zone ID for the search. ## Response `application/json` - `aggregations` (map string to {} Aggregation) See [Aggregation](https://www.elastic.co/elastic/docs-builder/docs/4641/api/doc/elasticsearch/v9/types/_types-aggregations-aggregationcontainer) - `size` (number) - `_source` (union oneOf) - **boolean** - **SourceFilter** - `exclude_vectors` (boolean) If `true`, vector fields are excluded from the returned source. This option takes precedence over `includes`: any vector field will remain excluded even if it matches an `includes` rule. - `excludes` (union oneOf) A list of fields to exclude from the returned source. One of: `string[]` or `string` - `includes` (union oneOf) A list of fields to include in the returned source. One of: `string[]` or `string` - `fields` (object[]) - `field` (string) A wildcard pattern. The request returns values for field names matching this pattern. - `format` (string) The format in which the values are returned. - `include_unmapped` (boolean) - `query` ({} Query) See [Query](https://www.elastic.co/elastic/docs-builder/docs/4641/api/doc/elasticsearch/v9/types/_types-query_dsl-querycontainer) - `sort` (union oneOf) A value or an array; each item is one of: - **string** Path to field or array of paths. Some API's support wildcards in the path to select multiple fields. - **SortOptions** - `_score` (object) - `order` (enum) Supported values include: - `asc`: Ascending (smallest to largest) - `desc`: Descending (largest to smallest) Values: `asc`, `desc` - `_doc` (object) - `order` (enum) Supported values include: - `asc`: Ascending (smallest to largest) - `desc`: Descending (largest to smallest) Values: `asc`, `desc` - `_geo_distance` (object) - `mode` (enum) Values: `min`, `max`, `sum`, `avg`, `median` - `distance_type` (enum) Supported values include: - `arc`: The `arc` calculation is the most accurate. - `plane`: The `plane` calculation is faster but less accurate. Values: `arc`, `plane` - `ignore_unmapped` (boolean) - `order` (enum) Supported values include: - `asc`: Ascending (smallest to largest) - `desc`: Descending (largest to smallest) Values: `asc`, `desc` - `unit` (enum) Values: `in`, `ft`, `yd`, `mi`, `nmi`, `km`, `m`, `cm`, `mm` - `nested` (object) - `filter` ({} Query) See [Query](https://www.elastic.co/elastic/docs-builder/docs/4641/api/doc/elasticsearch/v9/types/_types-query_dsl-querycontainer) - `max_children` (number) - `nested` (object) — recursive Same fields as `nested` in `_geo_distance` - `path` (string) - `_script` (object) - `order` (enum) Supported values include: - `asc`: Ascending (smallest to largest) - `desc`: Descending (largest to smallest) Values: `asc`, `desc` - `script` (string) - `type` (enum) Values: `string`, `number`, `version` - `mode` (enum) Values: `min`, `max`, `sum`, `avg`, `median` - `nested` (object) - `filter` ({} Query) See [Query](https://www.elastic.co/elastic/docs-builder/docs/4641/api/doc/elasticsearch/v9/types/_types-query_dsl-querycontainer) - `max_children` (number) - `nested` (object) — recursive Same fields as `nested` in `_script` - `path` (string) - `track_total_hits` (boolean | number) ## Code examples #### Console ```console POST _sql/translate { "query": "SELECT * FROM library ORDER BY page_count DESC", "fetch_size": 10 } ``` #### JavaScript ```javascript const response = await client.sql.translate({ query: "SELECT * FROM library ORDER BY page_count DESC", fetch_size: 10, }); ``` #### Python ```python resp = client.sql.translate( query="SELECT * FROM library ORDER BY page_count DESC", fetch_size=10, ) ``` #### curl ```curl 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 ```java client.sql().translate(t -> t .fetchSize(10) .query(""" SELECT * FROM library ORDER BY page_count DESC """) ); ``` #### C# ```c# var response = await client.Sql .TranslateAsync(d1 => d1 .FetchSize(10) .Query("SELECT * FROM library ORDER BY page_count DESC") ); ``` #### PHP ```php $resp = $client->sql()->translate([ "body" => [ "query" => "SELECT * FROM library ORDER BY page_count DESC", "fetch_size" => 10, ], ]); ``` #### Ruby ```ruby response = client.sql.translate( body: { "query": "SELECT * FROM library ORDER BY page_count DESC", "fetch_size": 10 } ) ``` ## Response examples #### 200 `application/json` Generated from the API spec. Values are representative. Unions use the first variant; deeply nested or large bodies may omit fields. ```json { "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": {} } } } } } ```