Get SQL search results

POST also GET /_sql

Run an SQL request.

Authentication

Api key

Authorization: <value>

Elasticsearch APIs support key-based authentication. You must create an API key and use the encoded value in the request header. For example:

		curl -X GET "${ES_URL}/_cat/indices?v=true" \
  -H "Authorization: ApiKey ${API_KEY}"

To get API keys, use the /_security/api_key APIs.

Basic

Authorization: Basic <credentials>

Bearer

Authorization: Bearer <token>

Elasticsearch APIs support the use of bearer tokens in the Authorization HTTP header to authenticate with the API. For examples, refer to Token-based authentication services

Request body

allow_partial_search_results boolean · default: false

If true, the response has partial results when there are shard request timeouts or shard failures. If false, the API returns an error with no partial results.

catalog string

The default catalog (cluster) for queries. If unspecified, the queries execute on the data in the local cluster only.

columnar boolean · default: false

If true, the results are in a columnar fashion: one row represents all the values of a certain column from the current page of results. The API supports this parameter only for CBOR, JSON, SMILE, and YAML responses.

Reference

cursor string

The 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 number · default: 1000

The maximum number of rows (or entries) to return in one response.

field_multi_value_leniency boolean · default: false

If false, the API returns an exception when encountering multiple values for a field. If true, the API is lenient and returns the first value from the array with no guarantee of consistent results.

filter {} Query · default: none

The Elasticsearch query DSL for additional filtering.

index_using_frozen boolean · default: false

If true, the search can run on frozen indices.

keep_alive string · default: 5d

The retention period for an async or saved synchronous search.

keep_on_completion boolean · default: false

If true, Elasticsearch stores synchronous searches if you also specify the wait_for_completion_timeout parameter. If false, Elasticsearch only stores async searches that don't finish before the wait_for_completion_timeout.

page_timeout string · default: 45s

The 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.

params object[]

The values for parameters in the query.

query string

The SQL query to run.

Reference

request_timeout string · default: 90s

The timeout before the request fails.

runtime_mappings object

One or more runtime fields for the search request. These fields take precedence over mapped fields with the same name.

time_zone string · default: Z

The ISO-8601 time zone ID for the search.

External docs

wait_for_completion_timeout string

The period to wait for complete results. It 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

application/json

Examples

Request Console 1 / 8 languages

Run POST _sql?format=txt to get results for an SQL search.

Console Kibana Dev Tools

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

JavaScript Elasticsearch JavaScript Client

const response = await client.sql.query({
  format: "txt",
  query: "SELECT * FROM library ORDER BY page_count DESC LIMIT 5",
});

Python Elasticsearch Python Client

resp = client.sql.query(
    format="txt",
    query="SELECT * FROM library ORDER BY page_count DESC LIMIT 5",
)

curl Shell

curl -X POST "$ELASTICSEARCH_URL/_sql?format=txt" \
  -H "Authorization: ApiKey $ELASTIC_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT * FROM library ORDER BY page_count DESC LIMIT 5"
  }'

Java Elasticsearch Java Client

client.sql().query(q -> q
    .format(SqlFormat.Txt)
    .query("""
        SELECT * FROM library ORDER BY page_count DESC LIMIT 5
        """)
);

C# Elasticsearch .NET Client

var response = await client.Sql
    .QueryAsync(d1 => d1
        .Format(SqlFormat.Txt)
        .Query("SELECT * FROM library ORDER BY page_count DESC LIMIT 5")
    );

PHP Elasticsearch PHP Client

$resp = $client->sql()->query([
    "format" => "txt",
    "body" => [
        "query" => "SELECT * FROM library ORDER BY page_count DESC LIMIT 5",
    ],
]);

Ruby Elasticsearch Ruby Client

response = client.sql.query(
  format: "txt",
  body: {
    "query": "SELECT * FROM library ORDER BY page_count DESC LIMIT 5"
  }
)

Response

No example

Get SQL search results

Required authorization

Authentication

Query parameters

Request body

Response

Required authorization

Authentication

Query parameters

Request body { }

Response

Request body