SQL search APIedit

Returns results for an SQL search.

POST _sql?format=txt
  "query": "SELECT * FROM library ORDER BY page_count DESC LIMIT 5"


GET _sql

POST _sql


  • If the Elasticsearch security features are enabled, you must have the read index privilege for the data stream, index, or alias you search.


See SQL Limitations.

Query parametersedit

(Optional, string) Separator for CSV results. Defaults to ,. The API only supports this parameter for CSV responses.

(Optional, string) Format for the response. For valid values, see Response Data Formats.

You can also specify a format using the Accept HTTP header. If you specify both this parameter and the Accept HTTP header, this parameter takes precedence.

Request bodyedit

(Optional, Boolean) If true, returns results in a columnar format. Defaults to false. The API only supports this parameter for CBOR, JSON, SMILE, and YAML responses. See Columnar results.
(Optional, string) Cursor used to retrieve a set of paginated results. If you specify a cursor, the API only uses the columnar and time_zone request body parameters. It ignores other request body parameters.
(Optional, integer) Maximum number of rows to return in the response. Defaults to 1000.
(Optional, Boolean) If false, the API returns an error for fields containing array values. If true, the API returns the first value from the array with no guarantee of consistent results. Defaults to false.
(Optional, object) Query DSL used to filter documents for the SQL search. See Filtering using Elasticsearch Query DSL.
(Optional, Boolean) If true, the search can run on frozen indices. Defaults to false.
(Optional, time value) Timeout before a pagination request fails. Defaults to 45s (45 seconds).
(Optional, array) Values for parameters in the query. For syntax, see Passing parameters to a query.
(Required, object) SQL query to run. For syntax, see SQL Language.
(Optional, time value) Timeout before the request fails. Defaults to 90s (90 seconds).

(Optional, object of objects) Defines one or more runtime fields in the search request. These fields take precedence over mapped fields with the same name.

Properties of runtime_mappings objects

(Required, object) Configuration for the runtime field. The key is the field name.

Properties of <field-name>

(Required, string) Field type, which can be any of the following:

  • boolean
  • date
  • double
  • geo_point
  • ip
  • keyword
  • long

(Optional, string) Painless script executed at query time. The script has access to the entire context of a document, including the original _source and any mapped fields plus their values.

This script must include emit to return calculated values. For example:

"script": "emit(doc['@timestamp'].value.dayOfWeekEnum.toString())"
(Optional, string) ISO-8601 time zone ID for the search. Several SQL date/time functions use this time zone. Defaults to Z (UTC).

Response bodyedit

The SQL search API supports multiple response formats. Most response formats use a tabular layout. JSON responses contain the following properties:

(array of arrays) Values for the search results.

(array of objects) Column headings for the search results. Each object is a column.

Properties of columns objects
(string) Name of the column.
(string) Data type for the column.
(string) Cursor for the next set of paginated results. For CSV, TSV, and TXT responses, this value is returned in the Cursor HTTP header.