Response Data Formats
editResponse Data Formats
editWhile 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 |
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:
CSV
editresponse = client.sql.query( format: 'csv', body: { query: 'SELECT * FROM library ORDER BY page_count DESC', fetch_size: 5 } ) puts response
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:
response = client.sql.query( format: 'csv', delimiter: ';', body: { query: 'SELECT * FROM library ORDER BY page_count DESC', fetch_size: 5 } ) puts response
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
JSON
editresponse = client.sql.query( format: 'json', body: { query: 'SELECT * FROM library ORDER BY page_count DESC', fetch_size: 5 } ) puts response
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=" }
TSV
editresponse = client.sql.query( format: 'tsv', body: { query: 'SELECT * FROM library ORDER BY page_count DESC', fetch_size: 5 } ) puts response
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
TXT
editresponse = client.sql.query( format: 'txt', body: { query: 'SELECT * FROM library ORDER BY page_count DESC', fetch_size: 5 } ) puts response
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
YAML
editresponse = client.sql.query( format: 'yaml', body: { query: 'SELECT * FROM library ORDER BY page_count DESC', fetch_size: 5 } ) puts response
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="