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

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

Here are some examples for the human readable formats:

CSV

  1. POST /_sql?format=csv
  2. {
  3.   "query": "SELECT * FROM library ORDER BY page_count DESC",
  4.   "fetch_size": 5
  5. }

Which returns:

  1. author,name,page_count,release_date
  2. Peter F. Hamilton,Pandora's Star,768,2004-03-02T00:00:00.000Z
  3. Vernor Vinge,A Fire Upon the Deep,613,1992-06-01T00:00:00.000Z
  4. Frank Herbert,Dune,604,1965-06-01T00:00:00.000Z
  5. Alastair Reynolds,Revelation Space,585,2000-03-15T00:00:00.000Z
  6. James S.A. Corey,Leviathan Wakes,561,2011-06-02T00:00:00.000Z

JSON

  1. POST /_sql?format=json
  2. {
  3.   "query": "SELECT * FROM library ORDER BY page_count DESC",
  4.   "fetch_size": 5
  5. }

Which returns:

  1. {
  2.   "columns": [
  3.     {"name": "author",       "type": "text"},
  4.     {"name": "name",         "type": "text"},
  5.     {"name": "page_count",   "type": "short"},
  6.     {"name": "release_date", "type": "datetime"}
  7.   ],
  8.   "rows": [
  9.     ["Peter F. Hamilton",  "Pandora's Star",       768, "2004-03-02T00:00:00.000Z"],
  10.     ["Vernor Vinge",       "A Fire Upon the Deep", 613, "1992-06-01T00:00:00.000Z"],
  11.     ["Frank Herbert",      "Dune",                 604, "1965-06-01T00:00:00.000Z"],
  12.     ["Alastair Reynolds",  "Revelation Space",     585, "2000-03-15T00:00:00.000Z"],
  13.     ["James S.A. Corey",   "Leviathan Wakes",      561, "2011-06-02T00:00:00.000Z"]
  14.   ],
  15.   "cursor": "sDXF1ZXJ5QW5kRmV0Y2gBAAAAAAAAAAEWWWdrRlVfSS1TbDYtcW9lc1FJNmlYdw==:BAFmBmF1dGhvcgFmBG5hbWUBZgpwYWdlX2NvdW50AWYMcmVsZWFzZV9kYXRl+v///w8="
  16. }

TSV

  1. POST /_sql?format=tsv
  2. {
  3.   "query": "SELECT * FROM library ORDER BY page_count DESC",
  4.   "fetch_size": 5
  5. }

Which returns:

  1. author    name    page_count    release_date
  2. Peter F. Hamilton    Pandora's Star    768    2004-03-02T00:00:00.000Z
  3. Vernor Vinge    A Fire Upon the Deep    613    1992-06-01T00:00:00.000Z
  4. Frank Herbert    Dune    604    1965-06-01T00:00:00.000Z
  5. Alastair Reynolds    Revelation Space    585    2000-03-15T00:00:00.000Z
  6. James S.A. Corey    Leviathan Wakes    561    2011-06-02T00:00:00.000Z

TXT

  1. POST /_sql?format=txt
  2. {
  3.   "query": "SELECT * FROM library ORDER BY page_count DESC",
  4.   "fetch_size": 5
  5. }

Which returns:

  1.      author      |        name        |  page_count   |      release_date
  2. -----------------+--------------------+---------------+------------------------
  3. Peter F. Hamilton|Pandora's Star      |768            |2004-03-02T00:00:00.000Z
  4. Vernor Vinge     |A Fire Upon the Deep|613            |1992-06-01T00:00:00.000Z
  5. Frank Herbert    |Dune                |604            |1965-06-01T00:00:00.000Z
  6. Alastair Reynolds|Revelation Space    |585            |2000-03-15T00:00:00.000Z
  7. James S.A. Corey |Leviathan Wakes     |561            |2011-06-02T00:00:00.000Z

YAML

  1. POST /_sql?format=yaml
  2. {
  3.   "query": "SELECT * FROM library ORDER BY page_count DESC",
  4.   "fetch_size": 5
  5. }

Which returns:

  1. columns:
  2. - name: "author"
  3.   type: "text"
  4. - name: "name"
  5.   type: "text"
  6. - name: "page_count"
  7.   type: "short"
  8. - name: "release_date"
  9.   type: "datetime"
  10. rows:
  11. - - "Peter F. Hamilton"
  12.   - "Pandora's Star"
  13.   - 768
  14.   - "2004-03-02T00:00:00.000Z"
  15. - - "Vernor Vinge"
  16.   - "A Fire Upon the Deep"
  17.   - 613
  18.   - "1992-06-01T00:00:00.000Z"
  19. - - "Frank Herbert"
  20.   - "Dune"
  21.   - 604
  22.   - "1965-06-01T00:00:00.000Z"
  23. - - "Alastair Reynolds"
  24.   - "Revelation Space"
  25.   - 585
  26.   - "2000-03-15T00:00:00.000Z"
  27. - - "James S.A. Corey"
  28.   - "Leviathan Wakes"
  29.   - 561
  30.   - "2011-06-02T00:00:00.000Z"
  31. cursor: "sDXF1ZXJ5QW5kRmV0Y2gBAAAAAAAAAAEWWWdrRlVfSS1TbDYtcW9lc1FJNmlYdw==:BAFmBmF1dGhvcgFmBG5hbWUBZgpwYWdlX2NvdW50AWYMcmVsZWFzZV9kYXRl+v///w8="