IMPORTANT: No additional bug fixes or documentation updates will be released for this version. For the latest information, see the current release documentation.

« Overview Paginating through a large response »

› › ›

Response Data Formats

edit

Response Data Formats

edit

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:

CSV

edit

response = 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

edit

response = 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

edit

response = 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

edit

response = 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

edit

response = 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="

« Overview Paginating through a large response »