› › ›

Форматы данных ответа

Хотя текстовый формат удобен для людей, компьютеры предпочитают что-то более структурированное.

Elasticsearch SQL может возвращать данные в следующих форматах, которые можно задать либо через свойство format в URL, либо установив заголовок HTTP Accept:

Параметр URL имеет приоритет над заголовком HTTP Accept. Если ни один из них не указан, то ответ возвращается в том же формате, что и запрос.

формат	`Accept` заголовок HTTP	Описание
Читаемый человеком
`csv`	`text/csv`	CSV
`json`	`application/json`	JSON (JavaScript Object Notation) в читаемом человеком формате
`tsv`	`text/tab-separated-values`	TSV
`txt`	`text/plain`	Представление в стиле командной строки
`yaml`	`application/yaml`	YAML (YAML Ain’t Markup Language) в читаемом человеком формате
Бинарные форматы
`cbor`	`application/cbor`	Concise Binary Object Representation
`smile`	`application/smile`	Smile бинарный формат данных, аналогичный CBOR

Формат CSV принимает атрибут запроса форматирования, delimiter, который указывает, какой символ должен использоваться для разделения значений CSV. По умолчанию это запятая (,), и он не может принимать следующие значения: двойные кавычки ("), возврат каретки (\r) и перевод строки (\n). Также нельзя использовать табуляцию (\t), вместо этого необходимо использовать формат tsv.

Вот несколько примеров для форматов, читаемых человеком:

CSV

resp = client.sql.query(
    format="csv",
    query="SELECT * FROM library ORDER BY page_count DESC",
    fetch_size=5,
)
print(resp)

response = client.sql.query(
  format: 'csv',
  body: {
    query: 'SELECT * FROM library ORDER BY page_count DESC',
    fetch_size: 5
  }
)
puts response

const response = await client.sql.query({
  format: "csv",
  query: "SELECT * FROM library ORDER BY page_count DESC",
  fetch_size: 5,
});
console.log(response);

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

что возвращает:

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

или:

resp = client.sql.query(
    format="csv",
    delimiter=";",
    query="SELECT * FROM library ORDER BY page_count DESC",
    fetch_size=5,
)
print(resp)

response = client.sql.query(
  format: 'csv',
  delimiter: ';',
  body: {
    query: 'SELECT * FROM library ORDER BY page_count DESC',
    fetch_size: 5
  }
)
puts response

const response = await client.sql.query({
  format: "csv",
  delimiter: ";",
  query: "SELECT * FROM library ORDER BY page_count DESC",
  fetch_size: 5,
});
console.log(response);

POST /_sql?format=csv&delimiter=%3b
{
    "query": "SELECT * FROM library ORDER BY page_count DESC",
    "fetch_size": 5
}

что возвращает:

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

resp = client.sql.query(
    format="json",
    query="SELECT * FROM library ORDER BY page_count DESC",
    fetch_size=5,
)
print(resp)

response = client.sql.query(
  format: 'json',
  body: {
    query: 'SELECT * FROM library ORDER BY page_count DESC',
    fetch_size: 5
  }
)
puts response

const response = await client.sql.query({
  format: "json",
  query: "SELECT * FROM library ORDER BY page_count DESC",
  fetch_size: 5,
});
console.log(response);

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

Что возвращает:

{
  "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

resp = client.sql.query(
    format="tsv",
    query="SELECT * FROM library ORDER BY page_count DESC",
    fetch_size=5,
)
print(resp)

response = client.sql.query(
  format: 'tsv',
  body: {
    query: 'SELECT * FROM library ORDER BY page_count DESC',
    fetch_size: 5
  }
)
puts response

const response = await client.sql.query({
  format: "tsv",
  query: "SELECT * FROM library ORDER BY page_count DESC",
  fetch_size: 5,
});
console.log(response);

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

Что возвращает:

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

resp = client.sql.query(
    format="txt",
    query="SELECT * FROM library ORDER BY page_count DESC",
    fetch_size=5,
)
print(resp)

response = client.sql.query(
  format: 'txt',
  body: {
    query: 'SELECT * FROM library ORDER BY page_count DESC',
    fetch_size: 5
  }
)
puts response

const response = await client.sql.query({
  format: "txt",
  query: "SELECT * FROM library ORDER BY page_count DESC",
  fetch_size: 5,
});
console.log(response);

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

Что возвращает:

     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

resp = client.sql.query(
    format="yaml",
    query="SELECT * FROM library ORDER BY page_count DESC",
    fetch_size=5,
)
print(resp)

response = client.sql.query(
  format: 'yaml',
  body: {
    query: 'SELECT * FROM library ORDER BY page_count DESC',
    fetch_size: 5
  }
)
puts response

const response = await client.sql.query({
  format: "yaml",
  query: "SELECT * FROM library ORDER BY page_count DESC",
  fetch_size: 5,
});
console.log(response);

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

Что возвращает:

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

© 2023-2025 Elasticsearch
As of September 2024, Elasticsearch is available under a choice of three licenses: the Server Side Public License (SSPL), the Elastic License, or the AGPLv3 (OSI approved).
Elasticsearch and the Elasticsearch logo are trademarks of Elasticsearch B.V., registered in the U.S. and in other countries.
https://www.elastic.co/guide/en/elasticsearch/reference/8.17/sql-rest-format.html