Get SQL search results | Elasticsearch API documentation (v9)

Query parameters

formatstring
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

BodyRequired

allow_partial_search_resultsboolean
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.
catalogstring
The default catalog (cluster) for queries. If unspecified, the queries execute on the data in the local cluster only.
columnarboolean
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.
External documentation
cursorstring
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_sizenumber
The maximum number of rows (or entries) to return in one response.
field_multi_value_leniencyboolean
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.
filterobject
An Elasticsearch Query DSL (Domain Specific Language) object that defines a query.
External documentation
index_using_frozenboolean
If true, the search can run on frozen indices.
keep_alivestring
A duration. Units can be nanos, micros, ms (milliseconds), s (seconds), m (minutes), h (hours) and d (days). Also accepts "0" without a unit and "-1" to indicate an unspecified value.
keep_on_completionboolean
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_timeoutstring
A duration. Units can be nanos, micros, ms (milliseconds), s (seconds), m (minutes), h (hours) and d (days). Also accepts "0" without a unit and "-1" to indicate an unspecified value.
paramsobject
The values for parameters in the query.
Hide params attribute Show params attribute object
- *object Additional properties
querystring
The SQL query to run.
External documentation
request_timeoutstring
A duration. Units can be nanos, micros, ms (milliseconds), s (seconds), m (minutes), h (hours) and d (days). Also accepts "0" without a unit and "-1" to indicate an unspecified value.
runtime_mappingsobject
Hide runtime_mappings attribute Show runtime_mappings attribute object
- *object Additional properties
  Hide * attributes Show * attributes object
  fieldsobject
  For type composite
  Hide fields attribute Show fields attribute object
  *object Additional properties
  Hide * attribute Show * attribute object
  typestring Required
  Values are boolean, composite, date, double, geo_point, geo_shape, ip, keyword, long, or lookup.
  fetch_fieldsarray[object]
  For type lookup
  Hide fetch_fields attributes Show fetch_fields attributes object
  fieldstring Required
  Path to field or array of paths. Some API's support wildcards in the path to select multiple fields.
  formatstring
  formatstring
  A custom format for date type runtime fields.
  input_fieldstring
  Path to field or array of paths. Some API's support wildcards in the path to select multiple fields.
  target_fieldstring
  Path to field or array of paths. Some API's support wildcards in the path to select multiple fields.
  target_indexstring
  scriptobject
  Hide script attributes Show script attributes object
  sourcestring | object
  One of:
  ScriptSourcestring SearchRequestBodyobject
  Hide attributes Show attributes
  aggregationsobject
  Defines the aggregations that are run as part of the search request.
  External documentation
  collapseobject
  External documentation
  explainboolean
  If true, the request returns detailed information about score computation as part of a hit.
  extobject
  Configuration of search extensions defined by Elasticsearch plugins.
  Hide ext attribute Show ext attribute object
  *object Additional properties
  fromnumber
  The starting document offset, which must be non-negative. By default, you cannot page through more than 10,000 hits using the from and size parameters. To page through more hits, use the search_after parameter.
  highlightobject
  track_total_hitsboolean | number
  Number of hits matching the query to count accurately. If true, the exact number of hits is returned at the cost of some performance. If false, the response does not include the total number of hits matching the query. Defaults to 10,000 hits.
  indices_boostarray[object]
  Boost the _score of documents from specified indices. The boost value is the factor by which scores are multiplied. A boost value greater than 1.0 increases the score. A boost value between 0 and 1.0 decreases the score.
  External documentation
  docvalue_fieldsarray[object]
  An array of wildcard (*) field patterns. The request returns doc values for field names matching these patterns in the hits.fields property of the response.
  External documentation
  knnobject | array[object]
  The approximate kNN search to run.
  One of:
  KnnSearchobject array-2array[object]
  rankobject
  Hide rank attribute Show rank attribute object
  rrf
  min_scorenumber
  The minimum _score for matching documents. Documents with a lower _score are not included in search results or results collected by aggregations.
  post_filterobject
  An Elasticsearch Query DSL (Domain Specific Language) object that defines a query.
  External documentation
  profileboolean
  Set to true to return detailed timing information about the execution of individual components in a search request. NOTE: This is a debugging tool and adds significant overhead to search execution.
  queryobject
  An Elasticsearch Query DSL (Domain Specific Language) object that defines a query.
  External documentation
  rescorearray[object]
  retrieverobject
  Hide retriever attributes Show retriever attributes object
  standard
  knn
  rrf
  text_similarity_reranker
  rule
  rescorer
  linear
  pinned
  script_fieldsobject
  Retrieve a script evaluation (based on different fields) for each hit.
  Hide script_fields attribute Show script_fields attribute object
  *object Additional properties
  search_afterarray[number | string | boolean | null]
  A field value.
  sizenumber
  The number of hits to return, which must not be negative. By default, you cannot page through more than 10,000 hits using the from and size parameters. To page through more hits, use the search_after property.
  sliceobject
  Hide slice attributes Show slice attributes object
  fieldstring
  Path to field or array of paths. Some API's support wildcards in the path to select multiple fields.
  idstring Required
  maxnumber Required
  sortarray[string | object]
  _sourceboolean | object
  Defines how to fetch a source. Fetching can be disabled entirely, or the source can be filtered.
  One of:
  SourceConfigboolean SourceFilterobject
  fieldsarray[object]
  An array of wildcard (*) field patterns. The request returns values for field names matching these patterns in the hits.fields property of the response.
  suggestobject
  Hide suggest attribute Show suggest attribute object
  textstring
  Global suggest text, to avoid repetition when the same text is used in several suggesters
  terminate_afternumber
  The maximum number of documents to collect for each shard. If a query reaches this limit, Elasticsearch terminates the query early. Elasticsearch collects documents before sorting.
  IMPORTANT: Use with caution. Elasticsearch applies this property to each shard handling the request. When possible, let Elasticsearch perform early termination automatically. Avoid specifying this property for requests that target data streams with backing indices across multiple data tiers.
  If set to 0 (default), the query does not terminate early.
  timeoutstring
  The period of time to wait for a response from each shard. If no response is received before the timeout expires, the request fails and returns an error. Defaults to no timeout.
  track_scoresboolean
  If true, calculate and return document scores, even if the scores are not used for sorting.
  versionboolean
  If true, the request returns the document version as part of a hit.
  seq_no_primary_termboolean
  If true, the request returns sequence number and primary term of the last modification of each hit.
  External documentation
  stored_fieldsstring | array[string]
  pitobject
  Hide pit attributes Show pit attributes object
  idstring Required
  keep_alivestring
  A duration. Units can be nanos, micros, ms (milliseconds), s (seconds), m (minutes), h (hours) and d (days). Also accepts "0" without a unit and "-1" to indicate an unspecified value.
  runtime_mappingsobject
  statsarray[string]
  The stats groups to associate with the search. Each group maintains a statistics aggregation for its associated searches. You can retrieve these stats using the indices stats API.
  idstring
  paramsobject
  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
  langstring
  Any of:
  ScriptLanguagestring ScriptLanguagestring
  Values are painless, expression, mustache, or java.
  optionsobject
  Hide options attribute Show options attribute object
  *string Additional properties
  typestring Required
  Values are boolean, composite, date, double, geo_point, geo_shape, ip, keyword, long, or lookup.
time_zonestring
wait_for_completion_timeoutstring
A duration. Units can be nanos, micros, ms (milliseconds), s (seconds), m (minutes), h (hours) and d (days). Also accepts "0" without a unit and "-1" to indicate an unspecified value.

Responses

200 application/json
Hide response attributes Show response attributes object
- columnsarray[object]
  Column headings for the search results. Each object is a column.
  Hide columns attributes Show columns attributes object
  namestring Required
  typestring Required
- cursorstring
  The cursor for the next set of paginated results. For CSV, TSV, and TXT responses, this value is returned in the Cursor HTTP header.
- idstring
- is_runningboolean
  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_partialboolean
  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.
- rowsarray[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"
}

curl \
 --request GET 'http://api.example.com/_sql' \
 --header "Authorization: $API_KEY" \
 --header "Content-Type: application/json" \
 --data '"{\n  \"query\": \"SELECT * FROM library ORDER BY page_count DESC LIMIT 5\"\n}"'

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"
}

Query parameters

BodyRequired

sourcestring | object

langstring

Responses