SQL search API
editSQL search API
editReturns results for an SQL search.
POST _sql?format=txt { "query": "SELECT * FROM library ORDER BY page_count DESC LIMIT 5" }
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
editSee 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 theAccept
HTTP header, this parameter takes precedence.
Request body
edit-
catalog
-
(Optional, string) Default catalog (cluster) for queries. If unspecified, the queries execute on the data in the local cluster only.
[preview] This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features. See cross-cluster search.
-
columnar
-
(Optional, Boolean) If
true
, returns results in a columnar format. Defaults tofalse
. 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 thecolumnar
andtime_zone
request body parameters. It ignores other request body parameters.
-
field_multi_value_leniency
-
(Optional, Boolean) If
false
, the API returns an error for fields containing array values. Iftrue
, the API returns the first value from the array with no guarantee of consistent results. Defaults tofalse
. -
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 tofalse
. -
keep_alive
-
(Optional, time value) Retention period for an
async or saved synchronous search. Defaults
to
5d
(five days). -
keep_on_completion
-
(Optional, Boolean) If
true
, Elasticsearch stores synchronous searches if you also specify thewait_for_completion_timeout
parameter. Iffalse
, Elasticsearch only stores async searches that don’t finish before thewait_for_completion_timeout
. Defaults tofalse
. -
page_timeout
-
(Optional, time value) Minimum retention period for the scroll
cursor. After this time period, a pagination request might
fail because the scroll cursor is no longer available. Subsequent scroll requests
prolong the lifetime of the scroll cursor by the duration of
page_timeout
in the scroll request. Defaults to45s
(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
-
composite
-
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). -
wait_for_completion_timeout
-
(Optional, time value) Period to wait for complete results. Defaults to no timeout, meaning the request waits for complete search results. If the search doesn’t finish within this period, the search becomes async.
To save a synchronous search, you must specify this parameter and the
keep_on_completion
parameter.
Response body
editThe SQL search API supports multiple response formats. Most response formats use a tabular layout. JSON responses contain the following properties:
-
id
-
(string) Identifier for the search. This value is only returned for
async and saved synchronous searches. For
CSV, TSV, and TXT responses, this value is returned in the
Async-ID
HTTP header. -
is_running
-
(Boolean) If
true
, the search is still running. Iffalse
, the search has finished. This value is only returned for async and saved synchronous searches. For CSV, TSV, and TXT responses, this value is returned in theAsync-partial
HTTP header. -
is_partial
-
(Boolean) If
true
, the response does not contain complete search results. Ifis_partial
istrue
andis_running
istrue
, the search is still running. Ifis_partial
istrue
butis_running
isfalse
, the results are partial due to a failure or timeout.This value is only returned for async and saved synchronous searches. For CSV, TSV, and TXT responses, this value is returned in the
Async-partial
HTTP header. -
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.