elasticsearch/docs/reference/esql/esql-rest.asciidoc

[[esql-rest]]
=== {esql} REST API

++++
<titleabbrev>REST API</titleabbrev>
++++

[discrete]
[[esql-rest-overview]]
=== Overview

The <<esql-query-api,{esql} query API>> accepts an {esql} query string in the
`query` parameter, runs it, and returns the results. For example:

[source,console]
----
POST /_query?format=txt
{
  "query": "FROM library | KEEP author, name, page_count, release_date | SORT page_count DESC | LIMIT 5"
}
----
// TEST[setup:library]

Which returns:

[source,text]
----
     author      |        name        |  page_count   | release_date
-----------------+--------------------+---------------+------------------------
Peter F. Hamilton|Pandora's Star      |768            |2004-03-02T00:00:00.000Z
Vernor Vinge     |A Fire Upon the Deep|613            |1992-06-01T00:00:00.000Z
Frank Herbert    |Dune                |604            |1965-06-01T00:00:00.000Z
Alastair Reynolds|Revelation Space    |585            |2000-03-15T00:00:00.000Z
James S.A. Corey |Leviathan Wakes     |561            |2011-06-02T00:00:00.000Z
----
// TESTRESPONSE[s/\|/\\|/ s/\+/\\+/]
// TESTRESPONSE[non_json]

[discrete]
[[esql-kibana-console]]
==== Run the {esql} query API in Console

We recommend using {kibana-ref}/console-kibana.html[Console] to run the {esql} 
query API. When creating the query, using triple quotes (`"""`) allows you to 
use special characters like quotes (`"`) without having to escape them. They 
also make it easier to write multi-line requests:

// tag::esql-query-api[]
[source,console]
----
POST /_query?format=txt
{
  "query": """
    FROM library
    | KEEP author, name, page_count, release_date
    | SORT page_count DESC
    | LIMIT 5
  """
}
----
// TEST[setup:library]

Inside quotes, Console suggests possible commands and functions to autocomplete your query:

image::images/esql/console-esql-autocomplete.png[align="center"]

[discrete]
[[esql-rest-format]]
==== Response formats

{esql} can return the data in the following human readable and binary formats.
You can set the format by specifying the `format` parameter in the URL or by
setting the `Accept` or `Content-Type` HTTP header.

NOTE: The URL parameter takes precedence over the HTTP headers. If neither is
specified then the response is returned in the same format as the request.

[cols="m,4m,8"]

|===
s|`format`
s|HTTP header
s|Description

3+h| Human readable

|csv
|text/csv
|{wikipedia}/Comma-separated_values[Comma-separated values]

|json
|application/json
|https://www.json.org/[JSON] (JavaScript Object Notation) human-readable format

|tsv
|text/tab-separated-values
|{wikipedia}/Tab-separated_values[Tab-separated values]

|txt
|text/plain
|CLI-like representation

|yaml
|application/yaml
|{wikipedia}/YAML[YAML] (YAML Ain't Markup Language) human-readable format

3+h| Binary

|cbor
|application/cbor
|https://cbor.io/[Concise Binary Object Representation]

|smile
|application/smile
|{wikipedia}/Smile_(data_interchange_format)[Smile] binary data format similar
to CBOR

|arrow
|application/vnd.apache.arrow.stream
|**Experimental.** https://arrow.apache.org/[Apache Arrow] dataframes, https://arrow.apache.org/docs/format/Columnar.html#ipc-streaming-format[IPC streaming format]
|===

The `csv` format accepts a formatting URL query attribute, `delimiter`, which
indicates which character should be used to separate the CSV values. It defaults
to comma (`,`) and cannot take any of the following values: double quote (`"`),
carriage-return (`\r`) and new-line (`\n`). The tab (`\t`) can also not be used.
Use the `tsv` format instead.

[discrete]
[[esql-rest-filtering]]
==== Filtering using {es} Query DSL

Specify a Query DSL query in the `filter` parameter to filter the set of
documents that an {esql} query runs on.

[source,console]
----
POST /_query?format=txt
{
  "query": """
    FROM library
    | KEEP author, name, page_count, release_date
    | SORT page_count DESC
    | LIMIT 5
  """,
  "filter": {
    "range": {
      "page_count": {
        "gte": 100,
        "lte": 200
      }
    }
  }
}
----
// TEST[setup:library]

Which returns:

[source,text]
--------------------------------------------------
    author     |                name                |  page_count   | release_date
---------------+------------------------------------+---------------+------------------------
Douglas Adams  |The Hitchhiker's Guide to the Galaxy|180            |1979-10-12T00:00:00.000Z
--------------------------------------------------
// TESTRESPONSE[s/\|/\\|/ s/\+/\\+/]
// TESTRESPONSE[non_json]

[discrete]
[[esql-rest-columnar]]
==== Columnar results

By default, {esql} returns results as rows. For example, `FROM` returns each
individual document as one row. For the `json`, `yaml`, `cbor` and `smile`
<<esql-rest-format,formats>>, {esql} can return the results in a columnar
fashion where one row represents all the values of a certain column in the
results.

[source,console]
----
POST /_query?format=json
{
  "query": """
    FROM library
    | KEEP author, name, page_count, release_date
    | SORT page_count DESC
    | LIMIT 5
  """,
  "columnar": true
}
----
// TEST[setup:library]

Which returns:

[source,console-result]
----
{
  "took": 28,
  "is_partial": false,
  "documents_found": 5,
  "values_loaded": 20,
  "columns": [
    {"name": "author", "type": "text"},
    {"name": "name", "type": "text"},
    {"name": "page_count", "type": "integer"},
    {"name": "release_date", "type": "date"}
  ],
  "values": [
    ["Peter F. Hamilton", "Vernor Vinge", "Frank Herbert", "Alastair Reynolds", "James S.A. Corey"],
    ["Pandora's Star", "A Fire Upon the Deep", "Dune", "Revelation Space", "Leviathan Wakes"],
    [768, 613, 604, 585, 561],
    ["2004-03-02T00:00:00.000Z", "1992-06-01T00:00:00.000Z", "1965-06-01T00:00:00.000Z", "2000-03-15T00:00:00.000Z", "2011-06-02T00:00:00.000Z"]
  ]
}
----
// TESTRESPONSE[s/"took": 28/"took": "$body.took"/]

[discrete]
[[esql-locale-param]]
==== Returning localized results

Use the `locale` parameter in the request body to return results (especially dates) formatted per the conventions of the locale.
If `locale` is not specified, defaults to `en-US` (English).
Refer to https://www.oracle.com/java/technologies/javase/jdk17-suported-locales.html[JDK Supported Locales].

Syntax: the `locale` parameter accepts language tags in the (case-insensitive) format `xy` and `xy-XY`.

For example, to return a month name in French:

[source,console]
----
POST /_query
{
  "locale": "fr-FR",
  "query": """
          ROW birth_date_string = "2023-01-15T00:00:00.000Z"
          | EVAL birth_date = date_parse(birth_date_string)
          | EVAL month_of_birth = DATE_FORMAT("MMMM",birth_date)
          | LIMIT 5
   """
}
----
// TEST[setup:library]
// TEST[skip:This can output a warning, and asciidoc doesn't support allowed_warnings]

[discrete]
[[esql-rest-params]]
==== Passing parameters to a query

Values, for example for a condition, can be passed to a query "inline", by
integrating the value in the query string itself:

[source,console]
----
POST /_query
{
  "query": """
    FROM library
    | EVAL year = DATE_EXTRACT("year", release_date)
    | WHERE page_count > 300 AND author == "Frank Herbert"
    | STATS count = COUNT(*) by year
    | WHERE count > 0
    | LIMIT 5
  """
}
----
// TEST[setup:library]

To avoid any attempts of hacking or code injection, extract the values in a
separate list of parameters. Use question mark placeholders (`?`) in the query
string for each of the parameters:

[source,console]
----
POST /_query
{
  "query": """
    FROM library
    | EVAL year = DATE_EXTRACT("year", release_date)
    | WHERE page_count > ? AND author == ?
    | STATS count = COUNT(*) by year
    | WHERE count > ?
    | LIMIT 5
  """,
  "params": [300, "Frank Herbert", 0]
}
----
// TEST[setup:library]

The parameters can be named parameters or positional parameters.

Named parameters use question mark placeholders (`?`) followed by a string.

[source,console]
----
POST /_query
{
  "query": """
    FROM library
    | EVAL year = DATE_EXTRACT("year", release_date)
    | WHERE page_count > ?page_count AND author == ?author
    | STATS count = COUNT(*) by year
    | WHERE count > ?count
    | LIMIT 5
  """,
  "params": [{"page_count" : 300}, {"author" : "Frank Herbert"}, {"count" : 0}]
}
----
// TEST[setup:library]

Positional parameters use question mark placeholders (`?`) followed by an
integer.

[source,console]
----
POST /_query
{
  "query": """
    FROM library
    | EVAL year = DATE_EXTRACT("year", release_date)
    | WHERE page_count > ?1 AND author == ?2
    | STATS count = COUNT(*) by year
    | WHERE count > ?3
    | LIMIT 5
  """,
  "params": [300, "Frank Herbert", 0]
}
----
// TEST[setup:library]

[discrete]
[[esql-rest-async-query]]
==== Running an async {esql} query

The <<esql-async-query-api,{esql} async query API>> lets you asynchronously
execute a query request, monitor its progress, and retrieve results when
they become available.

Executing an {esql} query is commonly quite fast, however queries across
large data sets or frozen data can take some time. To avoid long waits,
run an async {esql} query.

Queries initiated by the async query API may return results or not. The
`wait_for_completion_timeout` property determines how long to wait for
the results. If the results are not available by this time, a
<<esql-async-query-api-response-body-query-id,query id>> is returned which
can be later used to retrieve the results. For example:

[source,console]
----
POST /_query/async
{
  "query": """
    FROM library
    | EVAL year = DATE_TRUNC(1 YEARS, release_date)
    | STATS MAX(page_count) BY year
    | SORT year
    | LIMIT 5
  """,
  "wait_for_completion_timeout": "2s"
}
----
// TEST[setup:library]
// TEST[skip:awaitsfix https://github.com/elastic/elasticsearch/issues/104013]

If the results are not available within the given timeout period, 2
seconds in this case, no results are returned but rather a response that
includes:

* A query ID
* An `is_running` value of _true_, indicating the query is ongoing

The query continues to run in the background without blocking other
requests.

[source,console-result]
----
{
  "id": "FmNJRUZ1YWZCU3dHY1BIOUhaenVSRkEaaXFlZ3h4c1RTWFNocDdnY2FSaERnUTozNDE=",
  "is_running": true
}
----
// TEST[skip: no access to query ID - may return response values]

To check the progress of an async query, use the <<esql-async-query-get-api,
{esql} async query get API>> with the query ID. Specify how long you'd like
to wait for complete results in the `wait_for_completion_timeout` parameter.

[source,console]
----
GET /_query/async/FmNJRUZ1YWZCU3dHY1BIOUhaenVSRkEaaXFlZ3h4c1RTWFNocDdnY2FSaERnUTozNDE=?wait_for_completion_timeout=30s
----
// TEST[skip: no access to query ID - may return response values]

If the response's `is_running` value is `false`, the query has finished
and the results are returned, along with the `took` time for the query.

[source,console-result]
----
{
  "is_running": false,
  "took": 48,
  "columns": ...
}
----
// TEST[skip: no access to query ID - may return response values]

Use the <<esql-async-query-delete-api, {esql} async query delete API>> to
delete an async query before the `keep_alive` period ends. If the query
is still running, {es} cancels it.

[source,console]
----
DELETE /_query/async/FmdMX2pIang3UWhLRU5QS0lqdlppYncaMUpYQ05oSkpTc3kwZ21EdC1tbFJXQToxOTI=
----
// TEST[skip: no access to query ID]