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

• https://clickhouse.com/docs/en/sql-reference/formats#json