SQL REST API - レスポンスデータフォーマット（Response Data Formats）

レスポンスデータフォーマット
CSV
JSON
TSV
TXT
YAML

レスポンスデータフォーマット

テキスト形式は人間にとっては良いですが、コンピュータはより構造化されたものを好みます。

Elasticsearch SQLは、formatプロパティをURLで設定するか、Accept HTTPヘッダーを設定することで、以下のフォーマットでデータを返すことができます。

URLパラメータはAccept HTTPヘッダーよりも優先されます。どちらも指定されていない場合、レスポンスはリクエストと同じ形式で返されます。


フォーマット	`Accept` HTTPヘッダー	説明
人間可読
`csv`	`text/csv`	カンマ区切り値
`json`	`application/json`	JSON (JavaScriptオブジェクト表記) 人間可読形式
`tsv`	`text/tab-separated-values`	タブ区切り値
`txt`	`text/plain`	CLIライクな表現
`yaml`	`application/yaml`	YAML (YAMLはマークアップ言語ではない) 人間可読形式
バイナリフォーマット
`cbor`	`application/cbor`	簡潔なバイナリオブジェクト表現
`smile`	`application/smile`	Smile バイナリデータフォーマット、CBORに似ています

CSVフォーマットは、CSV値を区切るために使用する文字を示すフォーマットURLクエリ属性delimiterを受け入れます。デフォルトはカンマ(,)であり、次の値は使用できません: ダブルクォート(")、キャリッジリターン(\r)、および改行(\n)。タブ(\t)も使用できず、tsvフォーマットを代わりに使用する必要があります。

人間可読形式のいくつかの例を以下に示します:

CSV

Python

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

Ruby

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

Js

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

または:

Python

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

Ruby

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

Js

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

Python

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

Ruby

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

Js

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

Python

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

Ruby

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

Js

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

Python

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

Ruby

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

Js

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

Python

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

Ruby

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

Js

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
}

返される内容は:

Yaml

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