Get SQL search results Generally available; Added in 6.3.0

GET /_sql
Api key auth Basic auth Bearer auth

All methods and paths for this operation:

POST /_sql

GET /_sql

Run an SQL request.

Required authorization

  • Index privileges: read

Query parameters

  • format string

    The format for the response. 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.

    Values are csv, json, tsv, txt, yaml, cbor, or smile.

application/json

Body Required

  • allow_partial_search_results boolean

    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.

    Default value is false.

  • catalog string

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

  • columnar boolean

    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.

    Default value is false.

    External documentation
  • 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

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

    Default value is 1000.

  • field_multi_value_leniency boolean

    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.

    Default value is false.

  • filter object

    The Elasticsearch query DSL for additional filtering.

    External documentation
  • index_using_frozen boolean

    If true, the search can run on frozen indices.

    Default value is false.

  • keep_alive string

    The retention period for an async or saved synchronous search.

  • keep_on_completion boolean

    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.

    Default value is false.

  • page_timeout string

    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 array[object]

    The values for parameters in the query.

  • query string

    The SQL query to run.

    External documentation
  • request_timeout string

    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.

    Hide runtime_mappings attribute Show runtime_mappings attribute object
    • * object Additional properties
      Hide * attributes Show * attributes object
      • fields object

        For type composite

        Hide fields attribute Show fields attribute object
        • * object Additional properties
          Hide * attribute Show * attribute object
          • type string Required

            Values are boolean, composite, date, double, geo_point, geo_shape, ip, keyword, long, or lookup.

      • fetch_fields array[object]

        For type lookup

        Hide fetch_fields attributes Show fetch_fields attributes object
        • field string Required

          Path to field or array of paths. Some API's support wildcards in the path to select multiple fields.

        • format string
      • format string

        A custom format for date type runtime fields.

      • input_field string

        For type lookup

      • target_field string

        For type lookup

      • target_index string

        For type lookup

      • script object

        Painless script executed at query time.

        Hide script attributes Show script attributes object
        • source
        • id string

          The id for a stored script.

        • params object

          Specifies any named parameters that are passed into the script as variables. Use parameters instead of hard-coded values to decrease compile time.

          Hide params attribute Show params attribute object
          • * object Additional properties
        • lang
        • options object
          Hide options attribute Show options attribute object
          • * string Additional properties
      • type string Required

        Field type, which can be: boolean, composite, date, double, geo_point, ip,keyword, long, or lookup.

        Values are boolean, composite, date, double, geo_point, geo_shape, ip, keyword, long, or lookup.

  • time_zone string

    The ISO-8601 time zone ID for the search.

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

Responses

  • 200 application/json
    Hide response attributes Show response attributes object
    • columns array[object]

      Column headings for the search results. Each object is a column.

      Hide columns attributes Show columns attributes object
      • name string Required
      • type string Required
    • cursor string

      The cursor for the next set of paginated results. For CSV, TSV, and TXT responses, this value is returned in the Cursor HTTP header.

    • id string

      The identifier for the search. This value is returned only 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. If false, the search has finished. This value is returned only for async and saved synchronous searches. For CSV, TSV, and TXT responses, this value is returned in the Async-partial HTTP header.

    • is_partial boolean

      If true, the response does not contain complete search results. If is_partial is true and is_running is true, the search is still running. If is_partial is true but is_running is false, the results are partial due to a failure or timeout. This value is returned only for async and saved synchronous searches. For CSV, TSV, and TXT responses, this value is returned in the Async-partial HTTP header.

    • rows array[array] Required

      The values for the search results.
GET /_sql 
POST _sql?format=txt
{
  "query": "SELECT * FROM library ORDER BY page_count DESC LIMIT 5"
}
resp = client.sql.query(
    format="txt",
    query="SELECT * FROM library ORDER BY page_count DESC LIMIT 5",
)
const response = await client.sql.query({
  format: "txt",
  query: "SELECT * FROM library ORDER BY page_count DESC LIMIT 5",
});
response = client.sql.query(
  format: "txt",
  body: {
    "query": "SELECT * FROM library ORDER BY page_count DESC LIMIT 5"
  }
)
$resp = $client->sql()->query([
    "format" => "txt",
    "body" => [
        "query" => "SELECT * FROM library ORDER BY page_count DESC LIMIT 5",
    ],
]);
curl -X POST -H "Authorization: ApiKey $ELASTIC_API_KEY" -H "Content-Type: application/json" -d '{"query":"SELECT * FROM library ORDER BY page_count DESC LIMIT 5"}' "$ELASTICSEARCH_URL/_sql?format=txt"
client.sql().query(q -> q
    .format(SqlFormat.Txt)
    .query("SELECT * FROM library ORDER BY page_count DESC LIMIT 5")
);
Request example
Run `POST _sql?format=txt` to get results for an SQL search. 
{
  "query": "SELECT * FROM library ORDER BY page_count DESC LIMIT 5"
}