Columnar results

The most well known way of displaying the results of an SQL query result in general is the one where each individual record/document represents one line/row. For certain formats, Elasticsearch SQL can return the results in a columnar fashion: one row represents all the values of a certain column from the current page of results.

The following formats can be returned in columnar orientation: json, yaml, cbor and smile.

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

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.   "values": [
  9.     ["Peter F. Hamilton", "Vernor Vinge", "Frank Herbert", "Alastair Reynolds", "James S.A. Corey"],
  10.     ["Pandora's Star", "A Fire Upon the Deep", "Dune", "Revelation Space", "Leviathan Wakes"],
  11.     [768, 613, 604, 585, 561],
  12.     ["2004-03-02T00:00:00.000Z", "1992-06-01T00:00:00.000Z", "1965-06-01T00:00:00.000Z", "2000-03-15T00:00:00.000Z", "2011-06-02T00:00:00.000Z"]
  13.   ],
  14.   "cursor": "sDXF1ZXJ5QW5kRmV0Y2gBAAAAAAAAAAEWWWdrRlVfSS1TbDYtcW9lc1FJNmlYdw==:BAFmBmF1dGhvcgFmBG5hbWUBZgpwYWdlX2NvdW50AWYMcmVsZWFzZV9kYXRl+v///w8="
  15. }

Any subsequent calls using a cursor still have to contain the columnar parameter to preserve the orientation, meaning the initial query will not remember the columnar option.

  1. POST /_sql?format=json
  2. {
  3.   "cursor": "sDXF1ZXJ5QW5kRmV0Y2gBAAAAAAAAAAEWWWdrRlVfSS1TbDYtcW9lc1FJNmlYdw==:BAFmBmF1dGhvcgFmBG5hbWUBZgpwYWdlX2NvdW50AWYMcmVsZWFzZV9kYXRl+v///w8=",
  4.   "columnar": true
  5. }

Which looks like:

  1. {
  2.   "values": [
  3.     ["Dan Simmons", "Iain M. Banks", "Neal Stephenson", "Frank Herbert", "Frank Herbert"],
  4.     ["Hyperion", "Consider Phlebas", "Snow Crash", "God Emperor of Dune", "Children of Dune"],
  5.     [482, 471, 470, 454, 408],
  6.     ["1989-05-26T00:00:00.000Z", "1987-04-23T00:00:00.000Z", "1992-06-01T00:00:00.000Z", "1981-05-28T00:00:00.000Z", "1976-04-21T00:00:00.000Z"]
  7.   ],
  8.   "cursor": "46ToAwFzQERYRjFaWEo1UVc1a1JtVjBZMmdCQUFBQUFBQUFBQUVXWjBaNlFXbzNOV0pVY21Wa1NUZDJhV2t3V2xwblp3PT3/////DwQBZgZhdXRob3IBBHRleHQAAAFmBG5hbWUBBHRleHQAAAFmCnBhZ2VfY291bnQBBGxvbmcBAAFmDHJlbGVhc2VfZGF0ZQEIZGF0ZXRpbWUBAAEP"
  9. }