IMPORTANT: No additional bug fixes or documentation updates will be released for this version. For the latest information, see the current release documentation.
« Clear SQL cursor API SQL translate API »
Elastic Docs ›Elasticsearch Guide [7.13] ›REST APIs ›SQL APIs

SQL search API

edit

SQL search API

edit

Returns results for an SQL search.

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

Request

edit

GET _sql

POST _sql

Prerequisites

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

Limitations

edit

See SQL Limitations.

Query parameters

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

(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 body

edit
columnar
(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.
cursor
(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.
fetch_size
(Optional, integer) Maximum number of rows to return in the response. Defaults to 1000.
field_multi_value_leniency
(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.
filter
(Optional, object) Query DSL used to filter documents for the SQL search. See Filtering using Elasticsearch Query DSL.
index_include_frozen
(Optional, Boolean) If true, the search can run on frozen indices. Defaults to false.
page_timeout
(Optional, time value) Timeout before a pagination request fails. Defaults to 45s (45 seconds).
params
(Optional, array) Values for parameters in the query. For syntax, see Passing parameters to a query.
query
(Required, object) SQL query to run. For syntax, see SQL Language.
request_timeout
(Optional, time value) Timeout before the request fails. Defaults to 90s (90 seconds).
runtime_mappings

(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
<field-name>

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

Properties of <field-name>
type

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

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

(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())"
time_zone
(Optional, string) ISO-8601 time zone ID for the search. Several SQL date/time functions use this time zone. Defaults to Z (UTC).

Response body

edit

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

rows
(array of arrays) Values for the search results.
columns

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

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