Overviewedit

The ES|QL query API accepts an ES|QL query string in the query parameter, runs it, and returns the results. For example:

POST /_query?format=txt
{
  "query": "FROM library | KEEP author, name, page_count, release_date | SORT page_count DESC | LIMIT 5"
}

Which returns:

     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

Kibana Consoleedit

If you are using 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:

POST /_query?format=txt
{
  "query": """
    FROM library
    | KEEP author, name, page_count, release_date
    | SORT page_count DESC
    | LIMIT 5
  """
}

Response formatsedit

ES|QL 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.

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.

Filtering using Elasticsearch Query DSLedit

Specify a Query DSL query in the filter parameter to filter the set of documents that an ES|QL query runs on.

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
      }
    }
  }
}

Which returns:

    author     |                name                |  page_count   | release_date
---------------+------------------------------------+---------------+------------------------
Douglas Adams  |The Hitchhiker's Guide to the Galaxy|180            |1979-10-12T00:00:00.000Z

Columnar resultsedit

By default, ES|QL returns results as rows. For example, FROM returns each individual document as one row. For the json, yaml, cbor and smile formats, ES|QL can return the results in a columnar fashion where one row represents all the values of a certain column in the results.

POST /_query?format=json
{
  "query": """
    FROM library
    | KEEP author, name, page_count, release_date
    | SORT page_count DESC
    | LIMIT 5
  """,
  "columnar": true
}

Which returns:

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

Returning localized resultsedit

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 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:

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

Passing parameters to a queryedit

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

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

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:

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