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]]
==== Kibana Console

If you are using {kibana-ref}/console-kibana.html[Kibana Console] (which is
highly recommended), take advantage of the triple quotes `"""` when creating the
query. This not only automatically escapes double quotes (`"`) inside the query
string but also supports 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]

[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

|===

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]
----
{
  "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"]
  ]
}
----

[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]