Changing the default clickhouse server response format to JSON

Posted on:July 16, 2023
Changing the default clickhouse server response format to JSON
Image generated by adobe firefly

The clickhouse http interface uses TSV(tab separated) as the default response format.

CREATE TABLE users (uid Int16, name String, age Int16) ENGINE=Memory;

INSERT INTO users VALUES (1231, 'John', 33);
INSERT INTO users VALUES (6666, 'Ksenia', 48);
INSERT INTO users VALUES (8888, 'Alice', 50);

SELECT * FROM users;

The server response:

{
  "result": {
    "query_run_id": "0180af8f-c9c4-41c7-be52-011a96cc1768",
    "output": "1231\tJohn\t33\n8888\tAlice\t50\n6666\tKsenia\t48\n",
    "time_elapsed": "117ms"
  }
}

Changing the format to JSON allows the response to be easily parsed or loaded into js/python objects.

Change the query to

SELECT * FROM users FORMAT JSON;

This outputs:

{
  "result": {
    "query_run_id": "26732a8a-aac9-4de7-b71a-4e1f35032c17",
    "output": "{\n\t\"meta\":\n\t[\n\t\t{\n\t\t\t\"name\": \"uid\",\n\t\t\t\"type\": \"Int16\"\n\t\t},\n\t\t{\n\t\t\t\"name\": \"name\",\n\t\t\t\"type\": \"String\"\n\t\t},\n\t\t{\n\t\t\t\"name\": \"age\",\n\t\t\t\"type\": \"Int16\"\n\t\t}\n\t],\n\n\t\"data\":\n\t[\n\t\t{\n\t\t\t\"uid\": 1231,\n\t\t\t\"name\": \"John\",\n\t\t\t\"age\": 33\n\t\t},\n\t\t{\n\t\t\t\"uid\": 6666,\n\t\t\t\"name\": \"Ksenia\",\n\t\t\t\"age\": 48\n\t\t},\n\t\t{\n\t\t\t\"uid\": 8888,\n\t\t\t\"name\": \"Alice\",\n\t\t\t\"age\": 50\n\t\t}\n\t],\n\n\t\"rows\": 3,\n\n\t\"statistics\":\n\t{\n\t\t\"elapsed\": 0.000923364,\n\t\t\"rows_read\": 3,\n\t\t\"bytes_read\": 54\n\t}\n}\n",
    "time_elapsed": "106ms"
  }
}

Now, you can parse the value of output and get the data key.

JSON.parse(clickhouse_response["result"]["output"])["data"];

returns

[
  {
    "uid": 1231,
    "name": "John",
    "age": 33
  },
  {
    "uid": 6666,
    "name": "Ksenia",
    "age": 48
  },
  {
    "uid": 8888,
    "name": "Alice",
    "age": 50
  }
]

Check out all the supported clickhouse formats here

To set JSON as the default response format in the clickhouse HTTP interface, pass default_format=JSON as a query parameter. This should be handled automatically if you’re using a clickhouse client library.

References