Response data formats
Elastic Cloud Serverless projects are autoscaled environments, fully managed by Elastic and available on Elastic Cloud. Availability This functionality is generally available and ready for production usage. Serverless interfaces and procedures might differ from classic Elastic Stack deployments. The Elastic Stack includes Elastic's core products such as Elasticsearch, Kibana, Logstash, and Beats. Availability This functionality is generally available and ready for production usage. Unless stated otherwise on the page, this functionality is available when your Elastic Stack is deployed on Elastic Cloud Hosted, Elastic Cloud Enterprise, Elastic Cloud on Kubernetes, and self-managed environments. This documentation corresponds to the latest patch available for each minor version. If you're not using the latest patch, check the release notes for changes.
Serverless
Generally available
Stack
Generally available
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 | Accept HTTP header |
Description |
|---|---|---|
| Human Readable | ||
csv |
text/csv |
Comma-separated values |
json |
application/json |
JSON (JavaScript Object Notation) human-readable format |
tsv |
text/tab-separated-values |
Tab-separated values |
txt |
text/plain |
CLI-like representation |
yaml |
application/yaml |
YAML (YAML Ain’t Markup Language) human-readable format |
| Binary Formats | ||
cbor |
application/cbor |
Concise Binary Object Representation |
smile |
application/smile |
Smile binary data format similar to CBOR |
The CSV format accepts a formatting URL query attribute, delimiter, which indicates which character should be used to separate the CSV values. It defaults to comma (,) and cannot take any of the following values: double quote ("), carriage-return (\r) and new-line (\n). The tab (\t) can also not be used, the tsv format needs to be used instead.
Here are some examples for the human readable formats:
POST /_sql?format=csv{ "query": "SELECT * FROM library ORDER BY page_count DESC", "fetch_size": 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
or:
POST /_sql?format=csv&delimiter=%3b{ "query": "SELECT * FROM library ORDER BY page_count DESC", "fetch_size": 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
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="
}
POST /_sql?format=tsv{ "query": "SELECT * FROM library ORDER BY page_count DESC", "fetch_size": 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
POST /_sql?format=txt{ "query": "SELECT * FROM library ORDER BY page_count DESC", "fetch_size": 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
POST /_sql?format=yaml{ "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="