Query via http

Query API endpoint is /db/query.

The query api does not support only “SELECT” but also “CREATE TABLE”, “ALTER TABLE”, “INSERT”… all other SQL statements.

The /db/query API supports GET, POST JSON and POST form-data. The all methods supports the same parameters.

For example the parameter format can be specified in query parameter in GET method like GET /db/query?format=csv, or be a JSON field in POST-JSON method as { "format": "csv" }.

Query Example

curl -o - http://127.0.0.1:5654/db/query \
     --data-urlencode "q=select * from EXAMPLE limit 2"

Parameters

Query Parameters

param	default	description
q	required	SQL query string
format	`json`	Result data format: json, csv, box, ndjson
timeformat	`ns`	Time format: s, ms, us, ns
tz	`UTC`	Time Zone: UTC, Local and location spec
compress	no compression	compression method: gzip
rownum	`false`	including rownum: true, false

Available parameters with format=json

The options are only available when format=json. Those options are exclusive each other, applicable only one of them per a request.

param	default	description
transpose	false	produce cols array instead of rows. Since v8.0.12
rowsFlatten	false	reduce the array dimension of the rows field in the JSON object. Since v8.0.12
rowsArray	false	produce JSON that contains only array of object for each record. Since v8.0.12

Available parameters with format=csv

param	default	description
heading	`true`	show header line: true, false
precision	`-1`	precision of float value, -1 for no round, 0 for integers

Available timeformat

Please refer to the API Options/timeformat section for the available time formats.

Outputs

If the response content is too large to determine the total length, The header Transfer-Encoding: chunked is set, and the Content-Length header is omitted. The end of the response is identified by the last two consecutive newline characters (\n\n).

Transfer-Encoding: chunked: Means the data is sent in a series of chunks, useful for streaming.
Absence of Content-Length: Indicates that the total length of the response body is not known in advance.

JSON

The /db/query api’s default output format is json. Set query param format=json or omit it for the default value.

curl -o - http://127.0.0.1:5654/db/query \
    --data-urlencode "q=select * from EXAMPLE limit 2"

curl -o - http://127.0.0.1:5654/db/query ^
    --data-urlencode "q=select * from EXAMPLE limit 2"

The server responses in Content-Type: application/json.

name	type	description
success	bool	`true` if query execution succeed
reason	string	execution result message, this will contains error message if `success` is `false`
elapse	string	elapse time of the query execution
data		exists only when execution succeed
data.columns	array of strings	represents columns of result
data.types	array of strings	represents data types of result
data.rows	array of records	array represents the result set records. This field will be replaced with `cols` if `transpose` is `true`
data.cols	array of series	array represents the result set column-series. This element exists when `transpose` is `true`

curl -o - http://127.0.0.1:5654/db/query \
      --data-urlencode "q=select * from EXAMPLE"

{
  "data": {
    "columns": [ "NAME", "TIME", "VALUE" ],
    "types": [ "string", "datetime", "double" ],
    "rows": [
      [ "wave.sin", 1705381958775759000, 0.8563571936170834 ],
      [ "wave.sin", 1705381958785759000, 0.9011510331449053 ],
      [ "wave.sin", 1705381958795759000, 0.9379488170706388 ]
    ]
  },
  "success": true,
  "reason": "success",
  "elapse": "1.887042ms"
}

curl -o - http://127.0.0.1:5654/db/query \
      --data-urlencode "q=select * from EXAMPLE" \
      --data-urlencode "transpose=true"

{
  "data": {
    "columns": [ "NAME", "TIME", "VALUE" ],
    "types": [ "string", "datetime", "double" ],
    "cols": [
      [ "wave.sin", "wave.sin", "wave.sin" ],
      [ 1705381958775759000, 1705381958785759000, 1705381958795759000 ],
      [ 0.8563571936170834, 0.9011510331449053, 0.9379488170706388 ]
    ]
  },
  "success": true,
  "reason": "success",
  "elapse": "4.090667ms"
}

curl -o - http://127.0.0.1:5654/db/query \
      --data-urlencode "q=select * from EXAMPLE" \
      --data-urlencode "rowsFlatten=true"

{
  "data": {
    "columns": [ "NAME", "TIME", "VALUE" ],
    "types": [ "string", "datetime", "double" ],
    "rows": [
      "wave.sin", 1705381958775759000, 0.8563571936170834,
      "wave.sin", 1705381958785759000, 0.9011510331449053,
      "wave.sin", 1705381958795759000, 0.9379488170706388
    ]
  },
  "success": true,
  "reason": "success",
  "elapse": "2.255625ms"
}

curl -o - http://127.0.0.1:5654/db/query \
      --data-urlencode "q=select * from EXAMPLE" \
      --data-urlencode "rowsArray=true"

{
  "data": {
    "columns": [ "NAME", "TIME", "VALUE" ],
    "types": [ "string", "datetime", "double" ],
    "rows": [
      { "NAME": "wave.sin", "TIME": 1705381958775759000, "VALUE": 0.8563571936170834 },
      { "NAME": "wave.sin", "TIME": 1705381958785759000, "VALUE": 0.9011510331449053 },
      { "NAME": "wave.sin", "TIME": 1705381958795759000, "VALUE": 0.9379488170706388 }
    ]
  },
  "success": true,
  "reason": "success",
  "elapse": "3.178458ms"
}

NDJSON

Set query param format=ndjson in the request. Since v8.0.33

NDJSON (Newline Delimited JSON) is a format for streaming JSON data where each line is a valid JSON object. This is useful for processing large datasets or streaming data because it allows you to handle one JSON object at a time.

curl -o - http://127.0.0.1:5654/db/query \
    --data-urlencode "q=select * from EXAMPLE limit 2" \
    --data-urlencode "format=ndjson"

curl -o - http://127.0.0.1:5654/db/query ^
    --data-urlencode "q=select * from EXAMPLE limit 2" \
    --data-urlencode "format=ndjson"

The response comes with Content-Type: application/x-ndjson.

{"NAME":"wave.sin","TIME":1705381958775759000,"VALUE":0.8563571936170834}
{"NAME":"wave.sin","TIME":1705381958785759000,"VALUE":0.9011510331449053}

CSV

Set query param format=csv in the request.

CSV format is also useful for processing large datasets or streaming data because it allows you to handle one line at a time.

curl -o - http://127.0.0.1:5654/db/query \
    --data-urlencode "q=select * from EXAMPLE limit 2" \
    --data-urlencode "format=csv"

curl -o - http://127.0.0.1:5654/db/query ^
    --data-urlencode "q=select * from EXAMPLE limit 2" \
    --data-urlencode "format=csv"

The response comes with Content-Type: text/csv; utf-8

NAME,TIME,VALUE
wave.sin,1705381958775759000,0.8563571936170834
wave.sin,1705381958785759000,0.9011510331449053

BOX

Set query param format=box in the request.

curl -o - http://127.0.0.1:5654/db/query \
    --data-urlencode "q=select * from EXAMPLE limit 2" \
    --data-urlencode "format=box"

curl -o - http://127.0.0.1:5654/db/query ^
    --data-urlencode "q=select * from EXAMPLE limit 2" \
    --data-urlencode "format=box"

The result data in plain text with ascii box. The Content-Type of the response is plain/text

+----------+---------------------+--------------------+
| NAME     | TIME(UTC)           | VALUE              |
+----------+---------------------+--------------------+
| wave.sin | 1705381958775759000 | 0.8563571936170834 |
| wave.sin | 1705381958785759000 | 0.9011510331449053 |
+----------+---------------------+--------------------+

Response in CSV format

Set query param format=csv in the request.

curl -o - http://127.0.0.1:5654/db/query \
    --data-urlencode "q=select * from EXAMPLE limit 2" \
    --data-urlencode "format=csv"

curl -o - http://127.0.0.1:5654/db/query ^
    --data-urlencode "q=select * from EXAMPLE limit 2" \
    --data-urlencode "format=csv"

The response comes with Content-Type: text/csv

NAME,TIME,VALUE
wave.sin,1705381958775759000,0.8563571936170834
wave.sin,1705381958785759000,0.9011510331449053

POST JSON

It is also possible to request query in JSON form as below example.

Request JSON message

curl -o - -X POST http://127.0.0.1:5654/db/query \
    -H 'Content-Type: application/json' \
    -d '{ "q":"select * from EXAMPLE limit 2" }'

curl -o - -X POST http://127.0.0.1:5654/db/query ^
    -H "Content-Type: application/json" ^
    -d "{ \"q\":\"select * from EXAMPLE limit 2\" }"

POST Form

HTML Form data format is available too. HTTP header Content-type should be application/x-www-form-urlencoded in this case.

curl -o - -X POST http://127.0.0.1:5654/db/query \
    --data-urlencode "q=select * from EXAMPLE limit 2"

curl -o - -X POST http://127.0.0.1:5654/db/query ^
    --data-urlencode "q=select * from EXAMPLE limit 2"

Examples

Please refer to the detail of the API

For this tutorials, pre-write data below.

Data file

Copy and paste on a new file data-nano-1.json.

{
    "data":{
      "columns":["NAME","TIME","VALUE"],
      "rows":[
          ["wave.sin",1676432361,0],
          ["wave.sin",1676432362,0.406736],
          ["wave.sin",1676432363,0.743144],
          ["wave.sin",1676432364,0.951056],
          ["wave.sin",1676432365,0.994522]
      ]
    }
}

Or download it from here.

Create table

curl -o - http://127.0.0.1:5654/db/query \
    --data-urlencode \
    "q=create tag table EXAMPLE (name varchar(40) primary key, time datetime basetime, value double)"

Write data

curl -X POST http://127.0.0.1:5654/db/write/EXAMPLE?timeformat=ns \
    -H "Content-Type: application/json"                           \
    --data-binary "@data-nano-1.json"

Select in CSV

Request

Set the format=csv query param for CSV format.

curl -o - http://127.0.0.1:5654/db/query       \
    --data-urlencode "q=select * from EXAMPLE" \
    --data-urlencode "format=csv"

Response

NAME,TIME,VALUE
wave.sin,1676432361000000000,0.111111
wave.sin,1676432362111111111,0.222222
wave.sin,1676432363222222222,0.333333
wave.sin,1676432364333333333,0.444444
wave.sin,1676432365444444444,0.555555

Select in BOX

Request

Set the format=box query param for BOX format.

curl -o - http://127.0.0.1:5654/db/query       \
    --data-urlencode "q=select * from EXAMPLE" \
    --data-urlencode "format=box"

Response

+----------+---------------------+----------+
| NAME     | TIME                | VALUE    |
+----------+---------------------+----------+
| wave.sin | 1676432361000000000 | 0        |
| wave.sin | 1676432362111111111 | 0.406736 |
| wave.sin | 1676432363222222222 | 0.743144 |
| wave.sin | 1676432364333333333 | 0.951056 |
| wave.sin | 1676432365444444444 | 0.994522 |
+----------+---------------------+----------+

Select in BOX with rownum

Request

Set the format=box query param for BOX format.

curl -o - http://127.0.0.1:5654/db/query       \
    --data-urlencode "q=select * from EXAMPLE" \
    --data-urlencode "format=box"              \
    --data-urlencode "rownum=true"

Response

+--------+----------+---------------------+----------+
| ROWNUM | NAME     | TIME                | VALUE    |
+--------+----------+---------------------+----------+
|      1 | wave.sin | 1676432361000000000 | 0.111111 |
|      2 | wave.sin | 1676432362111111111 | 0.222222 |
|      3 | wave.sin | 1676432363222222222 | 0.333333 |
|      4 | wave.sin | 1676432364333333333 | 0.444444 |
|      5 | wave.sin | 1676432365444444444 | 0.555555 |
+--------+----------+---------------------+----------+

Select in BOX without heading

Request

Set the format=box and heading=false query param for BOX format without header.

curl -o - http://127.0.0.1:5654/db/query       \
    --data-urlencode "q=select * from EXAMPLE" \
    --data-urlencode "format=box"              \
    --data-urlencode "heading=false"

Response

+----------+---------------------+----------+
| wave.sin | 1676432361000000000 | 0        |
| wave.sin | 1676432362111111111 | 0.406736 |
| wave.sin | 1676432363222222222 | 0.743144 |
| wave.sin | 1676432364333333333 | 0.951056 |
| wave.sin | 1676432365444444444 | 0.994522 |
+----------+---------------------+----------+

Select in BOX value in INTEGER

Request

Set the format=box and precision=0 query param for BOX format with integer precision.

curl -o - http://127.0.0.1:5654/db/query       \
    --data-urlencode "q=select * from EXAMPLE" \
    --data-urlencode "format=box"              \
    --data-urlencode "precision=0"

Response

+----------+---------------------+-------+
| NAME     | TIME                | VALUE |
+----------+---------------------+-------+
| wave.sin | 1676432361000000000 | 0     |
| wave.sin | 1676432362111111111 | 0     |
| wave.sin | 1676432363222222322 | 0     |
| wave.sin | 1676432364333333233 | 0     |
| wave.sin | 1676432365444444444 | 1     |
+----------+---------------------+-------+

Last updated on Oct 11, 2024

Write via http