The SQL REST API accepts SQL in a JSON document, executes it, and returns the results. For example:
POST /_sql?format=txt
{
"query": "SELECT * FROM library ORDER BY page_count DESC LIMIT 5"
}Which returns:
author | name | page_count | release_date -----------------+--------------------+---------------+------------------------ Peter F. Hamilton|Pandora's Star |768 |2004-03-02T00:00:00.000Z Vernor Vinge |A Fire Upon the Deep|613 |1992-06-01T00:00:00.000Z Frank Herbert |Dune |604 |1965-06-01T00:00:00.000Z Alastair Reynolds|Revelation Space |585 |2000-03-15T00:00:00.000Z James S.A. Corey |Leviathan Wakes |561 |2011-06-02T00:00:00.000Z
Using Kibana Console. If you are using Kibana Console.
(which is highly recommended), take advantage of the
triple quotes """ when creating the query. This not only automatically escapes double
quotes (") inside the query string but also support multi-line as shown below:
Response Data Formats
While the textual format is nice for humans, computers prefer something more structured.
Elasticsearch SQL can return the data in the following formats which can be set
either through the format property in the URL or by setting the Accept HTTP header:
The URL parameter takes precedence over the Accept HTTP header.
If neither is specified then the response is returned in the same format as the request.
format |
| Description |
Human Readable | ||
|
| |
|
| JSON (JavaScript Object Notation) human-readable format |
|
| |
|
| CLI-like representation |
|
| YAML (YAML Ain’t Markup Language) human-readable format |
Binary Formats | ||
|
| |
|
| Smile binary data format similar to CBOR |
POST /_sql?format=json
{
"query": "SELECT * FROM library ORDER BY page_count DESC",
"fetch_size": 5
}Which returns:
{
"columns": [
{"name": "author", "type": "text"},
{"name": "name", "type": "text"},
{"name": "page_count", "type": "short"},
{"name": "release_date", "type": "datetime"}
],
"rows": [
["Peter F. Hamilton", "Pandora's Star", 768, "2004-03-02T00:00:00.000Z"],
["Vernor Vinge", "A Fire Upon the Deep", 613, "1992-06-01T00:00:00.000Z"],
["Frank Herbert", "Dune", 604, "1965-06-01T00:00:00.000Z"],
["Alastair Reynolds", "Revelation Space", 585, "2000-03-15T00:00:00.000Z"],
["James S.A. Corey", "Leviathan Wakes", 561, "2011-06-02T00:00:00.000Z"]
],
"cursor": "sDXF1ZXJ5QW5kRmV0Y2gBAAAAAAAAAAEWWWdrRlVfSS1TbDYtcW9lc1FJNmlYdw==:BAFmBmF1dGhvcgFmBG5hbWUBZgpwYWdlX2NvdW50AWYMcmVsZWFzZV9kYXRl+v///w8="
}Paginating through a large response
Using the example above, onu can continue to the next page by sending back the cursor field. In
case of text format the cursor is returned as Cursor http header.
POST /_sql?format=json
{
"cursor": "sDXF1ZXJ5QW5kRmV0Y2gBAAAAAAAAAAEWYUpOYklQMHhRUEtld3RsNnFtYU1hQQ==:BAFmBGRhdGUBZgVsaWtlcwFzB21lc3NhZ2UBZgR1c2Vy9f///w8="
}Which looks like:
{
"rows" : [
["Dan Simmons", "Hyperion", 482, "1989-05-26T00:00:00.000Z"],
["Iain M. Banks", "Consider Phlebas", 471, "1987-04-23T00:00:00.000Z"],
["Neal Stephenson", "Snow Crash", 470, "1992-06-01T00:00:00.000Z"],
["Frank Herbert", "God Emperor of Dune", 454, "1981-05-28T00:00:00.000Z"],
["Frank Herbert", "Children of Dune", 408, "1976-04-21T00:00:00.000Z"]
],
"cursor" : "sDXF1ZXJ5QW5kRmV0Y2gBAAAAAAAAAAEWODRMaXBUaVlRN21iTlRyWHZWYUdrdw==:BAFmBmF1dGhvcgFmBG5hbWUBZgpwYWdlX2NvdW50AWYMcmVsZWFzZV9kYXRl9f///w8="
}Note that the columns object is only part of the first page.
You’ve reached the last page when there is no cursor returned
in the results. Like Elasticsearch’s scroll,
SQL may keep state in Elasticsearch to support the cursor. Unlike
scroll, receiving the last page is enough to guarantee that the
Elasticsearch state is cleared.
To clear the state earlier, you can use the clear cursor command:
POST /_sql/close
{
"cursor": "sDXF1ZXJ5QW5kRmV0Y2gBAAAAAAAAAAEWYUpOYklQMHhRUEtld3RsNnFtYU1hQQ==:BAFmBGRhdGUBZgVsaWtlcwFzB21lc3NhZ2UBZgR1c2Vy9f///w8="
}Which will like return the
{
"succeeded" : true
}Filtering using Elasticsearch query DSL
You can filter the results that SQL will run on using a standard Elasticsearch query DSL by specifying the query in the filter parameter.
POST /_sql?format=txt
{
"query": "SELECT * FROM library ORDER BY page_count DESC",
"filter": {
"range": {
"page_count": {
"gte" : 100,
"lte" : 200
}
}
},
"fetch_size": 5
}Which returns:
author | name | page_count | release_date ---------------+------------------------------------+---------------+------------------------ Douglas Adams |The Hitchhiker's Guide to the Galaxy|180 |1979-10-12T00:00:00.000Z
Supported REST parameters
In addition to the query and fetch_size, a request a number of user-defined fields for specifying
the request time-outs or localization information (such as timezone).
The table below lists the supported parameters:
name | Default value | Description |
|
| SQL query to execute |
|
| The maximum number of rows (or entries) to return in one response |
|
| Optional Elasticsearch query DSL for additional filtering. |
|
| The timeout before the request fails. |
|
| The timeout before a pagination request fails. |
|
| Time-zone in ISO 8601 used for executing the query on the server. More information available here. |
|
| Throw an exception when encountering multiple values for a field (default) or be lenient and return the first value from the list (without any guarantees of what that will be - typically the first in natural ascending order). |
Do note that most parameters (outside the timeout ones) make sense only during the initial query - any follow-up pagination request only requires the cursor parameter as explained in the pagination chapter.
That’s because the query has already been executed and the calls are simply about returning the found results - thus the parameters are simply ignored.